group-bysasmaxenterprise-guide4gl

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


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

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 ?


Solution

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