data example;
input firmname $ year revenue;
cards;
APPLE 1991 102
APPLE 2004 232
APPLE 2005 322
APPLE 2006 231
APPLE 2007 234
APPLE 2009 421
APPLE 2010 231
APPLE 2011 442
APPLE 2012 422
APPLE 2013 212
MSMFT 2000 323
MSMFT 2001 231
MSMFT 2004 232
MSMFT 2005 152
MSMFT 2006 345
MSMFT 2007 232
MSMFT 2009 231
MSMFT 2010 341
MSMFT 2011 325
run;
Hi. I am trying to set two dummy variables for the example shown above, depending on whethter, for each firm ('APPLE' and 'MSMFT'), the 'revenue' information exist 1) in two-years in a row and 2) in three-years in a row, and the output should look like below. Thank you in advance!
APPLE | 1991 | 102 | 0 | 0 |
APPLE | 2004 | 232 | 0 | 0 |
APPLE | 2005 | 322 | 1 | 0 |
APPLE | 2006 | 231 | 1 | 1 |
APPLE | 2007 | 234 | 1 | 1 |
APPLE | 2009 | 421 | 0 | 0 |
APPLE | 2010 | 231 | 1 | 0 |
APPLE | 2011 | 442 | 1 | 1 |
APPLE | 2012 | 422 | 1 | 1 |
APPLE | 2013 | 212 | 1 | 1 |
MSMFT | 2000 | 323 | 0 | 0 |
MSMFT | 2001 | 231 | 1 | 0 |
MSMFT | 2004 | 232 | 0 | 0 |
MSMFT | 2005 | 152 | 1 | 0 |
MSMFT | 2006 | 345 | 1 | 1 |
MSMFT | 2007 | 232 | 1 | 1 |
MSMFT | 2009 | 231 | 0 | 0 |
MSMFT | 2010 | 341 | 1 | 0 |
MSMFT | 2011 | 325 | 1 | 1 |
This looks like it should work. I can't test it right now, but give it a shot and see what you get.
data want;
set have;
by firmname;
retain consecutive_count;
years_passed = dif(year);
if first.firmname then consecutive_count=1;
else do;
if years_passed=1 then consecutive_count + 1;
else consecutive_count=1;
end;
if consecutive_count >= 2 then two=1;
else two=0;
if consecutive_count >= 3 then three=1;
else three=0;
run;
This looks like it should work. I can't test it right now, but give it a shot and see what you get.
data want;
set have;
by firmname;
retain consecutive_count;
years_passed = dif(year);
if first.firmname then consecutive_count=1;
else do;
if years_passed=1 then consecutive_count + 1;
else consecutive_count=1;
end;
if consecutive_count >= 2 then two=1;
else two=0;
if consecutive_count >= 3 then three=1;
else three=0;
run;
Thank you! The code your provided works well!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.