topic Re: Binning (categorize continuous var into categories) in SAS Programming

Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 07:35:23 GMT

Hello.

Let's say that I have a data set with many continuous variables (independent variables) and response variable (1/0 Default yes or no).

Let's say that for specific variable X I want to categorize this variable into categories .

I have some criteria:

1-Classify to 4 or 5 categories

2-Category 1 is the worse category (The category with highest bads rate)

3-In each category be at least 50 bads (people with Y=1)

4-There must be cardinally in failure rate between groups

5-Must have at least 1% increasing in bads rate from one category to another category.

6-If there are some possible ways that meet 1-5 then choose the option with highest Information value.

May anyone show a nice code that perform this?

What way would you use to perform the binning based on the criteria's I mentioned?

Here is SAS code that show If the conditions I mentioned were meet.

Here can see also IV and see default rate in each category.

As I said, the magic question is how to binning the categories?


PROC FORMAT ;
VALUE IV_fmt
low-<0.02='Very weak'
0.02 <-0.1 ='Weak'
0.1 <-0.3  ='Intermediate'
0.3 <- HIGH ='Strong'
;
RUN;

%macro inf_value(Raw_Data_tbl,var,Response_VAR);
PROC SUMMARY DATA=&Raw_Data_tbl. (keep= &var. &Response_VAR.);
VAR  &Response_VAR.;
OUTPUT OUT=inf0 (DROP=_TYPE_)sum=all_bad;
RUN;

data _null_;
set inf0;
good=_freq_-all_bad;
call symput('all_good',good);
call symput('all_bad',all_bad);
run;
%put &all_good;
%put &all_bad;

PROC SUMMARY DATA=&Raw_Data_tbl. (keep= &var. &Response_VAR.)nway;
class &var.;
VAR  &Response_VAR.;
OUTPUT OUT=inf1 (DROP=_TYPE_ rename=(_freq_=Nr_Customers))sum=Nr_bad;
RUN;

data inf2;
set inf1;
Nr_good=Nr_Customers-Nr_bad;
if &all_bad. not in (0,.) then PCT_Bads=Nr_bad/&all_bad.;
if &all_good. not in (0,.) then PCT_Goods=Nr_good/&all_good.;
res_helkey_non=PCT_Bads/PCT_Goods;
woe=log(res_helkey_non);**weight of evidance***;
iv_categ=(PCT_Bads-PCT_Goods)*woe;
run;

PROC SUMMARY DATA= inf2;
VAR iv_categ;
OUTPUT OUT=inf3 (DROP=_TYPE_)sum=inf_val;
RUN;

data _null_;
set inf3;
call symputx('inf_val',inf_val);
run;
%put &inf_val;/***SUM IV over all categories***/

data inf4;
length var $100. categ $100.;
set inf2;
categ=compress(&var.);
drop &var.;
var="&var.";
P_Default=Nr_bad/Nr_Customers;
label iv='information value(iv)';
run;

data inf5;
retain 
var
categ
Nr_Customers
Nr_bad
Nr_good
PCT_bad
PCT_Bads
PCT_Goods
P_Default
res_helkey_non
woe
iv_categ
;
set inf4; 
format PCT_Bads PCT_Goods res_helkey_non woe iv_categ P_Default percent10.3;
format Nr_Customers Nr_bad Nr_good comma15.  ;
run;


PROC SUMMARY DATA= inf5;
VAR Nr_Customers Nr_bad Nr_good  PCT_Bads  PCT_Goods  iv_categ;
OUTPUT OUT=Summary_IV (DROP=_TYPE_ _freq_) sum=;
RUN;

Data Summary_IV_b;
Retain  field category ;
set Summary_IV;
VAR="&var.";
P_Default=Nr_bad/Nr_Customers;
format P_Default percent8.2;
Run;

Data Want;
set inf5  Summary_IV_b;
IF  missing(categ)  then IV_desc=put(iv_categ,IV_fmt.);
else IV_desc='';
Run;

title;
proc print data=Want noobs;Run;
%mend inf_value;
%inf_value(Raw_Data_tbl=ttt,var=X,Response_VAR=Ind_Default)

Re: Binning (categorize continuous var into categories)

Ksharp — Tue, 23 Jul 2024 08:04:09 GMT

You are building a Score Card ?

The following is the code I used before for this purpose.

/*
proc import datafile='c:\temp\1--German Credit.xlsx' dbms=xlsx out=have replace;
run;
*/

%let var=weight;  *the continuous variable you need to split;
%let group=4 ;    *the number of group you want to bin to;
%let n_iter=100;  *the number of iteration for Genetic Algorithm to get best WOE IV;


data temp;
 set sashelp.heart(obs=200);
 good_bad=ifc(status='Alive','good','bad ');
 if not missing(&var);
 keep &var good_bad ;
run;


proc sql noprint;
 select sum(good_bad='bad'),sum(good_bad='good'),
        floor(min(&var)),ceil(max(&var)) into : n_bad,: n_good,: min,: max
  from temp;
