topic How to flatten with suffix from another variable in SAS Programming

How to flatten with suffix from another variable

shasank — Fri, 07 Feb 2020 21:52:39 GMT

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.

Re: How to flatten with suffix from another variable

novinosrin — Fri, 07 Feb 2020 21:57:37 GMT


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;

Re: How to flatten with suffix from another variable

novinosrin — Fri, 07 Feb 2020 22:06:54 GMT

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;

Re: How to flatten with suffix from another variable

shasank — Fri, 07 Feb 2020 22:08:28 GMT

This is really helpful. Thank you.