How to aggregate character data by assigning weights to them in SAS Enterprise Guide or SAS PROC SQL?

Question

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 ?

Answer 1

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;