Minimize a value by changing values in equation

[david27]

Hello Everyone,

Can someone please guide me how to proceed with the below issue.

I want to minimize a value by changing coefficients of an equation.

The coefficients of equation are first assigned a random number but should change later after optimization.

Changing these coefficients should result in minimizing -- minimize_error

I understand the values are already assigned as macro variables--w1-w8 & b1-b3 but I am fine with corresponding new macro variables.

Using solver in excel is just a few clicks. Want to explore equivalent of it in SAS.

Please advise.

Thank You

%macro weight(neuron=,hidden_layer=2);

	%let nos_weight=%sysevalf(((&neuron./2)**2)+(&neuron.));
	%put &=nos_weight;

     data rand_data;
           call streaminit(0);
           do i = 1 to &nos_weight.;
                u=rand("Uniform");
                output;
           end;
     run;

     %do i = 1 %to &nos_weight.;
		 %global w&i.;
     %end;

     proc sql noprint;
           select u into :w1-:w&nos_weight.
                from rand_data
     ;quit;

     %do i = 1 %to &nos_weight.;
	 %global w&i.;
           %put w&i.=&&w&i.;
     %end;

     %let bias=%eval(&hidden_layer.+1);

     data rand_data;
           call streaminit(0);
           do i = 1 to &bias.;
                u=rand("Uniform");
                output;
           end;
     run;

     %do i = 1 %to &bias.;
		 %global b&i.;
     %end;


     proc sql noprint;
           select u into :b1-:b&bias.
                from rand_data
     ;quit;
     %do i = 1 %to &bias.;
           %put b&i.=&&b&i.;
     %end;

%mend weight;

%weight(neuron=4,hidden_layer=2);


data equation;
input y m x c;
datalines;
14 9 1 5
851 9 94 5
473 9 52 5
635 9 70 5
518 9 57 5
257 9 28 5
383 9 42 5
428 9 47 5
356 9 39 5
68 9 7 5
257 9 28 5
248 9 27 5
194 9 21 5
86 9 9 5
761 9 84 5
284 9 31 5
653 9 72 5
356 9 39 5
320 9 35 5
;run;

data equation2;
	set equation;
	n1h1=x*&w1.-&b1.;
	n2h1=x*&w2.-&b1.;
	n1h2=(n1h1*&w3.)+(n2h1*&w4.);
	n2h2=(n1h1*&w5.)+(n2h1*&w6.);
	output_network=(n1h2*&w7.)+(n2h2*&w8.);
	sq_diff=(y-output_network)**2;
run;

/*I want to minimize value of minimize_error by changing values w1-w8 and b1-b3*/
proc sql;
	select sum(sq_diff) format=best19.5
			into: minimize_error
		from equation2
;quit;
%put &=minimize_error;

[PaigeMiller]

SAS has a number of optimization routines. In most cases, you shouldn't have to write your own.

If you use your favorite internet search engine for "excel solver in SAS", you get a bunch of solutions.

[david27]

An example that actually solves the problem stated would be more useful I guess.

Thank You anyways for the suggestion.

Regards,

[Ksharp]

You could try PROC NLIN 

or If you have SAS/IML  @Rick_SAS  wrote many blog about this kind of optimial problem.

[Rick_SAS]

Do you have a license for SAS/OR or SAS/IML? Both offer built-in optimization routines.

[david27]

Thank You everyone for chiming in here.

I read a little and came up with below example using OPTMODEL.

Why am I getting the value of w1 as 0?

As you see the dataset y=2*x so i am expecting w1 to be close to 2.

What am I missing here?

Please adivse.

data equation;
	input y x;
	datalines;
10 5
20 10
30 15
40 20
50 25
60 30
70 35
80 40
;
run;
proc optmodel;
	var w1;
	number x ,y;
	read data equation into x y;
	min sq_diff=w1*x-y;
	con y=2*x;
	solve;
	print w1;
	expand / iis;
quit;

[mkeintz]

You are choosing W to minimizing the expression

     W*x-y

where x and y are fixed values, always positive, and with constraint  y=2x.

Assuming that proc optmodel is restricting its choices of W as a non-negative number, zero HAS to be the solution.  Given that x and y are always positive, and the constraint y=2x, the w=0 will always resolve the expression W*x-y to -y.

But you are naming your objective  sqdiff, so I suspect you actually want

    min sq_diff=(w*x-y)**2 ;

I did this, and got w=2.

[david27]

Thank You @mkeintz 

You said-- "Assuming that proc optmodel is restricting its choices of W as a non-negative number..."

Is there a way we can validate this?

And yes squaring the difference did get me value of 2.

Thank You

[mkeintz]

---

@david27 wrote:

Thank You @mkeintz 

