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