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!
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;
Keep the maximum for each ID.
It all depends on the type of your variables Opioid, NSAID, Diuretic and Steroids. Are they numeric or char?
proc summary data=have;
by id;
var /* your variables */;
output out=want max()=;
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.