How to take max value from one column for each value in second column in table with many other columns in SAS Enterprise Guide?

Question

I have table in SAS Enterprise Guide like below:

COL1 - date COL2 - numeric

COL1      | COL2  | COL3  | COL4 | COL5
--------- |-------|-------|------|-------
01APR2021 | 11    | XXX   | XXX  | XXX
01MAY2021 | 5     | XXX   | XXX  | XXX
01MAY2021 | 25    | XXX   | XXX  | XXX
01JUN2021 | 10    | XXX   | XXX  | XXX
...       | ...   | ...   | ...  | ...

And I need to for each dates in COL1 select max value in COL2.
Moroever, in output I need to have also rest of my columns: COL3, COL4, COL5

So as a result I need somethin like below, because for date 01MAY2021 in COL1, in COL2 are two values and 25>5.

COL1      | COL2  | COL3  | COL4 | COL5
--------- |-------|-------|------|-------
01APR2021 | 11    | XXX   | XXX  | XXX
01MAY2021 | 25    | XXX   | XXX  | XXX
01JUN2021 | 10    | XXX   | XXX  | XXX
...       | ...   | ...   | ...  | ...

How can I do that in SAS Enterprise Guide ?

score 0 · Accepted Answer · answered Dec 04 '22 at 04:11

You will need a GROUP BY COL1 clause in order to compute MAX(COL2) within the group, and also a HAVING clause to select the rows with the aggregate computation. Note, there might be two rows with the same max, and thus you will get both in your result set.

Example:

  create table want_table as
  select * from have_table
  group by COL1
  having COL2 = max(COL2)
  ;

How to take max value from one column for each value in second column in table with many other columns in SAS Enterprise Guide?

1 Answers1