topic Re: Find factor that reduce amount by defined amount in SAS Programming

Find factor that reduce amount by defined amount

Ronein — Tue, 29 Oct 2019 11:48:16 GMT

Hello

I would like to ask a question please.

I have a data set with 3 fields: ID,Mis,Use

I need to calculate a new field called Mis_New by following metric:

Mis_New= min(Use*Factor,Mis);

The question is how to find the factor that will reduce SUM of MIS by 200 .

For example:

If I use Factor=1.2 then I will reduce SUM of MIS from 5780 to 5330 (difference is 450...but required difference is 200)

Any idea?

Thank you


Data tttbl1;
input ID  Mis  Use;
cards;
1 100 90
2 150 130
3 800 300
4 250 200
5 1100 1000
6 950 900
7 890 800
8 250 240
9 590 530
10 700 600
;
Run;


Data tttbl2;
SET tttbl1;
Mis_New= min((Use*1.2),Mis);
Run;



PROC SQL;
	create table summary1  as
	select sum(Mis) as Sum_Mis ,
		   sum(Mis_New) as Sum_Mis_New,
		   calculated Sum_Mis-calculated Sum_Mis_New as dif
	from tttbl2 
;
QUIT;

Re: Find factor that reduce amount by defined amount

KachiM — Tue, 29 Oct 2019 11:14:43 GMT

@Ronein

I think you are looking for something like this:

data want;
   if _n_ = 1 then do until(eof);
      set tttbl1 end = eof;
      sum + Mis;
   end;
   fac = (sum - 200) / sum;
   do until(last);
      set tttbl1 end = last;
      new = min(Use, ceil(Mis * fac));
      output;
   end;
drop fac sum;
run;

Re: Find factor that reduce amount by defined amount

Ronein — Tue, 29 Oct 2019 11:22:24 GMT

Thank you

I run your code but I don't find the answer.....what is the factor value that reduce by 200?

Re: Find factor that reduce amount by defined amount

Ronein — Tue, 29 Oct 2019 11:42:40 GMT

Is there a more clever way to do it?

I found that factor=2 is the solution.

Is there a better way to run the simulation?


Data tttbl1;
input ID  Mis  Use;
cards;
1 100 90
2 150 130
3 800 300
4 250 200
5 1100 1000
6 950 900
7 890 800
8 250 240
9 590 530
10 700 600
;
Run;


 %macro mmacro(factor,run);
Data tttbl2_&run.;
SET tttbl1;
Mis_New= min((Use*&factor.),Mis);
Run;


PROC SQL;
	create table SummaryData&run.  as
	select &factor. as factor,
	       &run. as run,
           sum(Mis) as Sum_Mis ,
		   sum(Mis_New) as Sum_Mis_New,
		   calculated Sum_Mis-calculated Sum_Mis_New as Reduced_amount
	from tttbl2_&run. 
;
QUIT;
%mend;

%mmacro(1.05,1);
%mmacro(1.1,2);
%mmacro(1.15,3);
%mmacro(1.2,4);
%mmacro(1.25,5);
%mmacro(1.3,6);
%mmacro(1.4,7);
%mmacro(1.45,8);
%mmacro(1.5,9);
%mmacro(1.55,10);
%mmacro(1.6,11);
%mmacro(1.65,12);
%mmacro(1.7,13);
%mmacro(1.75,14);
%mmacro(1.8,15);
%mmacro(1.85,16);
%mmacro(1.9,17);
%mmacro(1.95,18);
%mmacro(2.0,19);
%mmacro(2.05,20);
%mmacro(2.1,21);



Data Simulations;
Set SummaryData:;
By factor;
Run;

Re: Find factor that reduce amount by defined amount

gamotte — Tue, 29 Oct 2019 15:37:09 GMT

Hello,

Edit Removed my second answer as i am not sure it really made sense.

Something like this ?

%macro mmacro(factor,run);

    data tttbl2_&run.;
        set tttbl1;
        Mis_New= min((Use*&factor.),Mis);
        sum_Mis+Mis;
        sum_Mis_New+Mis_New;
        Reduced_amount=sum_Mis-sum_Mis_New;
        call symputx("Reduced_amount",Reduced_amount,"G");
    run;

%mend;

%let MAXRUN=100;
%let TARGET=200;
%let PREC=1.0e-06;

Data tttbl1;
input ID  Mis  Use;
cards;
1 100 90
2 150 130
3 800 300
4 250 200
5 1100 1000
6 950 900
7 890 800
8 250 240
9 590 530
10 700 600
;
Run;

data history;
    stop=0;
    
    minfactor=1; maxfactor=10;

    do run=1 by 1 while(not stop and run<&MAXRUN.);

        if run=&MAXRUN. then put "WARNING: maximum number of runs reached";
        
        factor=(minfactor+maxfactor)/2;

        rc=dosubl(cats('%mmacro(',factor,',',run,');'));

        Reduced_amount=symget("Reduced_amount");

        put Reduced_amount=;

        if abs(Reduced_amount-&TARGET.)<&PREC. then stop=1;
        else if Reduced_amount<&TARGET. then do;
            action="The reduced amount is too small => we decrease the factor";
            maxfactor_old=maxfactor;
            maxfactor=factor;
        end;
        else do;
            action="The reduced amount is too big => we increase the factor";
            minfactor_old=minfactor;
            minfactor=factor;
        end;

        output;
    end;
