How to aggregate table to take minimum VALUE for each combination of 2 other columns in SAS Enterprise Guide?

Question

I have table in SAS Enterprise Guide like below:

Data types:

• ID - numeric
• DT - date
• EVENT - character
• VALUE - numeric

Values are not sorted in my real dataset, but it can be sorted if it is important for solution.

ID  | DT        | EVENT | VALUE
----|-----------|-------|--------
123 | 23MAY2022 | AAB   | 2
123 | 23MAY2022 | AAB   | 2 
123 | 30MAY2022 | G     | 15
555 | 11JUN2020 | Z     | 3
555 | 18JUL2020 | AG    | 2
876 | 11AUG2020 | E     | 18

And I need to aggregate this table:

1. For each ID take DT and EVENT with the lowest VALUE per this ID
2. If two VALUE-s have the same the lowest VALUE (like ID = 123 and VALUE = 2) take EVENT randomly

So I need something like below:

ID  | DT        | EVENT | VALUE  | 
----|-----------|-------|--------|
123 | 23MAY2022 | AAB   | 2      |
555 | 18JUL2020 | AG    | 2      |
876 | 11AUG2020 | E     | 18     |

How can I do that in SAS Enterprise Guide in PROC SQL or in normal SAS code ?

Answer 1

This should work.

data have;
    input ID DT :date9. EVENT $ VALUE;
    format DT date9.;
    datalines;
123 23MAY2022 AAB 2
123 23MAY2022 AAB 2 
123 30MAY2022 G 15
555 11JUN2020 Z 3
555 18JUL2020 AG 2
876 11AUG2020 E 18
;
run;

proc sort data=have;
    by ID VALUE;
run;

data want;
    set have;
    by ID;
    if first.ID then output;
run;