topic Re: Optimal binning in SAS Data Science

Optimal binning

chemicalab — Fri, 27 Sep 2013 13:06:38 GMT

Hi all,

Is there any source or idea on how to perform optimal binning in SAS Base besides complicated algorithms that usually dont work?

I am looking for something straightforward or more simple version that is easy to understand.

Thank you in advance

Re: Optimal binning

ballardw — Fri, 27 Sep 2013 15:01:40 GMT

What do you have to bin and how many bins do think you'll need? A brief description of input data and desired output is helpful.

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 15:08:15 GMT

well i have lets say two continuous variables against a binary response, number of bins optimal in accordance with what i have, although i prefer lets say max 4 or 5 so i able to control the estimate signs in the regression later on

Re: Optimal binning

ballardw — Fri, 27 Sep 2013 15:26:23 GMT

If you know the ranges of interest in the continuous variables I would recommend a custom format for each variable. Most of the analysis procedures will use the formatted value either by default or can be set to use the format. If the ranges don't work quite the way you want then you just change the format definition and don't have to create new variables.

Re: Optimal binning

Reeza — Fri, 27 Sep 2013 15:29:15 GMT

There's also the question of why bin entirely, rather than use the continuous variable?

If your in eMiner, consider running a tree and seeing where the cutoff's occur for those variables.

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 15:47:51 GMT

unfortunately i am not in EM otherwise i would use interactive binning to define myself based on event rate and gini, in general binning is preferable to handle rare levels and missing as well as outliers. Besides the relationship between the continuous and target is not always linear so it would lead to instable model cause the effects wouldnt be captured

Re: Optimal binning

Reeza — Fri, 27 Sep 2013 15:53:50 GMT

Ok, Are you looking for how to determine the cutoffs for the bins or how to implement said cutoffs?

If how to implement, suggestion of formats is the one I'd also recommend.

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 16:02:21 GMT

yes i guess thats what optimal binning would do so yes i am looking for the cut off values for creating bins that would give me the highest gini ratio.

I was just wondering if there was any tested way in code so i could try out instead of depending on some descriptives and if then else clauses.

Basically thats what i need cause i can calcultate the gini myself once i have the groups formed.

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 16:21:50 GMT

any suggestions or example on the format ? i am not sure i follow

thnx

Re: Optimal binning

M_Maldonado — Fri, 27 Sep 2013 17:22:03 GMT

Read about dynamic formats and informats here: http://www.lexjansen.com/pharmasug/2005/posters/po06.pdf

Just one of many papers in the subject.

What are the complicated algorithms that are not working for you?

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 18:38:56 GMT

a lot you find on the net that are supposively creating optimal bins, also in data mining preparation tools their macros do not work as they should , anyways i will look into the paper you sent me , thank you for the reply

Re: Optimal binning

M_Maldonado — Fri, 27 Sep 2013 18:53:32 GMT

I know advanced constraints have to be tweaked sometimes to get the most out of Interactive Binning or enterprise miner. If you are not getting the results you expect, contact sas tech support http://support.sas.com/techsup/contact/. they usually reply very fast.

good luck!

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 18:58:51 GMT

yeas you are right, in EM and the interactive binning node i have spent a lot of times creating own splits casue the ones you get are not always so good , in most cases it doesnt create bins even there are opportunities for it, i guess i will create something in a macro form to change cut offs every time , cause i am afraid that something automated that you usually encounter means more number of bins then possible to support later on the regression cause its just looking to maximize the gini ratio.

Thank you for your time

Re: Optimal binning

M_Maldonado — Fri, 27 Sep 2013 19:24:07 GMT

you need to give tech support a call. we got a fix for proc optbin. long story short, our server was not fast enough for the data we handle. they gave us a hotfix to omit some max time option that was stopping the binning process before time.

if EM is not creating bins where it should, it sounds like you might want to get rid of a couple constraints on the "advanced constrained optimal" options.

but since you have put so much time on this, just let sas figure out what you need to tweak. that's the real value of your sas license, my two cents.

Re: Optimal binning

Reeza — Fri, 27 Sep 2013 19:27:01 GMT

That sounds like a CART or CHAID process with a single variable perhaps? Could you use the CHAID macro out there, you may need to contact

http://listserv.uga.edu/cgi-bin/wa?A2=ind1309C&L=sas-l&D=0&P=5029

Re: Optimal binning

M_Maldonado — Fri, 27 Sep 2013 19:33:20 GMT

cart or chaid are just decision tree algorithms. yes, you will find some groupings using decision trees. but I think wants an optimal grouping solution, not just a grouping. tree-based grouping is a good start though. although EM binning has that too, I think.

Re: Optimal binning

chemicalab — Fri, 27 Sep 2013 19:56:44 GMT

From what i have seen using EM the binning or the grouping is done in accordance to maximize the gini ratio or IV , problem is that although automated it is still necessary to handle the binning manually in order to merge or split bins cause the event rate doesnt make sense otherwise and will for sure cause problems when checking the estimates of your logistic regression (they will have wrong signs).

Ofcourse the more bins the higher the gini ratio but believe me the regression signs will go bananas so its quite difficult to handle a large number of bins.

I will try to create some code keeping into mind a monotonically increasing or decreasing event rate (or WOE if prefered) and play interactively with it just like in EM cause i dont have it now. The gini calculation after that is solved by a sas code.

I was reading this book Mamdouh Refaat - Data Preparation for Data Mining Using SAS - 2006 which has a optimal binning macro code but believe me its too complicated to read through and always gets me errors when trying to apply, long story short i believe there are smaller and more efficient ways for it and that was what i was looking for with my initial enquiry

Re: Optimal binning

JasonXin — Sun, 29 Sep 2013 00:05:50 GMT

1. Regarding conflicting signs: while recommending PCA of variables, or variable clustering, may be 'out of place' for binning, you may want to run variable clustering to see what variables are 'together'. That knowledge will help advice you in the variable sign conflicting situation.

2. If you are into binning, manual tweaking appears inevitable. Except if you are building a large number of score cards, you don't care /have time to care about 80% of them. So you set the criteria and as long as they don't fall out of boundary you don't tweak them. That is one of several trends among EM users.

3. If you are still with EM, you may also want to just let the final model performance to help you as well. One of my favorite EM nodes is Model Comparison. Practically, copying and pasting modeling flow is much easier than manual tweaking. Go ahead, be bold, lay out many model path, let the node names reflecting your subtle changes, see what performance has to say. In a more critical aspect, model target definition should follow the same performance practice since the correctness of the target definition is about the 'quality of the war' what binning is local and tactical.

Jason Xin

Re: Optimal binning

chemicalab — Sun, 29 Sep 2013 10:09:47 GMT

I agree on the three points you make, just one addition on point 1, I believe based on what i have encountered before that you can get the sense that variable clustering offers by checking the IV or Gini ratio of the variables, usually variables with similar values on those metrics tend to be conflicting, the beauty though of the logistic regression is that it will eliminate one of them during the backward procedure.

Re: Optimal binning

JasonXin — Sun, 29 Sep 2013 13:47:23 GMT

You just said a very important word, SENSE. Optimal binning should never be taken as to mean one-pass through. Thanks.

Jason Xin