CODEWITHSUNDEEP
sastranspose
Kit Xing
Kit Xing

Reputation: 11

SAS transpose wide to long with duplicates

I am going to transpose the following table:

X     Y1     Y2     Y3

a      1       2        3

a      2       3        4

b      7       8        9

To 

X     Y

a      1  

a      2  

a      3 

a      2  

a      3  

a      4 

b      7  

b      8  

b      9

I tried this code 

PROC TRANSPOSE DATA=table1 OUT=table2;
BY X;
RUN;

However, it gave me this output

X     COL1       COL2

a      1                2

a      2                3

a      3                4

b      7  

b      8  

b      9

Does anyone how to get the table that I need?

Appreciate any thoughts, thank you!

Upvotes: 1

Views: 142

Answers (2)

Rhythm
Rhythm

Reputation: 682

You can use the following code, it adds a row and then transpose based on row. Since the row numbers will be unique

data have;
input X $ Y1 Y2 Y3;
datalines;
a 1 2 3
a 2 3 4
b 7 8 9
;
run;

data have;
set have;
row=_n_;
run;

PROC TRANSPOSE DATA=have OUT=want(rename=(COL1=Y) drop=_NAME_ row);
by row x;
RUN;

proc print data=want; run;

Upvotes: 0

Kiran
Kiran

Reputation: 3315

something like below should work

data want;
set have;
array name(*) Y1--Y3;
do i = 1 to dim(name);
Y=name[i];
output;
end;
Drop y1 y2 y3 i;
run;

Upvotes: 1

Related Questions