Transforming a data set

[ajb]

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!

[ChrisNZ]

Keep the maximum for each ID.

[andreas_lds]

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

[Kurt_Bremser]

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

[Angel_Larrion · Accepted solution]

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;