topic Latest Claim date and amount in SAS Programming

Latest Claim date and amount

Solly7 — Wed, 07 Oct 2020 11:46:49 GMT

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

Re: Latest Claim date and amount

Kurt_Bremser — Wed, 07 Oct 2020 11:58:59 GMT

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;

Re: Latest Claim date and amount

RichardDeVen — Wed, 07 Oct 2020 12:54:15 GMT

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 */

Re: Latest Claim date and amount

Kurt_Bremser — Wed, 07 Oct 2020 12:55:55 GMT

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