proc transpose copy multiple variables

[rj438]

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!

Obs SBP_FU FU_DATE month ID1234567etc.. 

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	.	.	.

[r_behata]

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;

[novinosrin · Accepted solution]

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;

[rj438]

this worked perfectly, thanks!