Help using Base SAS procedures

Use do loop to output a single dataset into multiple ones

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

Use do loop to output a single dataset into multiple ones

I've a dataset with start date and end date for distinct cases, and a missing end date indicates the peson is still alive. I just need to create multiple databases from 2005 to 2010 (as end of the observation year) to indicate if cases are alive/present in each respective year.

Here is the data I've.

data aa; input id startyear endyear;

datalines;

1 2005 2007

2 2006 2006

3 2007

4 2008 2009

;

Desired datasets with first column to indicate ID and second to indicate the person is present

data2005

1 1

data2006

1 1

2 1

data2007

1 1

4 1

data2008

3 1

4 1

data2009

3 1

4 1

data2010

3 1

I know I should use macro and %do %to, and compare start/end year with the year in the %do year=2005 %to 2010. I tried a few variations but wasn't successful in producing my desired outcome. So thanks in advance for any help that can get me in the right track.


Accepted Solutions
Solution
‎02-13-2012 08:04 PM
Respected Advisor
Posts: 3,156

Use do loop to output a single dataset into multiple ones

Try this out:

data aa;

infile cards missover;

input id startyear endyear;

cards;

1 2005 2007

2 2006 2006

3 2007

4 2008 2009

;

%macro test;

%do year=2005 %to 2010;

data data&year(keep=id present );

set aa;

if startyear<= &year <= endyear or  (startyear<= &year and missing(endyear)) then 

    do;

       present=1;

       output;

    end;

run;

%end;

%mend;

%test

BTW, I believe you have a typo in your wanted data2007. It should be 3 1.

Regards,

Haikuo

View solution in original post


All Replies
Solution
‎02-13-2012 08:04 PM
Respected Advisor
Posts: 3,156

Use do loop to output a single dataset into multiple ones

Try this out:

data aa;

infile cards missover;

input id startyear endyear;

cards;

1 2005 2007

2 2006 2006

3 2007

4 2008 2009

;

%macro test;

%do year=2005 %to 2010;

data data&year(keep=id present );

set aa;

if startyear<= &year <= endyear or  (startyear<= &year and missing(endyear)) then 

    do;

       present=1;

       output;

    end;

run;

%end;

%mend;

%test

BTW, I believe you have a typo in your wanted data2007. It should be 3 1.

Regards,

Haikuo

Super User
Posts: 10,020

Use do loop to output a single dataset into multiple ones

Just for a fun.

data aa;
infile cards missover;
input id startyear endyear;
cards;
1 2005 2007
2 2006 2006
3 2007
4 2008 2009
;
run;

data _null_;
present=1;
if 0 then set aa;
 declare hash ha(dataset:'aa',hashexp:10);
 declare hiter hi('ha');
  ha.definekey('id');
  ha.definedata('id','startyear','endyear');
  ha.definedone();
 declare hash h(hashexp:10);
  h.definekey('id');
  h.definedata('id','present');
  h.definedone(); 

do year=2005 to 2010;
 do while(hi.next()=0);
  if (year ge startyear and year le endyear) or (year ge startyear and missing(endyear))  then h.add();
 end;
 h.output(dataset: cats('data',year));  h.clear();
end;
run;
 

Tian.Kong

Respected Advisor
Posts: 3,156

Use do loop to output a single dataset into multiple ones

Tian.Kong,

Your fun is hurting my brain right now :smileysilly:.

Haikuo

Super User
Posts: 10,020

Use do loop to output a single dataset into multiple ones

Hai.Kuo

Take easy. Your code is more succinct and readable.

I think op like yours better than mine.  Smiley Happy

Ksharp

海阔天空

Frequent Contributor
Posts: 107

Use do loop to output a single dataset into multiple ones

Indeed yours is beyond my comprehension and my head was exploding just by reading it Smiley Happy. I tried Haikuo's and it worked the way I wanted. Thanks so much. You guys are always so great and helpful.

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 250 views
  • 4 likes
  • 3 in conversation