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