I have a dataset with patients with at least 2 observations - observed longitudinally. I would like to turn this into an independent dataset as below. What is the most efficient way to do this?
OLD:
ID YEAR
1 1980
1 1990
2 1975
2 1980
2 1985
NEW:
ID YEAR YEARS_UNTIL_2ND YEARS_UNTIL_LAST NUM_OBS
1 1980 10 10 2
2 1975 5 10 3
data have;
input ID YEAR;
datalines;
1 1980
1 1990
2 1975
2 1980
2 1985
;
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_=1 then _t=year;
else if _n_=2 then YEARS_UNTIL_2ND=year-_t;
if last.id then do;YEARS_UNTIL_LAST =year-_t;NUM_OBS=_n_;year=_t;end;
end;
drop _:;
run;
I actually have more variables in my dataset so this is keeping data for my last event, but replacing year with _t. I think it was meant to work only if we have these specific variables in the dataset, but how to I maintain the first?
Can you please modify your sample
OLD:
ID YEAR SYS DIAS ......................(just few of hundreds of vars)
1 1980 120 90
1 1990 130 85
2 1975 160 100
2 1980 146 98
2 1985 140 88
NEW:
ID YEAR YEARS_UNTIL_2ND YEARS_UNTIL_LAST NUM_OBS SYS DIAS.................(just few of hundreds of vars)
1 1980 10 10 2 120 90
2 1975 5 10 3 160 100
see if this works, I am off to class now at my college. If any issues, let me know. I will look into it in 3-4 hours
data have;
input ID YEAR SYS DIAS;
datalines;
1 1980 120 90
1 1990 130 85
2 1975 160 100
2 1980 146 98
2 1985 140 88
;
data want;
if _n_=1 then do;
dcl hash H (dataset:'have(obs=0)',ordered: "A") ;
h.definekey ("id") ;
h.definedata (all:'y') ;
h.definedone () ;
end;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_=1 then do; _t=year;h.add();end;
else if _n_=2 then YEARS_UNTIL_2ND=year-_t;
if last.id then do;YEARS_UNTIL_LAST =year-_t;NUM_OBS=_n_;year=_t;h.find();end;
end;
drop _:;
run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.