topic Re: proc sql-select one row by group with max date value in SAS Programming

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

Ronein — Thu, 07 Sep 2023 05:23:43 GMT

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;

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

yabwon — Thu, 07 Sep 2023 05:34:03 GMT

select distinct *...

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

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

Ronein — Thu, 07 Sep 2023 05:49:59 GMT

Great

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