BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASPhile
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

7 REPLIES 7
Reeza
Super User

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

SASPhile
Quartz | Level 8
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 

 

PGStats
Opal | Level 21

Why is custid=2 not flagged?

PG
SASPhile
Quartz | Level 8

because one of the dates is missing

Reeza
Super User
*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;
Reeza
Super User

 

*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;
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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