SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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
APPLE199110200
APPLE200423200
APPLE200532210
APPLE200623111
APPLE200723411
APPLE200942100
APPLE201023110
APPLE201144211
APPLE201242211
APPLE201321211
MSMFT200032300
MSMFT200123110
MSMFT200423200
MSMFT200515210
MSMFT200634511
MSMFT200723211
MSMFT200923100
MSMFT201034110
MSMFT201132511

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

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;

View solution in original post


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

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

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

Thank you! The code your provided works well!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 227 views
  • 2 likes
  • 2 in conversation