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

hi,

 

Looking for suggestions in order to flag agents who agree to Lend $ vs those who decline to Lend.

 

The objective is for management to see if there are any education opportunities for either agents who are either deviating from peers /have a bias toward declining, or have a bias toward lending.

 

Example data set (where agree_lend 1=lend, and 0=not lend:

 

name         agreed_lend    date

 

Marc             1                 01mar2017

Marc             0                01mar2017

Marc            1                 03mar2017
...

James          0                 02mar2017

...

 

Summary table for Month:

 

name         agreed_lend_sum   agreed_lend_count       ratio

Marc                    336                        562                         60%

James                    32                           33                         97%

 

*As James' overall volume of decisions is small, the ratio is not very meaningful.

 

I appreciate any suggestion anyone as to offer, many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

Just to get things started.  Let's assume that the incoming data set is reasonable sized - so that it can be sorted.  Here is a DATA step that will get you a summary.  The boundary conditions are arbitrary here.

* Fake data;
data have;
input name $ approved;
datalines;
frank 1
joe 1
joe 0
mary 1
joe 1
frank 1
mary 0
mary 1
frank 1
joe 0
bill 0
frank 1
mary 1
run;
proc sort data=have;
   by name;
   run;
data want(keep=name loancnt lendcnt ratio flag);
   set have;
   by name;
   if first.name then do;
      loancnt=0;
      lendcnt=0;
   end;
   loancnt+1;
   lendcnt+approved;
   if last.name then do;
      ratio = lendcnt/loancnt;
      if not(.2 le ratio le .8) then flag="*";
      if loancnt>2 then output want;
   end;
   run;

View solution in original post

4 REPLIES 4
ArtC
Rhodochrosite | Level 12
@brulard i suspect that the lack of replies is an indication that your question is not specific enough. Are you asking for suggestions on statistical tests or for an easy way to generate the summary stats ( given that you have the binary variable)?
brulard
Pyrite | Level 9

thanks for your feedback... my question is likely too broad. I am looking for suggestions on a means of identifying/highlighting meaningful outliers in my ouput, either through summary, or statistical procedure.

Looking at the few examples below, the approval ratio for Marc, Mary and John is around 60%.

I'd like to be able to flag Jane and Abe as too conservative/liberal in terms of their peer average. I would not consider James, as his overall decisions is too low.

 

name         agreed_to_lend   total_decisions     Approval_ratio
Marc          336                        562                60%
James          32                         33                97%
Jane          100                        500                20%
Mary          310                        515                60%
Abe           450                        498                90%
John          350                        575                61%
...

ArtC
Rhodochrosite | Level 12

Just to get things started.  Let's assume that the incoming data set is reasonable sized - so that it can be sorted.  Here is a DATA step that will get you a summary.  The boundary conditions are arbitrary here.

* Fake data;
data have;
input name $ approved;
datalines;
frank 1
joe 1
joe 0
mary 1
joe 1
frank 1
mary 0
mary 1
frank 1
joe 0
bill 0
frank 1
mary 1
run;
proc sort data=have;
   by name;
   run;
data want(keep=name loancnt lendcnt ratio flag);
   set have;
   by name;
   if first.name then do;
      loancnt=0;
      lendcnt=0;
   end;
   loancnt+1;
   lendcnt+approved;
   if last.name then do;
      ratio = lendcnt/loancnt;
      if not(.2 le ratio le .8) then flag="*";
      if loancnt>2 then output want;
   end;
   run;
brulard
Pyrite | Level 9
hi ArtC, your solution is great start, thank you!

If there are some procedure/techniques that you could recommend I study in terms of potentially automating the flag criteria, please let me know! Currently, the volume I look at is about 175 decisions per agent, monthly.

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
  • 4 replies
  • 526 views
  • 0 likes
  • 2 in conversation