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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.