Proc optmodel "by row" (Excel solver)

[rj_missionbeach]

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.

[PaigeMiller]

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.

[rj_missionbeach]

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.

[ballardw]

---

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

[rj_missionbeach]

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. 

[RobPratt · Accepted solution]

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

[rj_missionbeach]

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

[RobPratt]

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

[rj_missionbeach]

Thank you Rob!