Hi,
I could really use some help transforming my sets.
Consider the following:
data dsOriginalSet;
input pKey diag pid xpot xdist;
cards;
1998 G03 17752 080707 5
2727 E003M 185355 040188 9
1124 G03 85174 110101 3
1998 G03 65510 030912 5
2727 G03 319013 011096 8
4493 G02 881244 050709 3
;
The primary key here is pid, with some data being derivative.
From this set, I would like to construct a sequence of diag, using pKey as the primary key, where each occurrence of diag is sorted by date (as provided by xpot). The result from the above set should be:
data dsTransformed
input pKey diag1 diag2 (diag3 ...);
cards;
1998 G03 G03
2727 E003M G03
1124 G03
4493 G02
;
The number of diag variables is arbitrary, but will not exceed 10.
Do you have any suggestions on how to accomplish this?
Thank you in advance
proc sort data=dsOriginalSet;
by pkey xpot;
run;
proc transpose data=dsOriginalSet out=want prefix=diag;
by pkey;
var diag;
run;
Sort and transpose.
proc sort data=dsOriginalSet;
by pkey xpot;
run;
proc transpose data=dsOriginalSet out=want prefix=diag;
by pkey;
var diag;
run;
Tom wrote:
proc sort data=dsOriginalSet;
by pkey xpot;
run;
proc transpose data=dsOriginalSet out=want prefix=diag;
by pkey;
var diag;
run;
Tried this, and it works great. Thank you. For some reason I got two additional variables, _NAME_ and _LABEL_, filled on every row with DIAG, but that's not a problem, only a curiosity. Thank you all for the great answers!
Hello,
Another solution:
data have(index=(comp=(pkey xpot)));
input pKey diagg $ pid @27 xpot mmddyy6. xdist;
format xpot date9.;
cards;
1998 G03 17752 080707 5
2727 E003M 185355 040188 9
1124 G03 85174 110101 3
1998 G03 65510 030912 5
2727 G03 319013 011096 8
4493 G02 881244 050709 3
;
proc contents;
run;
data want;
array diag{10} $;
do i=1 to 10 until(last.pkey);
set have;
by pkey xpot;
diag{i}=diagg;
end;
run;
Transpose is the obvious way, but if you want it in 1 variable you then need another datastep to append the data together. The below uses a retained aggregate variable and outputs only at the last of the group:
proc sort data=dsoriginalset;
by pkey;
run;
data want (keep=pkey dstransformed);
set dsoriginalset;
by pkey;
retain dstransformed;
length dstransformed $2000.;
if first.pkey then dstransformed="";
dstransformed=catx(",",strip(dstransformed),strip(diag));
if last.pkey then output;
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.