BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dess
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

proc sort data=dsOriginalSet;

by pkey xpot;

run;

proc transpose data=dsOriginalSet out=want prefix=diag;

by pkey;

var diag;

run;

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

Sort and transpose.

Data never sleeps
Tom
Super User Tom
Super User

proc sort data=dsOriginalSet;

by pkey xpot;

run;

proc transpose data=dsOriginalSet out=want prefix=diag;

by pkey;

var diag;

run;

Dess
Calcite | Level 5

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!

Loko
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1578 views
  • 6 likes
  • 5 in conversation