CODEWITHSUNDEEP
group-bysasmaxenterprise-guide4gl
dingaro
dingaro

Reputation: 2342

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 ?

Upvotes: 0

Views: 695

Answers (1)

Richard
Richard

Reputation: 27508

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

Upvotes: 0

Related Questions