topic Re: Indicator variable that tracks the year before a variables changes and the year it changes in SAS Data Management

Indicator variable that tracks the year before a variables changes and the year it changes

r4321 — Fri, 13 Mar 2020 17:45:41 GMT

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

Re: Indicator variable that tracks the year before a variables changes and the year it changes

PeterClemmensen — Fri, 13 Mar 2020 18:19:17 GMT

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;

Re: Indicator variable that tracks the year before a variables changes and the year it changes

Astounding — Sat, 14 Mar 2020 00:43:13 GMT

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.

Re: Indicator variable that tracks the year before a variables changes and the year it changes

ed_sas_member — Sat, 14 Mar 2020 09:03:15 GMT

Hi @r4321

How should we understand "the year before":

(Option 1) if it is "the value of v1 in the previous row", then try @PeterClemmensen or @Astounding solutions
(Option 2) if it is "v1-1", then try the below code:

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:

Option 1Option 2

Best,

Re: Indicator variable that tracks the year before a variables changes and the year it changes

Kurt_Bremser — Sat, 14 Mar 2020 15:52:25 GMT

According to your description, you should have a 1 in 2001, as there is a change from 1 to 0.

Re: Indicator variable that tracks the year before a variables changes and the year it changes

r4321 — Mon, 25 May 2020 22:19:32 GMT

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!