sqlsasproc-sqlenterprise-guide4gl

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


I have Table in SAS Enterprise Guide like below.

Data type:

ID GROUP
8945 CON
9567 PRI
9567 PRI
284 CON
284 CON
284 PRI

And I need to create new character column "COL1" where:

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 ?


Solution

  • 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;