topic Re: Proc transpose to transform dataset from long to wide in New SAS User

Proc transpose to transform dataset from long to wide

monsterpie — Sat, 28 Aug 2021 20:16:10 GMT

Hello all,

I am trying to use proc transpose to change my dataset from long to wide. Below is an example of my data and my desired output, as well as the code I have attempted so far. My issue is that when I run my code, the visit_num columns of the transposed dataset do not contain the diag values. I'm wondering how to fix my code so I can get my desired output (and how to keep the Age variable from the original dataset).

data have;
input id visit_num diag$ age treatment;
cards;
01 1 diabetes 42 1
02 1 stroke 78 1
02 2 cancer . 2
02 3 stroke . .
03 1 stroke 66 2
03 2 copd . 1
03 3 . . .
;
run;

proc transpose data=have out=wide2 
	prefix=visit_num;
by id;
var visit_num;
run;

Desired output:

ID	Visit_Num1	Visit_Num2	Visit_Num3	Age	Treatment1	Treatment2	Treatment3
01	diabetes			42	1
02	stroke	cancer	stroke	78	1	2
03	stroke	copd		66	2	1

Re: Proc transpose to transform dataset from long to wide

Kurt_Bremser — Sun, 29 Aug 2021 06:35:37 GMT

proc transpose data=have out=wide1
	prefix=visit_num;
by id;
id visit_num;
var diag;
run;

proc transpose data=have out=wide2
	prefix=treatment;
by id;
id visit_num;
var treatment;
run;

Then merge the datasets.

Or do you rather need a report?

Re: Proc transpose to transform dataset from long to wide

Ksharp — Sun, 29 Aug 2021 11:20:13 GMT

The simplest way is using PROC SUMMARY.

data have;
input id visit_num diag$ age treatment;
cards;
01 1 diabetes 42 1
02 1 stroke 78 1
02 2 cancer . 2
02 3 stroke . .
03 1 stroke 66 2
03 2 copd . 1
03 3 . . .
;
run;

proc sql noprint;
select max(n) into :n
 from (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want idgroup(out[&n] (visit_num diag age treatment)=);
run;

Re: Proc transpose to transform dataset from long to wide

monsterpie — Sun, 29 Aug 2021 13:21:56 GMT

Thank you @Ksharp for your response and help. Would you mind just providing me a brief explanation of each line of code? I'd like to ensure I understand it for future reference. Thank you again.

Re: Proc transpose to transform dataset from long to wide

Ksharp — Mon, 30 Aug 2021 11:51:37 GMT

You should check sas documentation about PROC SUMMARY + IDGROUP option at
support.sas.com
.
You could find all you need in its documentation.