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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.