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
.....
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;
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?
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
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?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.