Lag function in SPSS-CodePudding

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 :)

CodePudding user response：

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.