topic Transpose data in SAS Programming

Transpose data

draroda — Wed, 06 Jun 2018 14:43:42 GMT

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

Re: Transpose data

RW9 — Wed, 06 Jun 2018 14:56:07 GMT

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;

Re: Transpose data

novinosrin — Wed, 06 Jun 2018 14:57:57 GMT

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

Re: Transpose data

draroda — Thu, 07 Jun 2018 04:06:55 GMT

Thanks a lot.