topic Re: Using proc transpose to convert data in SAS Procedures

Using proc transpose to convert data

chouchou — Thu, 09 Oct 2014 23:40:13 GMT

Hi guys, do you have any idea on how to use proc transpose to convert data have---> data want?

data have:

Obs Subj X1 X2 X3 X4 X5 Y1 Y2 Y3 Y4 Y5

1 001 8 5 6 5 4 10 20 30 40 50

2 002 7 5 6 4 5 11 33 29 34 56

3 003 2 2 4 5 6 22 38 21 20 34

data want:

Obs Subj Time X Y

1 001 1 8 10

2 001 2 5 20

3 001 3 6 30

4 001 4 5 40

5 001 5 4 50

6 002 1 7 11

7 002 2 5 33

8 002 3 6 29

9 002 4 4 34

10 002 5 5 56

I wrote the following codes:

proc transpose data=learn.wide out=long;

by Subj;

var X1-X5 Y1-Y5;

run;

The problem is that X variables and Y variables are under the same column, looks like below:

Subj _name_ Col1

001 X1 8

001 X2 5

001 X3 6

001 X4 5

001 X5 4

001 Y1 10

001 Y2 20

.....

Re: Using proc transpose to convert data

Jagadishkatam — Fri, 10 Oct 2014 00:44:33 GMT

data have;

input

Subj$ X1 X2 X3 X4 X5 Y1 Y2 Y3 Y4 Y5;

cards;

001 8 5 6 5 4 10 20 30 40 50

002 7 5 6 4 5 11 33 29 34 56

003 2 2 4 5 6 22 38 21 20 34

;

run;

data want;

set have;

array xs(*) x1-x5;

array ys(*) y1-y5;

do i = 1 to dim(xs);

x=xs(i);

y=ys(i);

output;

end;

rename i = time;

keep subj x y i;

run;

Re: Using proc transpose to convert data

PGStats — Fri, 10 Oct 2014 00:47:48 GMT

Arrays are your friends here.

data want;

set have;

array xx{*} x:;

array yy{*} y:;

do time = 1 to dim(xx);

x = xx{time}; y = yy{time};

output;

end;

keep subj time x y;

run;

Re: Using proc transpose to convert data

chouchou — Sat, 11 Oct 2014 00:10:58 GMT

Thank you PG.

However I know how to use array to do it, I'm just looking for a way to solve by proc transpose, is it possible?

Re: Using proc transpose to convert data

chouchou — Sat, 11 Oct 2014 00:11:31 GMT

Thank you Jagadishkatam .

However I know how to use array to do it, I'm just looking for a way to solve by proc transpose, is it possible?

Re: Using proc transpose to convert data

PGStats — Sat, 11 Oct 2014 02:51:42 GMT

There are many ways to do this kind of transposition. Here are two of them using proc transpose:

/* Two transpositions and a merge */

proc transpose data=have out=tempx name=namex prefix=x;

by subj;

var x:;

run;

proc transpose data=have out=tempy name=namey prefix=y;

by subj;

var y:;

run;

data want2;

merge tempx tempy; by subj;

time = input(compress(namex,,"kd"), best.);

drop name:;

rename x1=x y1=y;

run;

/* Variable stacking and block transposition */

proc sql;

create view temp as

select subj, "X" as var, x1, x2, x3, x4, x5 from have

union all

select subj, "Y", y1, y2, y3, y4, y5 from have

union all

select subj, "TIME", 1, 2, 3, 4, 5 from have

order by subj, var;

quit;

proc transpose data=temp out=want3(drop=_name_);

by subj;

var x:;

id var;

run;

Re: Using proc transpose to convert data

Tom — Sat, 11 Oct 2014 03:34:43 GMT

Either transpose the two groups of variables separately and merge then back together.

Or take you current transpose output and process it to convert the _NAME_ field into a new _NAME_ and TIME fields. Then sort and re-transpose it.