Hi all, I have a dataset that looks like this. I want to transpose it with the SBP_FU values for each month as the variables by ID, but also retain the first and last followup dates. I was able to do the first or last Follow up date using COPY, but not both. .thanks for your help!
127 | 09/25/2007 | 6 | 328 |
142 | 12/19/2007 | 9 | 328 |
129 | 04/02/2008 | 12 | 328 |
122 | 06/25/2008 | 15 | 328 |
117 | 09/03/2008 | 18 | 328 |
114 | 09/26/2006 | 6 | 514 |
120 | 12/25/2006 | 9 | 514 |
The final dataset should look like this ;
Obs | ID | firstdate | last date | SBP_6_mnth | SBP_9_mnth | SBP_12_mnth | SBP_15_mnth | SBP_18_mnth |
1 | 328 | 9/25/2007 | 9/3/2008 | 127 | 142 | 129 | 122 | 117 |
2 | 514 | 9/26/2006 | 12/25/2006 | 114 | 120 | . | . | . |
data have;
input SBP_FU FU_DATE :mmddyy10. month ID;
format FU_DATE mmddyy10. ;
cards;
127 09/25/2007 6 328
142 12/19/2007 9 328
129 04/02/2008 12 328
122 06/25/2008 15 328
117 09/03/2008 18 328
114 09/26/2006 6 514
120 12/25/2006 9 514
;
proc transpose data=have out=w(drop=_:) prefix=SBP_ suffix=_mnth;
by id;
var SBP_FU;
id month;
run;
data want;
if 0 then set have(keep=id FU_DATE) ;
format firstdate lastdate mmddyy10.;
do until(last.id);
merge have(keep=id FU_DATE) w;
by id;
if first.id then firstdate=FU_DATE;
end;
lastdate=FU_DATE;
drop FU_DATE;
run;
data have;
input SBP_FU FU_DATE :anydtdte10. month ID $;
cards;
127 09/25/2007 6 328
142 12/19/2007 9 328
129 04/02/2008 12 328
122 06/25/2008 15 328
117 09/03/2008 18 328
114 09/26/2006 6 514
120 12/25/2006 9 514
run;
proc sort data=have;
by id FU_DATE;
run;
data want;
format FU_DATE firstdate lastdate date9.;
array sbp_arr[*] sbp1-sbp5;
do i=1 to dim(sbp_arr) until(last.id);
set have;
by id;
if first.id then
firstdate=FU_DATE;
sbp_arr[i] =SBP_FU;
end;
lastdate=FU_DATE;
drop FU_DATE i month SBP_FU;
run;
data have;
input SBP_FU FU_DATE :mmddyy10. month ID;
format FU_DATE mmddyy10. ;
cards;
127 09/25/2007 6 328
142 12/19/2007 9 328
129 04/02/2008 12 328
122 06/25/2008 15 328
117 09/03/2008 18 328
114 09/26/2006 6 514
120 12/25/2006 9 514
;
proc transpose data=have out=w(drop=_:) prefix=SBP_ suffix=_mnth;
by id;
var SBP_FU;
id month;
run;
data want;
if 0 then set have(keep=id FU_DATE) ;
format firstdate lastdate mmddyy10.;
do until(last.id);
merge have(keep=id FU_DATE) w;
by id;
if first.id then firstdate=FU_DATE;
end;
lastdate=FU_DATE;
drop FU_DATE;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.