Help using Base SAS procedures

Using proc transpose to convert data

Reply
Contributor
Posts: 68

Using proc transpose to convert data

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

.....

Trusted Advisor
Posts: 1,137

Re: Using proc transpose to convert data

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;

Thanks,
Jag
Contributor
Posts: 68

Re: Using proc transpose to convert data

Posted in reply to Jagadishkatam

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?

Respected Advisor
Posts: 4,925

Re: Using proc transpose to convert data

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;

PG

PG
Contributor
Posts: 68

Re: Using proc transpose to convert data

Posted in reply to Jagadishkatam

Thank you .

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?

Respected Advisor
Posts: 4,925

Re: Using proc transpose to convert data

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;

PG

PG
Super User
Super User
Posts: 7,050

Re: Using proc transpose to convert data

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.

Ask a Question
Discussion stats
  • 6 replies
  • 296 views
  • 2 likes
  • 4 in conversation