DATA Step, Macro, Functions and more

Transpose data

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

Transpose data

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


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,415

Re: Transpose data

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


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 9,415

Re: Transpose data

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;
Contributor
Posts: 63

Re: Transpose data

Thanks a lot.
PROC Star
Posts: 1,571

Re: Transpose data

@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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 90 views
  • 2 likes
  • 3 in conversation