DATA Step, Macro, Functions and more

max paydate

Accepted Solution Solved
Reply
Super Contributor
Posts: 647
Accepted Solution

max paydate

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?


Accepted Solutions
Solution
‎05-09-2017 12:05 PM
Super User
Posts: 9,681

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

View solution in original post


All Replies
Super User
Posts: 17,829

Re: max paydate

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

Super Contributor
Posts: 647

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 

 

Respected Advisor
Posts: 4,649

Re: max paydate

Why is custid=2 not flagged?

PG
Super Contributor
Posts: 647

Re: max paydate

because one of the dates is missing

Super User
Posts: 17,829

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: 17,829

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: 9,681

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
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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 176 views
  • 1 like
  • 4 in conversation