I have Table in SAS Enterprise Guide like below.
Data type:
- ID - numeric
- GROUP - character (only PRI or CON values)
ID | GROUP |
---|---|
8945 | CON |
9567 | PRI |
9567 | PRI |
284 | CON |
284 | CON |
284 | PRI |
And I need to create new character column "COL1" where:
- If even once a given ID is in the PRI group, no matter how many times it was in CON COL1 = PRI
- if ID is nowhere PRI only CON, then CON
Generally, PRI is more important group than CON, and if you was at least once in PRI gropu, you have PRI in new COL1. So, as a result I need somethin like below:
ID | COL1 |
---|---|
8945 | CON |
9567 | PRI |
284 | PRI |
How can I do that in SAS Enterprise Guide in PROC SQL or in normal SAS code ?
CodePudding user response:
Try this
data have;
input ID GROUP $;
datalines;
8945 CON
9567 PRI
9567 PRI
284 CON
284 CON
284 PRI
;
proc sql;
create table want as
select distinct *
from have
group by ID
having whichc(GROUP, 'PRI', 'CON')
= min(whichc(GROUP, 'PRI', 'CON'));
quit;