topic Record with max date in SAS Programming

Record with max date

nickspencer — Wed, 05 Sep 2018 18:31:33 GMT

Hi,
I Have a table that has record with multiple dates. I need to create a dataset with each record with maximum date. The source table looks as below.

ID STATE eff_date
123 CA 10/18/2017
123 CA 10/18/2017
123 NY 3/25/2018
123 NY 3/25/2018
234 AZ 1/1/2018
234 AZ 7/18/2018
234 ? 7/18/2018
I need to pick the ID and state with maximum effective date. I need to pick just one id and a state even though they have duplicates.
Any logic for this ?

Thanks

Re: Record with max date

novinosrin — Wed, 05 Sep 2018 18:43:30 GMT


data have;
input ID STATE $ eff_date : mmddyy10.;
format eff_date mmddyy10.;
cards;
123 CA 10/18/2017
123 CA 10/18/2017
123 NY 3/25/2018
123 NY 3/25/2018
234 AZ 1/1/2018
234 AZ 7/18/2018
;
proc sort data=have out=_have;
by id state descending eff_date;
run;


data want;
set _have;
by id state descending eff_date;
if first.state;
run;


proc sort data=have out=_have;
by id state  eff_date;
run;


data want;
set _have;
by id state  eff_date;
if last.state;
run;

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

Re: Record with max date

PGStats — Wed, 05 Sep 2018 18:52:04 GMT

Which record do you want when the latest date is duplicated?

Re: Record with max date

nickspencer — Wed, 05 Sep 2018 18:55:13 GMT

@PGStats I see the city values are same when eff date is duplicated while some have null values.

Re: Record with max date

novinosrin — Wed, 05 Sep 2018 19:01:53 GMT

Please provide us a better and comprehensive sample of what you have and your required output for the sample

Re: Record with max date

PGStats — Wed, 05 Sep 2018 19:09:39 GMT

Assuming you means STATE and not CITY and that you prefer a record with a non missing STATE when there there is one but a missing STATE when it's the only one on the LAST eff_date :

data test;
input ID STATE $ eff_date :mmddyy.;
format eff_date yymmdd10.;
datalines;
123 CA 10/18/2017
123 CA 10/18/2017
123 NY 3/25/2018
123 NY 3/25/2018
234 AZ 1/1/2018
234 AZ 7/18/2018
234 . 7/18/2018
;

proc sort data=test out=last; 
by descending eff_date descending state; run;

data want; set last(obs=1); run;

proc print; run;