quit;
%put &n_bad &n_good &min &max;
proc sort data=temp;by &var ;run;
proc iml;
use temp(where=(&var is not missing));
read all var {&var good_bad};
close;

start function(x) global(bin,&var ,good_bad,group,woe);
if countunique(x)=group-1 then do;

col_x=t(x);
call sort(col_x,1);
cutpoints= .M//col_x//.I ;
b=bin(&var ,cutpoints,'right');

if countunique(b)=group  then do;
do i=1 to group;
 idx=loc(b=i);
 temp=good_bad[idx];
 n_bad=sum(temp='bad');
 n_good=sum(temp='good');
 bad_dist=n_bad/&n_bad ; 
 good_dist=n_good/&n_good ; 
 if Bad_Dist>0.05 & Good_Dist>0.05  then woe[i]=log(Bad_Dist/Good_Dist);
  else woe[i]=.;
end;

if countmiss(woe)=0 then do;
/*
xx=j(group,1,1)||woe||woe##2;
*/
xx=j(group,1,1)||woe;
beta=solve(xx`*xx,xx`*bin);
yhat=xx*beta;
sse=ssq(bin-yhat);
end;
else sse=999999;

end;
else sse=999999;

end;
else sse=999999;

return (sse);
finish;

group=&group ;  
bin=t(1:group);
woe=j(group,1,.);



encoding=j(2,group-1,&min );
encoding[2,]=&max ;    

id=gasetup(2,group-1,123456789);
call gasetobj(id,0,"function");
call gasetsel(id,10,1,1);
call gainit(id,1000,encoding);


niter =  &n_iter ;
do i = 1 to niter;
 call garegen(id);
 call gagetval(value, id);
end;
call gagetmem(mem, value, id, 1);

col_mem=t(mem);
call sort(col_mem,1);
cutpoints= .M//col_mem//.I ;
b=bin(&var ,cutpoints,'right');

create cutpoints var {cutpoints};
append;
close;
create group var {b};
append;
close;

print value[l = "Min Value:"] ;
call gaend(id);
quit;


data all_group;
 set temp(keep=&var rename=(&var=b) where=(b is missing)) group;
run;
data all;
 merge all_group temp;
 rename b=group;
run;




title "变量: &var" ;
proc sql;
create table woe_&var as
 select group label=' ',
min(&var) as min label='最小值',max(&var) as max label='最大值',count(*) as n label='频数',
calculated n/(select count(*) from all) as per format=percent7.2 label='占比',
sum(good_bad='bad') as n_bad label='bad的个数',sum(good_bad='good') as n_good label='good的个数',
sum(good_bad='bad')/(select sum(good_bad='bad') from all ) as bad_dist label='bad的占比',
sum(good_bad='good')/(select sum(good_bad='good') from all ) as good_dist label='good的占比',
log(calculated Bad_Dist/calculated Good_Dist) as woe
from all
   group by group
    order by woe;

create index group on woe_&var;

select *,sum(  (Bad_Dist-Good_Dist)*woe  ) as iv
 from woe_&var ;

quit;
title ' ';




/*
data fmt_&var ;
 set cutpoints;
 start=lag(cutpoints);
 end=cutpoints;
 if start=.M then hlo='IL';
 if end=.I then hlo='IH';
 if _n_ ne 1 then do;group+1;output;end;
run;
data fmt_&var(index=(group));
 merge  fmt_&var woe_&var(keep=group woe);
 by group;
 retain fmtname "&var" type 'I';
 keep group fmtname type start end woe hlo;
 rename woe=label;
 label group=' ';
run;
proc format cntlin=fmt_&var library=z;
run;



proc print data=woe_&var noobs label;run;
proc sgplot data=woe_&var;
reg y=group x=woe/degree=2 cli clm jitter;
run;
*/
proc sgplot data=woe_&var noautolegend;
 vbar group/response=woe nostatlabel missing;
 vline group/response=woe nostatlabel missing markers MARKERATTRS=(symbol=circlefilled 
  size=12) MARKERFILLATTRS=(color=white) MARKEROUTLINEATTRS=graphdata1
  FILLEDOUTLINEDMARKERS;
run;

Re: Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 08:24:00 GMT

Thank you so so much!

Yes, build credit risk model (ScoreCard).

Can I ask please regarding the criteria I mentioned:

Classify to 4 or 5 categories ----Defined in macro var %let group

Category 1 is the worse category (The category with highest bads rate)--Where did you define it?

In each category be at least 50 bads (people with Y=1)--Where did you define it?

Must have at least 1% increasing in bads rate from one category to another category.--Where did you define it?

Re: Binning (categorize continuous var into categories)

Ksharp — Tue, 23 Jul 2024 08:54:54 GMT

Classify to 4 or 5 categories ----Defined in macro var %let group
Answer: Yes.

Category 1 is the worse category (The category with highest bads rate)--Where did you define it?
Answer: Here.Category 4 is worse , You could see Category 4 has the lowest WOE, If you want Category 1 be, you could change group number reversely.
But that did not mean the Category 4 has the highest bads rate. I did not consider this criteria . I only make the IV be the highest.

