Solved
Contributor
Posts: 63

# 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
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;```

All Replies
Solution
2 weeks ago
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

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.