## max paydate

Solved
Super Contributor
Posts: 717

# max paydate

custid   action                          paydate
1        paid                            01-01-2016
1        miss                            02-04-2016

2        paid                            01-01-2016
2        miss                               NA

4        paid                            01-01-2016
4        miss                            09-04-2016

5        paid                            01-01-2016
5        miss                            09-04-2016

in the above test cases,a flag has to show "check status" on those records
when the record is "lost to follow" is  the max paydate for that custid,
and if a custid has more than one record and any of the paydate is not 'NA'.

so in the above custid 1&3  will be part of the rule set. Any help?

Accepted Solutions
Solution
‎05-09-2017 12:05 PM
Super User
Posts: 10,850

## Re: max paydate

``````data have;
input custid   action    & \$20.                      paydate :\$20.;
date=input(paydate,?? mmddyy10.);
format date mmddyy10.;
cards;
1        paid                            01-01-2016
1        miss                            02-04-2016
2        paid                            01-01-2016
2        miss                               NA
4        paid                            01-01-2016
4        miss                            09-04-2016
5        paid                            01-01-2016
5        miss                            09-04-2016
;
run;
proc sort data=have;by custid date;run;
data want;
do until(last.custid);
set have;
by custid;
if paydate='NA' then has_na=1;
end;
do until(last.custid);
set have;
by custid;
if last.custid and action='lost follow up' and not has_na then
status='Check me';
output;
end;

run;``````

All Replies
Super User
Posts: 24,012

## Re: max paydate

Please show what the output would look like for your sample data.

Super Contributor
Posts: 717

## Re: max paydate

 custid    action                          paydate flag 1        miss                               02-04-2016 1        paid                               01-01-2016 1        lost follow up              08-24-2016 check status 2        paid                               01-01-2016 2        miss                               NA 2       lost follow up               08-24-2016 3       lost follow up                08-24-2016 check status 4        paid                              01-01-2016 4        miss                             09-04-2016 4       lost follow up            08-24-2016 5        paid                            01-01-2016 5        miss                            09-04-2016 5        lost follow up                     NA

Posts: 5,625

## Re: max paydate

Why is custid=2 not flagged?

PG
Super Contributor
Posts: 717

## Re: max paydate

because one of the dates is missing

Super User
Posts: 24,012

## Re: max paydate

``````*Sort by id and pay date, this puts the largest as the last record;
proc sort data=have;
by id paydate;
run;

data want;
set have;
by id;
if first.id then na_flag=0;

if paydate='NA' then na_flag=1;

*Check rule, last record and max paydate;
if last.id and action= 'action to check' and na_flag ne 1 then status='Check me';

run;``````
Super User
Posts: 24,012

## Re: max paydate

``````*Sort by id and pay date, this puts the largest as the last record;
proc sort data=have;
by id paydate;
run;

data want;
set have;
by id;

*Check rule, last record and max paydate;
if last.id and action= 'action to check' then status='Check me';

run;``````
Solution
‎05-09-2017 12:05 PM
Super User
Posts: 10,850

## Re: max paydate

``````data have;
input custid   action    & \$20.                      paydate :\$20.;
date=input(paydate,?? mmddyy10.);
format date mmddyy10.;
cards;
1        paid                            01-01-2016
1        miss                            02-04-2016
2        paid                            01-01-2016
2        miss                               NA
4        paid                            01-01-2016
4        miss                            09-04-2016
5        paid                            01-01-2016
5        miss                            09-04-2016
;
run;
proc sort data=have;by custid date;run;
data want;
do until(last.custid);
set have;
by custid;
if paydate='NA' then has_na=1;
end;
do until(last.custid);
set have;
by custid;
if last.custid and action='lost follow up' and not has_na then
status='Check me';
output;
end;

run;``````
☑ This topic is solved.