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;

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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