消费信用中的漏斗图

现在的位置: 首页 > 综合 > 正文

消费信用中的漏斗图

2018年10月21日 ⁄ 综合 ⁄ 共 4252字 ⁄ 字号小中大 ⁄ 评论关闭

"Application of SAS to monitoring loan defaults in consumer credit portfolios“

libname maps 'D:\Program Files\SAS\SASFoundation\9.2\maps\'; 
libname temp 'D:\temp\sas Temporary Files';

/* Resetting SAS global graphical settings to accommodate the charts in the best way*/
options fmtsearch=(sashelp.mapfmts); 
goptions reset=global 
         rotate=landscape 
         gunit=pct 
         border 
         cback=white 
         colors=(blue green lime lipk cyan red) 
         ctext=black ftext=swiss 
         htitle=2.5 htext=2.5;


/* Generating random values for volume and delinquency rates at the state level */
data usmap; 
   set maps.us2 (keep = STATE STATECODE STATENAME); 
   do i =1 to 52; 
      dlqrt=rand('BETA',0.98,97.02); 
      volume=10*rand('NEGBINOMIAL',0.05,2); 
   end; 
   drop i; 
   label dlqrt='Observed delinquency rate'; 
   label volume='Loan volume'; 
   label STATECODE='State name abbreviation'; 
run;

proc sort data=usmap; by STATECODE; run;

/* Constructing risk level bounds and classifying the states by delinquency rate */

proc sql; 
   create table temp.usmap as 
   select *, 
          avg(dlqrt) as dlqrt_us 
          label='US average delinquency rate',

	/*90% CI: 5%-95% boundaries: */ 
   calculated dlqrt_us+1.645*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume) as dlqrt_95pct 
   label='95% delinquency rate bound',

   case when 
        (calculated dlqrt_us-1.645*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume))<0 
           then 0 
        when (calculated dlqrt_us-1.645*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume))>1 
           then 1 
   else calculated dlqrt_us-1.645*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume) end as dlqrt_5pct 
   label='5% delinquency rate bound',

   /*80% CI: 10%-90% boundaries: 1.281 */ 
   calculated dlqrt_us+1.281*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume) as dlqrt_90pct 
   label='90% delinquency rate bound',

   case when (calculated dlqrt_us-1.281*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume))<0 then 0 
   when (calculated dlqrt_us-1.281*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume))>1 then 1 else calculated dlqrt_us-1.281*sqrt(calculated dlqrt_us*(1-calculated dlqrt_us)/volume) end as dlqrt_10pct 
   label='10% delinquency bound',

   /* Classifying in risk levels */ 
   case when (calculated dlqrt_95pct)<=dlqrt then 'Extremely high' 
   when (calculated dlqrt_90pct)<=dlqrt<(calculated dlqrt_95pct) then 'Moderate high' 
   when (calculated dlqrt_10pct)<dlqrt<(calculated dlqrt_90pct) then 'Normal' when (calculated dlqrt_5pct)<dlqrt<=(calculated dlqrt_10pct) then 'Moderate low' 
   else 'Extremely low' end as risk_level 

   from usmap; 
quit;


/* Constructing Chart 1: Funnel plot */ /* Auxiliary work: idetifying and marking the outliers*/ 
data temp.outliers;
   set temp.usmap (keep= STATECODE STATENAME volume dlqrt dlqrt_90pct dlqrt_10pct);
   where dlqrt>=dlqrt_90pct or dlqrt<=dlqrt_10pct; 
   format dlqrt 6.5 dlqrt_90pct 6.5 dlqrt_10pct 6.5 flag_print $CHAR15.;
   select; 
      when(dlqrt>=dlqrt_90pct) flag_print='Up-outlier';
      when(dlqrt<=dlqrt_10pct) flag_print='Down-outlier'; 
      otherwise flag_print='Non-outlier';
   end;
   XSYS = '2';YSYS = '2';
   X = volume;Y = dlqrt;
   FUNCTION='LABEL';
   function='label'; 
   text=STATECODE;
   size=1; 
   position='1';
   color='red'; 
   output; 
run;


proc sort data=temp.outliers; 
   by flag_print descending volume;
run;

/* Developing a funnel plot */
proc sort data=temp.usmap; 
   by volume;
run;


symbol1 v=star i=none h=1 w=2 c=black; 
/* Actual Observations */ 
symbol2 v=none i=spline line=1 w=2 c=red;
/* Extremely high*/ 
symbol3 v=none i=spline line=1 w=2 c=blue; 
/* Extremely low*/ 
symbol4 v=none i=spline line=1 w=2 c=pink;
/* Moderate high */
symbol5 v=none i=spline line=1 w=2 c=cyan; 
/* Moderate low: */
symbol6 v=none i=spline line=1 w=2 c=lime;
/* Normal*/
symbol7 v=none i=join line=21 w=1 c=black;

ods graphics on; 
ods html; 
proc gplot data=temp.usmap;
   title1 j=c h=3 c=black 
      'Funnel plot. 90-day delinquency rate in credit card loans in the USA';
   axis1 label=(c=black a=90 h=2.5 '90-day delinquency rate') 
         value=(c=black)
         order=(-0.005 to 0.05 by 0.005);
   axis2 label=(c=black h=2.5 'Loan volume') 
         value=(c=black);
   plot dlqrt*volume=1 
        dlqrt_95pct*volume=2 
        dlqrt_5pct*volume=3 
        dlqrt_90pct*volume=4 
        dlqrt_10pct*volume=5 
        dlqrt_us*volume=7 
        /overlay
		legend=legend1

   vaxis=axis1 
   haxis=axis2 
   cframe = white 
   hminor = 0 
   vminor =0 
   anno=temp.outliers; 
   legend1 value=(justify=left) label=('Legend:' justify=left
           position=(middle left)) 
           value=(h=2.5) frame;
run; 
ods graphics off;


proc print data=temp.outliers noobs label; 
   var STATECODE STATENAME volume dlqrt dlqrt_90pct; 
   where flag_print ='Up-outlier';
   title 'Table. The US states with high delinquency rate'; 
run;