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.
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
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
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
Tian.Kong,
Your fun is hurting my brain right now :smileysilly:.
Haikuo
Hai.Kuo
Take easy. Your code is more succinct and readable.
I think op like yours better than mine.
Ksharp
海阔天空
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.