## making dummy variables for existence of row information in two- and three in a row

Solved
Occasional Contributor
Posts: 19

# making dummy variables for existence of row information in two- and three in a row

[ Edited ]
``````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!

firmname   year   revenue   two   three
 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

Accepted Solutions
Solution
‎04-01-2017 03:08 PM
Super User
Posts: 6,629

## Re: making dummy variables for existence of row information in two- and three in a row

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;

All Replies
Solution
‎04-01-2017 03:08 PM
Super User
Posts: 6,629

## Re: making dummy variables for existence of row information in two- and three in a row

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;

Occasional Contributor
Posts: 19