BookmarkSubscribeRSS Feed
FreelanceReinh
Jade | Level 19

@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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 2559 views
  • 6 likes
  • 5 in conversation