topic Re: max paydate in SAS Programming

max paydate

SASPhile — Thu, 20 Apr 2017 20:12:16 GMT

custid   action                          paydate
1        paid                            01-01-2016
1        miss                            02-04-2016
1        lost follow up                  08-24-2016

2        paid                            01-01-2016
2        miss                               NA
2       lost follow up                   08-24-2016

3 lost follow up 08-24-2016

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

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?

Re: max paydate

Reeza — Thu, 20 Apr 2017 20:28:28 GMT

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

Re: max paydate

SASPhile — Thu, 20 Apr 2017 20:36:26 GMT

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

Re: max paydate

Reeza — Thu, 20 Apr 2017 20:41:06 GMT

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

Re: max paydate

PGStats — Fri, 21 Apr 2017 02:20:47 GMT

Why is custid=2 not flagged?

Re: max paydate

Ksharp — Fri, 21 Apr 2017 03:02:06 GMT

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
1        lost follow up                  08-24-2016
2        paid                            01-01-2016
2        miss                               NA
2       lost follow up                   08-24-2016
3       lost follow up                   08-24-2016
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
;
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;

Re: max paydate

SASPhile — Fri, 21 Apr 2017 15:01:59 GMT

because one of the dates is missing

Re: max paydate

Reeza — Fri, 21 Apr 2017 15:14:31 GMT

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