sqlsasproc-sqlenterprise-guide4gl

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


I have table in SAS Enterprise Guide like below:

Data types:

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 ?


Solution

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