topic Re: Goal seek in sas in SAS Programming

Goal seek in sas

npr — Tue, 17 Dec 2019 10:07:42 GMT

I have a variable

Y = I1 + b1E + b2M + b3V , where

E =I2 + m2 * x1, where

x1 = I3 + m3 * x2 + m4 * x3 + m5 * x4

I want to find the value of x3 , x4 and x5 where the value of Y becomes 1 and Y becomes 0.

Is there a direct method in SAS to achevie this .. this is very similar to goal seek operation.

Any help would be appreciated

Re: Goal seek in sas

Kurt_Bremser — Tue, 17 Dec 2019 10:15:54 GMT

... where the value of Y becomes 1 and Y becomes 0.

??? Y can't be one and zero at the same time.

If you need to find a function root, look at https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3099-2019.pdf

Re: Goal seek in sas

npr — Tue, 17 Dec 2019 10:38:34 GMT

Hey Kurt

I was referring to two instances

1) where value of y is 0

2) where value of y is 1

Re: Goal seek in sas

Kurt_Bremser — Tue, 17 Dec 2019 12:17:17 GMT

You should be able to adapt the algorithm from the paper to both target values. If in doubt, ask @Ksharp 😉

Re: Goal seek in sas

Ksharp — Tue, 17 Dec 2019 12:30:11 GMT

Your equation has too many parameter.

Do you have some data for I1 I2 b1 b2 ......... like ?

I1 I2 b1 b2 ..

0.1 9 11 0.2

0.4 1 3 0.4

........

if you have , PROC NLIN could help you out.

And calling @Rick_SAS who wrote a couple of blogs about this topic .

https://communities.sas.com/t5/Statistical-Procedures/PROCs-for-multiplicative-model/m-p/611435#M29594

Re: Goal seek in sas

Rick_SAS — Tue, 17 Dec 2019 13:29:48 GMT

We need more information.

There is no x5 in your equations.
I assume x2-x4 are the variables. Do all the other letters represent known constants, such as I1, I2, I3, b1, b2, b3, m2, m3, m4, and m5? What about M and V?

In general, this is a root-finding problem. You have one equation Y = L(x2, x3, x4) where L is a linear function. For one equation and three unknowns, there are infinitely many solutions. Geometrically, the solution is a 2-D plane in (x2, x3, x4)-space.

If you clarify the problem and provide values for the constants, we can show you how to compute the solution in SAS.

Re: Goal seek in sas

npr — Tue, 17 Dec 2019 14:30:54 GMT

1. There is no X5 variable

2. These are for various customers , so M and V are different for each customers. I1, I2, I3, b1, b2, b3, m2, m3, m4, and m5 remain constant as these are coefficients from regression.

Cust	Y	E	M	V
1	0.76364	0.4234	0.24325	0.1636	I1	0.1231
2	0.3451	0.423421	0.5675	0.1674	I2	0.4564
3	0.84764	0.6456	0.4756	0.2457	I3	0.8675
4	0.46823	0.53466	0.52345	0.254564	b1	1
5	0.345783	0.52345	0.6745	0.742	b2	1
6	0.783478	0.3523	0.3452	0.876	b3	1
7	0.78246	0.54536	0.52345	0.2453	m2	0.1341
8	0.827612	0.352	0.7452	0.65434	m3	0.6456
9	0.89459	0.35234	0.66745	0.2341
10	0.094897	0.23452	0.245	0.7547
11	0.92342	0.2345	0.647	0.24523

I have pasted the above dummy data , it ll be helpful if u can show me what values of x2 ,x3 and x4 can give me values of y=0 and what values of x2,x3 and x4 can give me values of y=1 , for each customer .

Re: Goal seek in sas

Rick_SAS — Tue, 17 Dec 2019 15:41:20 GMT

You can solve this problem by hand by using algebraic manipulations. I did not double-check my calculations, so please read the comments for an explanation and to verify my algebra. Also, you did not supply m4 or m5, but I indicated where you can plug those numbers in.

data Have;
retain I1	0.1231
       I2	0.4564
       I3	0.8675
       b1	1
       b2	1
       b3	1
       m2	0.1341
       m3	0.6456
       m4 1          /* OP did not supply */
       m5 1;         /* OP did not supply */
input Cust	Y	E	M	V;
datalines;
1	0.76364	0.4234	0.24325	0.1636	 	
2	0.3451	0.423421	0.5675	0.1674	 	
3	0.84764	0.6456	0.4756	0.2457	 	
4	0.46823	0.53466	0.52345	0.254564	 	
5	0.345783	0.52345	0.6745	0.742	 	
6	0.783478	0.3523	0.3452	0.876	 	
7	0.78246	0.54536	0.52345	0.2453	 	
8	0.827612	0.352	0.7452	0.65434	 	
9	0.89459	0.35234	0.66745	0.2341	 	 	 
10	0.094897	0.23452	0.245	0.7547	 	 	 
11	0.92342	0.2345	0.647	0.24523	 
;


proc iml;
/*
x1 = I3 + m3 * x2 + m4 * x3  + m5 * x4
E = I2 + m2 * x1
Y = I1 + b1*E + b2*M + b3*V 

so 
Y = I1 + b2*M + b3*V + b1*(I2 + m2 *(I3 + m3 * x2 + m4 * x3  + m5 * x4) )
  = I1 + b2*M + b3*V + b1*I2 + b1*m2*I3 + b1*m2*m3 *x2 + b1*m2*m4*x3 + b1*m2*m5*x4

Define 
c1 = I1 + b2*M + b3*V + b1*I2 + b1*m2*I3
c2 = b1*m2*m3
c3 = b1*m2*m4
c4 = b1*m2*m5

Then Y = c1 + c2*x2 + c3*x3 and c4*x4
or 
x2 = (Y - c1 - c3*x3 - c4*x4) / c2

Because the system is overdetermined, you can choose ANY VALUES for x3 and x4
For example, if you choose x3=1 and x4=1 then the two equations are
x2 = (0 - c1 - c3*x3 - c4*x4) / c2
and
x2 = (1 - c1 - c3*x3 - c4*x4) / c2
*/

data Want;
set Have;
c1 = I1 + b2*M + b3*V + b1*I2 + b1*m2*I3;
c2 = b1*m2*m3;
c3 = b1*m2*m4;
c4 = b1*m2*m5;

/* make a choice for x3 and x4 */
x3 = 1; x4 = 1;

do Ytarget = 0, 1;
   x2 = (YTarget - c1 - c3*x3 - c4*x4) / c2;
   output;
end;
run;

proc print data=Want;
   var cust yTarget x2 x3 x4;
run;