For my master's thesis I have to work with a database in order to make a logistic regression model. One of the variables of my model is NEWS, This should be a dummy variable that is equal to 1 if a company had to publish a negative net income (bad news) in a given year, which belongs to the time period of this study (2017, 2018 and 2019), while it still had a positive net income the year before (good news). The dummy is equal to 0 otherwise. Now my professor said that I had to make a dummy that is equal to one when a company had a negative net income during one of the three years, O otherwise. That part I understand. But then she also said that I had to use a LAG function in order to find out if the company had a positive net income the year before the negative net income. And that part I don't understand. I don't really understand how i should do this with the help of SPSS. Below you can find a small part of the data I have to work with.
Company's number Year Net income Dummy(net income negative)
899999256 2017 222884,00 0
899999256 2018 237387,00 0
899999256 2019 314501,00 0
899998959 2017 -22414,00 1
899998959 2018 -10834,00 1
899998959 2019 -365,00 1
899993714 2017 453,00 0
899993714 2018 152,00 0
899993714 2019 -178,00 1
In this case company one and two would get a final dummy variable (NEWS) of 0. But company three would get a final dummy of one as they had a positive net income in 2018 and a negative net income in 2019.
Now I have to do this for more than 170 thousand companies so having a syntax code for this would make this much easier and that is why I ask you guys for your help.
Thank you in advance!
I looked online how to do a LAG function but none of those solutions really solved my problem. I hope one of you can help me :)
The following code will use the lag function to mark the years where the same company had a negative net income while the previous year it had a positive one:
sort cases by CompanyNumber Year.
compute dummy=0.
if company=lag(company) and NetIncome<0 and lag(NetIncome)>0 dummy=1.
exe.