Latest Claim date and amount

[Solly7]

Hi I need help in extracting last claim date and corresponding claim amount

data HAVE;

input policy_no$ dt;

input amount$;

informat dt datetime.;

format dt datetime.;

cards;

1     500        21MAY2018

1     3000     22DEC2018

2     800      01JAN2019

3     550      21MAY2017

3     130      05JUN2020

run;

DATA WANT

policy_no    amount         date

1                 3000        22DEC2018

2                 800          01JAN2019

3                130           05JUN2020

[Kurt_Bremser · Accepted solution]

Please test your data step code before posting, so we do not have to fix it.

See this:

data HAVE;
input policy_no $ amount dt :date9.;
format dt date9.;
cards;
1     500        21MAY2018
1     3000     22DEC2018
2     800      01JAN2019
3     550      21MAY2017
3     130      05JUN2020
;

data want;
set have;
by policy_no dt; /* dt included here to force correct sort order */
if last.policy_no;
run;

[RichardDeVen]

Just to clarify for beginners

by policy_no dt; /* dt included here to force correct sort order */

The DT in the BY statement does not force or implicitly sort the data.  The DT ensures proper operation of the algorithm (i.e. business rule) because the DATA Step will ERROR: if the data are not in the expected sorted order.  A more precise comment might be  /* dt included here to assert correct sort order */

[Kurt_Bremser]

Nice correction; after all I am not a native English speaker, and sometimes miss out on nuances.