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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.