BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
rj_missionbeach
Fluorite | Level 6

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

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

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
rj_missionbeach
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6

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
SAS Super FREQ

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
Fluorite | Level 6

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
SAS Super FREQ

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

rj_missionbeach
Fluorite | Level 6

Thank you Rob!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 788 views
  • 0 likes
  • 4 in conversation