@Ronein wrote:
The metric to calculate the value of new "Mis" field for each customer is
New_Mis=min((Use*Factor),Mis);
Where can I see this formula in your code please?
My code focuses on finding the desired value of variable Factor. You can then use this value in another DATA (or PROC SQL) step to compute New_Mis with your formula. You had done this already, so I assumed that you don't need help with this step. To quote your original post:
@Ronein wrote:
The question is how to find the factor that will reduce SUM of MIS by 200 .
The intended reduction (200) is stored in macro variable threshold in my code. The factor in question is eventually the only value in dataset WANT. You can apply your macro mmacro to confirm that (or: to check if) this factor does solve your problem (and I would recommend doing so). The output dataset of @gamotte's code (HISTORY) is more informative as it contains other interesting quantities as well.
@Ronein wrote:
The factor should be apply on "Use" field and not on "Mis" field
In the code that you sent I think that by mistake factor is applied on "Mis" field
I'm not sure where you see this. The only formula containing factor in my code is
factor=(s-&threshold)/t;
where s and t are certain (partial) sums of Mis and Use values, respectively. You could rewrite this formula (mathematically) as
s - factor*t = &threshold
Now you see that factor is applied to t, i.e., to a sum of Use values.
Thanks for providing more background information about your question. I guess that among a million customers some didn't use their credit card at all, so Use would be zero for them, right? As mentioned previously, my current code cannot handle this case appropriately because it computes ratios with Use in the denominator. As a quick fix I suggest the following changes to the SELECT statement in the PROC SQL step creating view vtemp:
select mis, max(use,1e-50) as use, round(mis/calculated use,1e-12) as r
That is, use=0 would effectively be replaced by a minuscule use=10**-50 in the calculation, so that the division doesn't fail. It is still assumed that negative values of use do not occur.