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

Hi All,

 

I have data as below

 

data have;
 input subj $ 1-3 AE $5-15;
 datalines;
 X1 Fever
 X2 Fever
 X3 Cold
 X4 Cold
 X5 Chills
 X6 Nausea
 ;

I need to transpose data as below.

 

Fever

Cold

Chills

Nausea

X1

X3

X5

X6

X2

X4

 

 

 

 

 

 

 

This seems to be simple one with Proc transpose but i am not getting any idea about by variable as same is not present.

 

Do suggest.

 

Regards,

Rajesh

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its because there is no logical relationship from the data presented.  Why would X3 be in column 2 on row 1. Why would X1 be on row 1 even.  You need to add more information to the data.  First assign a number which is unique, which indicates the position in the output dataset to appear, then transpose based on that.

data have;
 input subj $ ae $;
datalines;
X1 Fever
X2 Fever
X3 Cold
X4 Cold
X5 Chills
X6 Nausea
;
run;
data have;
  set have;
  retain idvar 0;
  by ae notsorted;
  idvar=ifn(first.ae,1,idvar+1);
run;
proc sort data=have;
  by idvar ae;
run;

proc transpose data=have out=want;
  by idvar;
  var subj;
  id ae;
  idlabel ae;
run;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its because there is no logical relationship from the data presented.  Why would X3 be in column 2 on row 1. Why would X1 be on row 1 even.  You need to add more information to the data.  First assign a number which is unique, which indicates the position in the output dataset to appear, then transpose based on that.

data have;
 input subj $ ae $;
datalines;
X1 Fever
X2 Fever
X3 Cold
X4 Cold
X5 Chills
X6 Nausea
;
run;
data have;
  set have;
  retain idvar 0;
  by ae notsorted;
  idvar=ifn(first.ae,1,idvar+1);
run;
proc sort data=have;
  by idvar ae;
run;

proc transpose data=have out=want;
  by idvar;
  var subj;
  id ae;
  idlabel ae;
run;
draroda
Fluorite | Level 6
Thanks a lot.
novinosrin
Tourmaline | Level 20

@draroda All you need is a double transpose in my opinion

 

data have;
 input subj $ 1-3 AE $5-15;
 datalines;
 X1 Fever
 X2 Fever
 X3 Cold
 X4 Cold
 X5 Chills
 X6 Nausea
 ;

 proc transpose data=have out=temp ;
 by ae  notsorted;
 var subj;
 run;

 proc transpose data=temp out=want;
 var col1 col2;
 id ae;
 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
  • 3 replies
  • 749 views
  • 2 likes
  • 3 in conversation