Proc transpose to transform dataset from long to wide

[monsterpie]

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

[Kurt_Bremser]

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?

[Ksharp · Accepted solution]

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;

[monsterpie]

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.

[Ksharp]

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