topic Re: How to Transpose once for all vars?! in SAS Programming

How to Transpose once for all vars?!

hellohere — Sat, 09 Dec 2023 01:49:35 GMT

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	…

Re: How to Transpose once for all vars?!

Patrick — Sat, 09 Dec 2023 02:29:22 GMT

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.

Re: How to Transpose once for all vars?!

Tom — Sat, 09 Dec 2023 02:48:52 GMT

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?

Re: How to Transpose once for all vars?!

Kurt_Bremser — Sat, 09 Dec 2023 21:35:32 GMT

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;

Re: How to Transpose once for all vars?!

Ksharp — Sat, 09 Dec 2023 10:26:55 GMT

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;

Re: How to Transpose once for all vars?!

kimmorgan — Sat, 09 Dec 2023 13:20:43 GMT

Thanks for the information!