proc sql-select one row by group with max date value

[Ronein]

Hello

I want to use proc sql to select one row by group.

The criteria is to select  for each group the row with max value of date.

However, if there are multiple rows with max date value then this query is not working well because it select multiple rows by group.

What is the way to solve it via proc sql?

data have;
input ID $ date : date9. var;
format date date9.;
cards;
B 05JAN2023 5
D 24JAN2023 2
B 06JAN2023 6
B 06JAN2023 6
B 06JAN2023 6
B 06JAN2023 6
C 15JAN2023 7
A 01JAN2023 10
C 16JAN2023 8
C 19JAN2023 9
C 20JAN2023 10
C 20JAN2023 10
C 20JAN2023 10
;
Run;

proc sql;
create table Way1 as
SELECT *
from have
group by ID
having date=max(date)
;
quit;

[yabwon · Accepted solution]

select distinct *...

Use Maxim 1 and read here: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0mo9ak7pmhfjhn1fw5crkp17jg0.htm#p02x...

[Ronein]

Great

proc sql;
create table want as
SELECT distinct *
from have
group by ID
having date=max(date)
;
quit;