topic Re: Proc optmodel "by row" (Excel solver) in Mathematical Optimization, Discrete-Event Simulation, and OR

Proc optmodel "by row" (Excel solver)

rj_missionbeach — Thu, 16 Mar 2023 19:54:25 GMT

Hi all -

I am trying to get an iterative solution for each observation. The examples I've been through appear to optimize across a dataset (I've never used proc optmodel so I'm pretty ignorant here). My data looks like:

data a1;
input int amt_removed i1 i2 i3 b c;
datalines;
0.024860826 5 0.002456519 0.004913037 0.008185046 0.006051554 0.00734217
0.031408863 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217
0.020765289 3 0.002456519 0.004913037 0.004911028 0.006051554 0.00734217
0.010037152 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217

;

And I want to find the solution (X) for each observation of this:

min error = abs(X*i1-i2-i3+b+c-int);

or X=(int+i2+i3-b-c)/(i1)

Thus, the solutions for the four rows are, respectively, X=10, 10, 7, and 1.3

I realize, of course, I can/do solve this analytically in this simple example, but my actual data makes this more complicated and requires an iterative solution. Seems like this should be easy, but I can't seem to figure it out. Any help would be greatly appreciated.

Re: Proc optmodel "by row" (Excel solver)

PaigeMiller — Thu, 16 Mar 2023 20:07:05 GMT

So what would PROC OPTMODEL code look like for row 1?

Perhaps this example using DOSUBL would apply here, where the author is running some other PROC on each row.

Re: Proc optmodel "by row" (Excel solver)

ballardw — Thu, 16 Mar 2023 20:12:20 GMT

@rj_missionbeach wrote:

Hi all -

I am trying to get an iterative solution for each observation. The examples I've been through appear to optimize across a dataset (I've never used proc optmodel so I'm pretty ignorant here). My data looks like:

data a1;
input int amt_removed i1 i2 i3 b c;
datalines;
0.024860826 5 0.002456519 0.004913037 0.008185046 0.006051554 0.00734217
0.031408863 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217
0.020765289 3 0.002456519 0.004913037 0.004911028 0.006051554 0.00734217
0.010037152 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217

;

And I want to find the solution (X) for each observation of this:

min error = abs(X*i1-i2-i3+b+c-int);

or X=(int+i2+i3-b-c)/(i1)

Thus, the solutions for the four rows are, respectively, X=10, 10, 7, and 1.3

I realize, of course, I can/do solve this analytically in this simple example, but my actual data makes this more complicated and requires an iterative solution. Seems like this should be easy, but I can't seem to figure it out. Any help would be greatly appreciated.

See if this is what you expect:

data a1;
input int amt_removed i1 i2 i3 b c;
X=(int+i2+i3-b-c)/(i1);
datalines;
0.024860826 5 0.002456519 0.004913037 0.008185046 0.006051554 0.00734217
0.031408863 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217
0.020765289 3 0.002456519 0.004913037 0.004911028 0.006051554 0.00734217
0.010037152 1 0.002456519 0.004913037 0.001637009 0.006051554 0.00734217
;

The X you provided appear to be rounded results. I get x=9.9999979646 for the first and second observations. So perhaps you would round the value to 1 or 2 decimal places which would be

round(x,0.1) for 1 decimal place or round(x,0.01) for two decimal places.

Re: Proc optmodel "by row" (Excel solver)

rj_missionbeach — Thu, 16 Mar 2023 20:19:17 GMT

Thank you ballardw - I agree that is the analytical solution. However, my real data are more complicated and cannot be solved algebraically. I can solve using excel solver, and am trying to figure out if there is an analogous SAS solution.

Re: Proc optmodel "by row" (Excel solver)

rj_missionbeach — Thu, 16 Mar 2023 20:25:02 GMT

Thank you Paige -

I'm new to optmodel so I'm not sure. I simply want sas to, iteratively, solve for X (for each row of data) by finding the X that makes this (approximately) zero: abs(X*i1-i2-i3+b+c-int). I'm not sure optmodel is the solution - I simply googled SAS and excel solver which kept coming up with optmodel.

Re: Proc optmodel "by row" (Excel solver)

RobPratt — Thu, 16 Mar 2023 20:30:18 GMT

Here's one way to do it, with a DO loop that calls the solver once for each observation:

proc optmodel;
   set OBS;
   num int {OBS};
   num amt_removed {OBS};
   num i1 {OBS};
   num i2 {OBS};
   num i3 {OBS};
   num b {OBS};
   num c {OBS};
   read data a1 into OBS=[_N_] int amt_removed i1 i2 i3 b c;

   var X;
   num obsThis;
   min error = abs(X*i1[obsThis]-i2[obsThis]-i3[obsThis]+b[obsThis]+c[obsThis]-int[obsThis]);
   num Xsol {OBS};

   do obsThis = OBS;
      put obsThis=;
      solve linearize;
      Xsol[obsThis] = X.sol;
   end;

   create data want from [i] int amt_removed i1 i2 i3 b c X=Xsol;
quit;

Notice that the LINEARIZE option automatically linearizes the absolute value and calls the linear programming (LP) solver.

Other approaches include using a FOR loop or COFOR loop. In SAS Optimization in SAS Viya, you can also use the groupBy= parameter in the runOptmodel action to do BY-group processing without writing any explicit loops.

See this SAS Usage Note for an example that involves multiple observations per group:

https://support.sas.com/kb/42/332.html

Re: Proc optmodel "by row" (Excel solver)

rj_missionbeach — Thu, 16 Mar 2023 20:41:46 GMT

Thank you Rob -

That seems to work if I comment out the linearize. With linearize in there, I get an error message.

08 do obsThis = OBS;
209 put obsThis=;
210 solve linearize;
---------
22
76
ERROR 22-322: Syntax error, expecting one of the following: /, OBJECTIVE, WITH.

ERROR 76-322: Syntax error, statement will be ignored.

211 Xsol[obsThis] = X.sol;
212 end;
213

Re: Proc optmodel "by row" (Excel solver)

RobPratt — Thu, 16 Mar 2023 21:02:08 GMT

Automated linearization via the LINEARIZE option was introduced in SAS Optimization in the 2020.1.1 release of SAS Viya.

Re: Proc optmodel "by row" (Excel solver)

rj_missionbeach — Thu, 16 Mar 2023 21:09:38 GMT

Thank you Rob!