Trying to create an indicator variable that tracks the year before a different variable changes and the year it changes by creating a dummy = 1. See example.
1999 | 222 | ABC CO | 0 |
2000 | 222 | ABC CO | 1 |
2001 | 222 | ABC CO | 0 |
2002 | 222 | ABC CO | 0 |
2003 | 222 | ABC CO | 0 |
2004 | 222 | ABC CO | 0 |
2005 | 222 | ABC CO | 0 |
2006 | 222 | ABC CO | 1 |
Want
1999 | 222 | ABC CO | 0 | 1 |
2000 | 222 | ABC CO | 1 | 1 |
2001 | 222 | ABC CO | 0 | 0 |
2002 | 222 | ABC CO | 0 | 0 |
2003 | 222 | ABC CO | 0 | 0 |
2004 | 222 | ABC CO | 0 | 0 |
2005 | 222 | ABC CO | 0 | 1 |
2006 | 222 | ABC CO | 1 | 1 |
Your help would be very much appreciated!
Thanks
Try this
data have;
input v1 v2 v3 $ v4 $ v5;
datalines;
1999 222 ABC CO 0
2000 222 ABC CO 1
2001 222 ABC CO 0
2002 222 ABC CO 0
2003 222 ABC CO 0
2004 222 ABC CO 0
2005 222 ABC CO 0
2006 222 ABC CO 1
;
data want (drop=_:);
merge have
have (firstobs=2 rename=v5=_v5 keep=v5);
if v5 = 1 | _v5 = 1 then dummy = 1;
else dummy = 0;
run;
Try this
data have;
input v1 v2 v3 $ v4 $ v5;
datalines;
1999 222 ABC CO 0
2000 222 ABC CO 1
2001 222 ABC CO 0
2002 222 ABC CO 0
2003 222 ABC CO 0
2004 222 ABC CO 0
2005 222 ABC CO 0
2006 222 ABC CO 1
;
data want (drop=_:);
merge have
have (firstobs=2 rename=v5=_v5 keep=v5);
if v5 = 1 | _v5 = 1 then dummy = 1;
else dummy = 0;
run;
Here's one approach:
data want;
set have;
by company flag notsorted;
if (last.flag=1 and last.company=0 and flag=0) or (first.flag=1 and flag=1 and first.company=0) then indicator=1;
else indicator=0;
run;
The logic is untested (that's your job), and only identifies changes from "0" to "1" not the other way around.
I made up the variable names, since you didn't post them, but they should be obvious.
Hi @r4321
How should we understand "the year before":
proc sql;
select a.*, case when a.v5=1 or b.v5=1 then 1 else 0 end as dummy
from have as a left join have as b
on a.v1 = b.v1-1 and a.v3=b.v3 /* add a.v2=b.v2 and a.v4=b.v4 if needed to group observations of the same company*/
order by v2, v3, V4, v1;
quit;
See the difference, in case it occurs that sometimes you have not consecutive years:
Best,
According to your description, you should have a 1 in 2001, as there is a change from 1 to 0.
Hey Kurt, thanks for your help. I actually only wanted the indicator to =1 if it was the before or concurrent with the change (not after). Appreciate your assistance!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.