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