BookmarkSubscribeRSS Feed
chouchou
Calcite | Level 5

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

.....

6 REPLIES 6
Jagadishkatam
Amethyst | Level 16

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
chouchou
Calcite | Level 5

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?

PGStats
Opal | Level 21

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
chouchou
Calcite | Level 5

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?

PGStats
Opal | Level 21

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
Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1075 views
  • 2 likes
  • 4 in conversation