Use do loop to output a single dataset into multiple ones

[Solph]

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.

[Haikuo · Accepted solution]

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

[Ksharp]

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

[Haikuo]

Tian.Kong,

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

Haikuo

[Ksharp]

Hai.Kuo

Take easy. Your code is more succinct and readable.

I think op like yours better than mine. 

Ksharp

海阔天空

[Solph]

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