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

Question

I have table in SAS Enterprise Guide like below:

• VAL1 - character
• ID - numeric
• VAL2 - character (with only 3 options: P, C, S)

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:

• If some "ID" has at least once P or C in "VAL2" then stay this "ID" with P or C for the corresponding value from column "VAL1" and of course each "ID" has to be only one time in output.
• else if some "ID" do not has P or C in "VAL2" then take what it has, so S

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) :).

Answer 1

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;