BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
r4321
Pyrite | Level 9

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. 

 

 

1999222ABC CO0
2000222ABC CO1
2001222ABC CO0
2002222ABC CO0
2003222ABC CO0
2004222ABC CO0
2005222ABC CO0
2006222ABC CO1

 

 

 

Want 

 

 

1999222ABC CO01
2000222ABC CO11
2001222ABC CO00
2002222ABC CO00
2003222ABC CO00
2004222ABC CO00
2005222ABC CO01
2006222ABC CO11

 

 

Your help would be very much appreciated!

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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;
Astounding
PROC Star

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.

ed_sas_member
Meteorite | Level 14

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 1Option 2Option 2

Best,

r4321
Pyrite | Level 9

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!

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 1318 views
  • 3 likes
  • 5 in conversation