topic Re: Transforming a data set in SAS Programming

Transforming a data set

ajb — Fri, 17 Jul 2020 05:45:06 GMT

I am using SAS 9.4

Hello all,

I have a data set that is similar to this:

ID	Med	Opioid	NSAID	Diuretic	Steroids
1	A	1	0	0	0
1	B	1	0	0	0
1	C	0	1	0	0
1	D	0	0	1	0
1	E	0	0	0	1
2	D	0	0	1	0
2	C	0	1	0	0
3	A	1	0	0	0
3	I	0	1	0	0
3	J	0	0	0	1
3	D	0	0	1	0
4	A	1	0	0	0
4	B	1	0	0	0
4	N	0	0	1	0

and I would like to transform the data set into something that looks like this:

ID	Opioid	NSAID	Diuretic	Steroids
1	1	1	1	1
2	0	1	1	0
3	1	1	1	1
4	1	0	1	0

How would I go about doing this?

Thank you in advance!

Re: Transforming a data set

ChrisNZ — Fri, 17 Jul 2020 06:12:25 GMT

Keep the maximum for each ID.

Re: Transforming a data set

andreas_lds — Fri, 17 Jul 2020 07:03:08 GMT

It all depends on the type of your variables Opioid, NSAID, Diuretic and Steroids. Are they numeric or char?

Re: Transforming a data set

Kurt_Bremser — Fri, 17 Jul 2020 08:37:53 GMT

proc summary data=have;
by id;
var /* your variables */;
output out=want max()=;
run;

Re: Transforming a data set

Angel_Larrion — Fri, 17 Jul 2020 14:17:33 GMT

You can do it with proc sql.

proc sql;
create table want as
select  id, max(Opioid) as opioid, max(NSAID) as NSAID, max(Diuretic) as Diuretic, max(Steroids) as Steroids
from have
group by id;
quit;