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