HOW TO EXTRACT LATEST DATE

[tekish]

Dear Expert,

I want to extract the  latest date from the date time stamp values.

here is the sample code.

data HAVE;

input ID$ dt;

informat dt datetime.;

format dt datetime.;

cards;

 A  21MAY2018:09:27:00

A  21MAY2018:09:29:00

B  21MAY2018:09:30:01

B  21MAY2018:09:35:00

C  21MAY2018:09:40:00

C  21MAY2018:09:40:01

C  21MAY2018:09:40:10

C  21MAY2018:09:41:10

run;

PROC SQL;

CREATE TABLE WANT AS

SELECT ID,DT

 FROM

HAVE

GROUP BY ID

 HAVING MAX(ID)

ORDER BY ID,DT;

QUIT;

EXPECTED OUTPUT

FINAL RESULT

A 21MAY2018:09:29:00

B 21MAY2018:09:35:00

C 21MAY2018:09:41:10

[Kurt_Bremser]

proc sql;
create table want as
select id, dt
from have
group by id
having dt = max(dt);
quit;

[novinosrin · Accepted solution]

data HAVE;

input ID$ dt;

informat dt datetime.;

format dt datetime.;

cards;
 A  21MAY2018:09:27:00
A  21MAY2018:09:29:00
B  21MAY2018:09:30:01
B  21MAY2018:09:35:00
C  21MAY2018:09:40:00
C  21MAY2018:09:40:01
C  21MAY2018:09:40:10
C  21MAY2018:09:41:10
;
run;

proc sql;
create table want as
select id,max(dt) as dt format=datetime.
from have
group by id;
quit;