You said-- "Assuming that proc optmodel is restricting its choices of W as a non-negative number..."

Is there a way we can validate this?

Thank You

---

Yes.   "We" can look at the proc optmodel documentation.

editted note:  And if W were not restricted to non-negatives, then the more negative W is,the more minimization of     wx-y.  It would be an unbounded solution.

[david27]

It goes negative.

I just validated it.

data equation;
	input y x;
	datalines;
10 -5
-20 10
-30 15
40 -20
-50 25
-60 30
70 -35
80 40
;
run;
proc optmodel;
	var w1;
	number x ,y;
	read data equation into x y;
	min sq_diff =(w1*x-y)**2;
	con y=abs(2*x);
	solve;
	print w1;
	expand / iis;
quit;

[david27]

Hello,

So I have this below example.

In the verification_dsn I have populated the values for all weights==w1-w8 & b1-b3.

verification_dsn is the dataset i use to just validate the formulas.

I use these weights to populate minimize_function and constraints.

The values match or are very close.

I use the same formula in the OPTMODEL procedure but then get all weights(w1-w8) as 0.

Is there anything that I am missing?

I got the values for w1-w8 and b1-b3 using solver in excel. But not able to get similar values in SAS.

Please advise.

Thank You.

data equation;
input y m x c;
datalines;
14 9 1 5
851 9 94 5
473 9 52 5
635 9 70 5
518 9 57 5
257 9 28 5
383 9 42 5
428 9 47 5
356 9 39 5
68 9 7 5
257 9 28 5
248 9 27 5
194 9 21 5
86 9 9 5
761 9 84 5
284 9 31 5
653 9 72 5
356 9 39 5
320 9 35 5
;run;
%let w1=0.094961;
%let w2=0.008734;
%let w3=6.21478;
%let w4=7.002409;
%let w5=0.012339;
%let w6=0.396723;
%let w7=13.94726;
%let w8=1.045836;

%let b1=0;
%let b2=0;
%let b3=0;
data verification_dsn;
	set equation;
w1=0.094961;
w2=0.008734;
w3=6.21478;
w4=7.002409;
w5=0.012339;
w6=0.396723;
w7=13.94726;
w8=1.045836;

b1=0;
b2=0;
b3=0;
	n1h1=x*&w1.-&b1.;
	n2h1=x*&w2.-&b1.;
	n1h2=((x*&w1.-&b1.)*&w3.)+((x*&w2.-&b1.)*&w4.)-&b2.;
	n2h2=(n1h1*&w5.)+(n2h1*&w6.)-&b2.;
	output_network=(n1h2*&w7.)+(n2h2*&w8.)-&b3.;
	sq_diff=(y-output_network)**2;
	minimize_function=(y-(((((x*w1-b1)*w3)+((x*w2-b1)*w4)-b2)*w7)+((((x*w1-b1)*w5)+((x*w2-b1)*w6)-b2)*w8)-b3))**2;
	constraint=abs(((((x*w1-b1)*w3)+((x*w2-b1)*w4)-b2)*w7)+((((x*w1-b1)*w5)+((x*w2-b1)*w6)-b2)*w8)-b3);
run;

proc optmodel;
	var w1 ,w2 ,w3 ,w4 ,w5 ,w6 ,w7 ,w8 ,b1 ,b2 ,b3;
	number x ,y;
	read data equation into x y;
	min sq_diff=(y-(((((x*w1-b1)*w3)+((x*w2-b1)*w4)-b2)*w7)+((((x*w1-b1)*w5)+((x*w2-b1)*w6)-b2)*w8)-b3))**2;
	con y = abs(((((x*w1-b1)*w3)+((x*w2-b1)*w4)-b2)*w7)+((((x*w1-b1)*w5)+((x*w2-b1)*w6)-b2)*w8)-b3);
	solve;
	print w1 w2 w3 w4 w5 w6 w7 w8 b1 b2 b3;
	expand / iis;
quit;

[mkeintz]

Well, that was certainly faster than going to the docs, and dismisses my thought the proc optmodel was by default constraining W>=0 - probably a good idea.

But then I don't understand why w1 is not an unbounded estimate.  After all your constraint

    y=2x

means x=.5y

Then isn't the initial (unintended) objective function   min   w1x-y

   equivalent to minimizing    w1*.5y-y    which in turn is  

   equivalent to minimizing (.5*w1-1)*y

If w is unbounded, then why is the solution w=0?  Any negative value of w would produce a smaller objective function. In fact, the more negative w1 is, the more "minimal" the objective.  There is something about the syntax of proc optmodel that is not apparent to me in your simplified problem.

[Ksharp]

If you would like to use SAS/OR , better post it at OR forum 

and calling @RobPratt