topic How to construct a dummy variable that is related to previous years in Statistical Procedures

How to construct a dummy variable that is related to previous years

sfan6 — Wed, 20 Nov 2019 00:20:21 GMT

Hello everyone, I have the following data set,

Ticker Year Net income Net income is negative in the past two years? (yes=1, no=0)

A 2000 2

A 2001 -1
A 2002 1 0
A 2003 -1 0
A 2004 -3 1
A 2005 -2 1
B 2001 3

B 2002 4

B 2003 -1 0

B 2004 -2 1

My question is how to construct "Net income is negative in the past two years", the last column?

Re: How to construct a dummy variable that is related to previous years

PaigeMiller — Wed, 20 Nov 2019 02:09:03 GMT

Use the LAG() and LAG2() function.

Re: How to construct a dummy variable that is related to previous years

Ksharp — Wed, 20 Nov 2019 11:44:52 GMT

data have;
input Ticker $   Year       Netincome ;
cards; 
A              2000          2
A              2001           -1
A              2002           1                                                  0
A              2003            -1                                               0
A              2004             -3                                              1
A              2005             -2                                               1
B              2001              3               
B              2002               4
B              2003               -1                                             0
B              2004               -2 
;

proc sql;
create table want as
select *,case when 
(select sum(netincome) from have 
where ticker=a.ticker and year between a.year-1 and a.year)<0 then 1
else 0 end as flag
 from have as a;
quit;