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?
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;
Please show what the output would look like for your sample data.
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 |
Why is custid=2 not flagged?
because one of the dates is missing
*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;
*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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.