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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.