In each category be at least 50 bads (people with Y=1)--Where did you define it?
Answer: If you have more obs than 200 ,this criteria should be meet.But I don't guarantee it ,since I only make the IV be the highest.

Must have at least 1% increasing in bads rate from one category to another category.--Where did you define it?
Answer: If you have more data,that would be happend.But I don't guarantee it ,since I only make the IV be the highest.

Re: Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 09:06:30 GMT

Thanks,

If in data set have 100,000 rows.

Then how long approximately should it take to run the 100 iterations for one var only?

Which code should Modify that worse group be 1 and best group be 4 ( define 4 groups)?

Can you show the code please?

Re: Binning (categorize continuous var into categories)

Ksharp — Tue, 23 Jul 2024 09:11:14 GMT

If you need get these criteria , I suggest you to post it at OR forum:
https://communities.sas.com/t5/Mathematical-Optimization/bd-p/operations_research

And calling out @RobPratt

Re: Binning (categorize continuous var into categories)

Ksharp — Tue, 23 Jul 2024 09:16:37 GMT

You could test it by yourself. But I think that would be very slowly, at least 3 minutes I guesss.
Here change group number:
data all;
merge all_group temp;
b=5-b; /*<----------*/
rename b=group;
run;

Re: Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 09:48:29 GMT

I run it more than 2 hours.......still didnt finish (Run for one variable only)

Re: Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 09:55:37 GMT

you mean 3 minutes for each iteration?

so for 100 iterations will have 300 minutes?

Re: Binning (categorize continuous var into categories)

Ronein — Tue, 23 Jul 2024 13:26:36 GMT

Sorry I didn't understand. Which criteria ( what question) do you recommend me to post in this OR forum? What is the purpose of that forum please?

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 00:46:57 GMT

Yes. 3 minutes for one variable.
Since you tested it for more than two hours , try to reduce %let n_iter=100 .

Or get more people to run code, each for one variable?

P.S. I do not recommend to reduce "%let n_iter= 100", and it is other way around, I would have more than 100 to get better IV .

In summary, if you want fastest way to solve your question, post your question at OR forum and calling @RobPratt .

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 00:17:17 GMT

All the conditions you mentioned ----> "I have some criteria:"

SAS/OR is for solving a optimal problem,
since your question looks like searching a optimal value ,you could try to get helpl from @RobPratt
Try post your question at OR forum.

Re: Binning (categorize continuous var into categories)

Ronein — Wed, 24 Jul 2024 05:51:10 GMT

I run your original code:

In data set CUTPOINTS there is one column called CUTPOINTS (numeric var with no format ).

How come I see letter values when the column is numeric??

What is the meaning of values "M" and "I"?

I also added to final table percent of bads within each group

Re: Binning (categorize continuous var into categories)

Ronein — Wed, 24 Jul 2024 05:12:04 GMT

run your code on my data on specific variable (Nr Years in bank )

In your code the best group should be 1 and worse should be 4

I don't see that Per increasing consistently by moving from group 1 to 2 and 2 to 3 and 3 to 4.

Is it not part of the requirement??? To have consistently increasing in per (percentage of bads)???

Re: Binning (categorize continuous var into categories)

Ronein — Wed, 24 Jul 2024 05:17:10 GMT

Thanks,

Not always number of groups is 4 .

I also want that worse group be 1 and not 0.

Here is the code that do it 100%


proc sql noprint;
select count(distinct b) as nr_groups into :nr_groups
from all_group
;
quit;
%put &nr_groups;


data all;
merge all_group temp;
b=(&nr_groups.-b)+1; 
/***I want that worse group be group 1 and so on****/
rename b=group;
Run;

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 06:20:12 GMT

"What is the meaning of values "M" and "I"?"
It is means negative infinity and positive infinity .
A.K.A you can map your data into bins by these cutpoints:
low-137='1'
137-144='2'
144-174='3'
174-high='4'

"I also added to final table percent of bads within each group"
And also glad to see you make my code running to get the perfect result . Congratulations!

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 06:27:05 GMT

And about your time costing problem,
You could start 10 sas session ,each session for ONE variable,that could save you lots of time.
P.S. if you could set "%let n_iter=100" as big as you can to get the better IV.

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 06:32:16 GMT

"I don't see that Per increasing consistently by moving from group 1 to 2 and 2 to 3 and 3 to 4."
You should calcualte it on your own, my code did not include it.
bad_pct=n_bad/n;
====>
group bad_pct
1 0.062
2 0.036
3 0.02
4 0.011

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 06:36:20 GMT

No need add one more PROC SQL,just use macro variable &group. at top of code.

%let var=weight; *the continuous variable you need to split;
%let group=4 ; *the number of group you want to bin to;
...........

data all;
merge all_group temp;
b=(&group.-b)+1;
/***I want that worse group be group 1 and so on****/
rename b=group;
Run;

Re: Binning (categorize continuous var into categories)

Ksharp — Wed, 24 Jul 2024 06:38:23 GMT

"M" and "I"
represent two missing value .M and .I ,
.M stands for negative infinity,
.I stands for positive infinity.