run;

Re: Find factor that reduce amount by defined amount

KachiM — Tue, 29 Oct 2019 14:43:47 GMT

@Ronein

I missed your specification.

Re: Find factor that reduce amount by defined amount

Reeza — Tue, 29 Oct 2019 15:31:22 GMT

Is this reduce by 200 related to a specific value of Use or in general or given the value of Use?

The formula doesn't appear complicated and I would assume that there may be a mathematical solution using some basic algebra, although simulations will work.

Re: Find factor that reduce amount by defined amount

FreelanceReinh — Tue, 29 Oct 2019 15:47:35 GMT

Hello @Ronein,

Now that you've got an algorithm approximating the factor numerically, let me add an approach aiming at a direct calculation (good point, @Reeza!):

%let threshold=200;

proc sql;
create view vtemp as
select mis, use, round(mis/use,1e-12) as r
from tttbl1
order by r;

create table totals as
select sum(mis) as s, sum(use) as t
from tttbl1;
quit;

data want(keep=factor);
if _n_=1 then set totals;
do until(last.r);
  set vtemp;
  by r;
  if first.r & s-r*t<=&threshold then do;
    factor=(s-&threshold)/t;
    output;
    stop;
  end;
  s+(-mis);
  t+(-use);
end;
run;

Note: Modifications would be necessary if use=0 was a possible value in dataset TTTBL1.

Re: Find factor that reduce amount by defined amount

gamotte — Tue, 29 Oct 2019 16:20:12 GMT

I can only give you one like unfortunately.

Re: Find factor that reduce amount by defined amount

FreelanceReinh — Tue, 29 Oct 2019 16:41:49 GMT

@gamotte wrote:
I can only give you one like unfortunately.

Thanks! 🙂 Honestly, I'm not quite satisfied with my solution. I mentioned "direct calculation," but this was actually an overstatement: In fact, the algorithm steps through the vertices of the graph of a convex, decreasing, piecewise linear function (from left to right) until the graph crosses the threshold. Only then a direct calculation is performed (intersection of the respective line segment with the horizontal threshold line). I haven't tested the program yet on a large dataset where your nice approximation might be more efficient because it doesn't care about the line segments, but just the function values. Also, as mentioned, the current version of my program wouldn't work if use=0 for some observation.

Re: Find factor that reduce amount by defined amount

Ronein — Wed, 30 Oct 2019 05:36:00 GMT

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?

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

Re: Find factor that reduce amount by defined amount

Ronein — Wed, 30 Oct 2019 05:15:35 GMT

Sorry,

I don't see where you end the macro that you defined and don't see where you run it.

The metric to calculate the value of new "Mis" field for each customer is

New_Mis=min((Use*Factor),Mis);

Where did you use it in the code please?

Re: Find factor that reduce amount by defined amount

Ronein — Wed, 30 Oct 2019 05:25:04 GMT

The issue is like that.

Customers in bank has :

CREDIT CARD LINE (This is the maximum amount that they are allowed to use).

CREDIT USE (This is the actual amount that customer use in credit card).

As you understand CREDIT USE should be most of times lower than CREDIT CARD LINE.

Let's say that in the bank there are 1 million customers and total CREDIT CARD LINE is 10 billions Dollar.

Now let's say that there is requirement to reduce Total CREDIT CARD LINE amount by 2 billions Dollar.

Let's say that the metric that was agreed is that for each customer in the bank the NEW CREDIT CARD LINE will be calculated by:

NEW CREDIT CARD LINE=min((CREDIT USE*Factor),Current_CREDIT_CARD_LINE);

The target is to calculate the Factor that give us the desired reduce of 2 billions Dollar.

Moreover, better to have a chart that show us for each value of factor what will be the reduced amount

Re: Find factor that reduce amount by defined amount

gamotte — Wed, 30 Oct 2019 08:54:38 GMT

I don't know about efficiency but it surely seems clever to me the way you recompute the reduced amount

variable at each step without having to re-read the whole dataset.

Re: Find factor that reduce amount by defined amount

gamotte — Wed, 30 Oct 2019 09:02:30 GMT

@Ronein,

The macro only contains one data step that compute the reduced amount for a given factor :

%macro mmacro(factor,run);

    data tttbl2_&run.;
        set tttbl1;
        Mis_New= min((Use*&factor.),Mis);
        sum_Mis+Mis;
        sum_Mis_New+Mis_New;
        Reduced_amount=sum_Mis-sum_Mis_New;
        call symputx("Reduced_amount",Reduced_amount,"G");
    run;

%mend;

You can see that it contains your formula

Mis_New= min((Use*&factor.),Mis);

This macro is then used in the data history step, inside the do loop, for each tried value of the factor :

rc=dosubl(cats('%mmacro(',factor,',',run,');'));

dosubl is used since the reduced amount is computed in a separate data step.

Re: Find factor that reduce amount by defined amount

FreelanceReinh — Wed, 30 Oct 2019 10:35:06 GMT

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