Get data structure below. How to transpose once for all variables?!
proc transpose data=have out=need;
by seq_id;
id time;
var var1 var2 var3;
run;quit;
the outcome is stacked for all variables.
OR anyway to do this by SQL or data step?
time seq_id var1 var2 var3 …
1120 1 …
1120 2 …
1120 3 …
1120 4 …
1125 1 …
1125 2 …
1125 3 ..
1125 4 …
Below article explains and provides macros for transposing that exceed what a single Proc Transpose can do.
Transpose your analysis data with the %MAKELONG and %MAKEWIDE macro
The macros are available from within the article.
I don't understand. Why would you create a variable using TIME as the basis for the name of the variable?
What use is that going to be to you? How will you be able to calculate time differences if you have stored the time value into the NAME of a variable instead of leaving it in a the VALUE of variable where it can be referenced?
Since the result would be unusable for further analysis, I take it this is for reporting purposes.
proc report data=have;
column seq_id (var1 var2 var3),time;
define seq_id / group;
define var1 / analysis;
define var2 / analysis;
define var3 / analysis;
define time / "" across;
run;
Check my paper - MERGE Skill to transpose data.
https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
/*
Try the MERGE skill proposed by me.
And that would be better if you post the result you are looking for.
*/
data have;
infile cards truncover expandtabs;
input time seq_id var1 var2 var3;
cards;
1120 1 1 2 3
1120 2 1 2 3
1120 3 1 2 3
1120 4 1 2 3
1125 1 1 2 3
1125 2 1 2 3
1125 3 1 2 3
1125 4 1 2 3
;
proc sql noprint;
select distinct catt('have(where=(time=',time,') rename=(var1=var1_',time,' var2=var2_',time,' var3=var3_',time,'))')
into :merge separated by ' '
from have;
quit;
data want;
merge &merge.;
by seq_id;
drop time;
run;
Thanks for the information!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.