sqlsasproc-sqlenterprise-guide4gl

How to choose more important value from one column for value in corresponding column in SAS Enterprise Guide?


I have table in SAS Enterprise Guide like below:

VAL1 ID VAL2
P1 123 P
P1 123 P
P1 123 S
S2 44 C
S2 44 S
GG 44 P
P1 58 S
P1 58 S

And I need to make something like this:

So as a result I need something like below:

ID VAL1 VAL2
123 P1 P
44 S2 C
44 GG P
58 P1 S

How can I do that in PROC SQL in SAS Enterprise Guide? (Of course code could be also in normal SAS not PROC SQL) :).


Solution

  • This could definitely be done more elegantly. But this should work just fine.

    data have;
        input VAL1 $2. ID VAL2 $1.;
        datalines;
    P1 123 P
    P1 123 P
    P1 123 S
    S2 44 C
    S2 44 S
    GG 44 P
    P1 58 S
    P1 58 S
    ;
    run;
    
    proc sort data=have out=sorted nodupkey;
    by ID descending VAL2;
    run;
    
    data want;
        set sorted;
        by ID;
        if VAL2 in ('P' 'C') then output;
        else if last.ID then output;
    run;