Operations Research topics: SAS/OR,
SAS Optimization, and SAS Simulation Studio

Outputs of Excel Solver and PROC OPTMODEL

Posts: 25

Outputs of Excel Solver and PROC OPTMODEL



From the documentation of PROC OPTMODEL, I am trying to replicate the Answer Report and Sensitivity Report section of Excel Solver. I have picked up the example from http://people.brunel.ac.uk/~mastjjb/jeb/or/lpsens_solver.html and trying to replicate all the outputs.


From Answer Report section of Excel Solver output, I am not able to get the Status and Slack. This is not critical as I can derive it easily. But my expectation from SAS is usually high.


The problem is with the Sensitivity Report section of Excel Solver output. I am able to get Reduced Cost (.RC) and Shadow Price (.DUAL). However I am not able to get Allowable Increase and Allowable Decrease of Variables and Constraints.


Can you please let me know how to get Allowable Increase and Allowable Decrease in particular and replicate Excel Solver output in general using PROC OPTMODEL?


The SAS code that i have done to replicate the LP problem in the link is as follows.

proc optmodel;
ods output PrintTable=arg1 /*ProblemSummary=arg2noneed*/ /*DerivMethods=arg3absent*/
/*SolverOptions=arg4absent*/ /*SolutionSummary=arg5noneed*/ /*OptStatistics=arg6absent*/;
/* declare variables */
var x1>=0,x2>=0,x3>=0,x4>=0;
/* subject to constraints */
con assembly : 2*x1 + 4*x2 + 3*x3 + 7*x4 <= 100000;
con polish : 3*x1 + 2*x2 + 3*x3 + 4*x4 <= 50000;
con pack : 2*x1 + 3*x2 + 2*x3 + 5*x4 <= 60000;
/* maximize profit */
max profit = 1.5*x1 + 2.5*x2 + 3.0*x3 + 4.5*x4;
/* solve LP */
/* display solution */
print profit;
print x1.sol x2.sol x3.sol x4.sol;
print x1.rc x2.rc x3.rc x4.rc; /* reduced cost */
print assembly.body polish.body pack.body; /* cons val */
print assembly.dual polish.dual pack.dual; /* shadow price */


Thanks In Advance!


SAS Employee
Posts: 571

Re: Outputs of Excel Solver and PROC OPTMODEL

Posted in reply to arindam1984

You can get the basis information from the .STATUS suffix:



Note also that you can use the problem symbols _VAR_ and_CON_ to shorten the code:

print _VAR_.name _VAR_.sol  _VAR_.status _VAR_.rc;
print _CON_.name _CON_.body _CON_.status _CON_.dual;


For nonbasic variables, you can read off the allowable increase or decrease from the .RC suffix.


The other parts depend on the optimal tableau, which is not returned to the user.  For your small instance, you can compute B inverse directly and use the techniques discussed in linear programming textbooks.  For example, see Section 6.3 (Sensitivity Analysis) in Winston's Operations Research.

Posts: 25

Re: Outputs of Excel Solver and PROC OPTMODEL

Well it is unfortunate that SAS does not return the optimal tableau to give me the allowable increase and decrease. The example I gave was just to co-relate with the available excel solver output. My actual problem is very different but is still a LP.

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation