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
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;
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 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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.