How to flatten with suffix from another variable

[shasank]

Hi Community,

Thank you for your time.

I am stuck with a situation where I have to flatten a dataset with suffix from different variable.

Here is the raw dataset. 

id	Intake	ctx	sz	pel
2211	1	0	0	0
2211	4	0	0	1
2211	5	0	0	0
3322	1	0	0	0
3322	4	0	0	0
3322	5	0	0	0
4422	1	0	0	0
5533	1	0	0	1
5533	4	0	0	0
5533	5	0	0	0

The idea is to use the intake variable and add it as a suffix and flatten the dataset to get one row per patient final file.

Expected Output. 

id		ctx1	ctx4	ctx5	sz1	sz4	sz5	pel1	pel4	pel5
2211		0	0	0	0	0	0	0	1	0
3322		0	0	0	0	0	0	0	0	0
4422		0	.	.	0	.	.	0	.	.
5533		0	0	0	0	0	0	1	0	0

I tried to use proc transpose by ID but had not luck.

Any help is appreciated. Thank you for looking. 

[novinosrin · Accepted solution]

data have;
input id	Intake	ctx	sz	pel;
cards;
2211	1	0	0	0
2211	4	0	0	1
2211	5	0	0	0
3322	1	0	0	0
3322	4	0	0	0
3322	5	0	0	0
4422	1	0	0	0
5533	1	0	0	1
5533	4	0	0	0
5533	5	0	0	0
;

proc transpose data=have out=temp;
by id intake;
var 	ctx	sz	pel;
run;
proc transpose data=temp out=want(drop=_name_);
by id ;
var col1;
id _name_ intake;
run;

[novinosrin]

In case you wanted the order exactly like you showed-->

data have;
input id	Intake	ctx	sz	pel;
cards;
2211	1	0	0	0
2211	4	0	0	1
2211	5	0	0	0
3322	1	0	0	0
3322	4	0	0	0
3322	5	0	0	0
4422	1	0	0	0
5533	1	0	0	1
5533	4	0	0	0
5533	5	0	0	0
;

data temp;
 set have;
 array t ctx	sz	pel;
 length vn $32;
 do over t;
  v=t;
  vn=vname(t);
  grp=_i_;
  output;
 end;
 drop ctx	sz	pel;
run;
proc sort data=temp;
by id grp;
run;
proc transpose data=temp out=want(drop=_name_);;
by id ;
id vn intake;
var v;
run;

[shasank]

This is really helpful. Thank you.