🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

## Replicate excel output of solver using Proc Optmodel.

Hi,

I am trying to solve this using proc optmodel. But, not able to do. Can anyone help me in this regards. It works when c2 value is changed to 0.5. But, doesn't work with 0.13. P1 and P2 are probabilities. F1 and F2 are frequency. They should be integer. c1 and C2 are cost per f1 and f2 respectively.  f1*c1+f2*c2 should be less than cost.

objective function is maximise p1[I]*f1[I]*c1[I]+p2[I]*f2[I]*c2[I]

data temp;
infile DATALINES dsd missover;
input id cr1 cr2 P1 P2 F1 F2 C1 C2 cost;
CARDS;
1,0.7,0.2,0.7,0.3,2,0,2,0.13,10
2,0.7,0.2,0.894965079197824,0.105034920802176,5,0,2,0.13,15
3,0.7,0.2,0.94779739712309,0.0522026028769104,2,0,2,0.13,20
4,0.7,0.2,0.993687038743847,0.00631296125615344,1,1,2,0.13,25
5,0.7,0.2,0.594148011375916,0.405851988624084,1,1,2,0.13,10
6,0.7,0.2,0.419275472526551,0.580724527473449,1,0,2,0.13,15
7,0.7,0.2,0.540976095560129,0.459023904439871,2,1,2,0.13,20
8,0.7,0.2,0.321767691744239,0.678232308255761,2,1,2,0.13,25
9,0.7,0.2,0.186053772182906,0.813946227817094,0,2,2,0.13,10
10,0.7,0.2,0.515581052099613,0.484418947900387,1,2,2,0.13,15

;
run;

proc optmodel;
set<num> indx;
number  cr1{indx}, cr2{indx} , p1{indx} , p2{indx} , c1{indx} , c2{indx} , cost{indx} ;
var F1{indx}  init  ,
F2{indx}  init  ,
Z1 {indx} ,
Z2 {indx} ,
y1 {indx},
y2 {indx},
X1 {indx} integer,
X2 {indx} integer

;

read data  temp  into  indx=[_N_]
cr1 = cr1 cr2 = cr2 p1 = p1 p2 = p2 c1=c1
c2=c2 cost = cost ;
print  cr1 cr2 P1 P2 F1 F2 C1 C2 cost;

number n = &_Nobs.;  /* size of matrix */
con b1 {i in 1..10}: y1[i]= (P1[i] *F1[i]* c1[i]);
con b2 {i in 1..10}: Y2[i]=  (P2[i] *F2[i]* c2[i]);
con a1{i in 1..10}: Z1[i]= (F1[i] * C1[i]);
con a2{i in 1..10}: Z2[i]= (F2[i] * C2[i]);
con a3{i in 1..10}: x1[i]= (F1[i]);
con a4{i in 1..10}: x2[i]= (F2[i]);

con Eq1 {i in 1..10}: X1[i] + X2[i] >= 2 ;

con Eq3 {i in 1..10}: X1[i] <= 100 ;
con Eq4 {i in 1..10}: X2[i] <= 100 ;
*con Eq5 {i in 1..10}: (Z1[i]*c1[i] + Z2[i]*c2[i]) <= cost[i] ;
con Eq6 {i in 1..10}:  (Z1[i]+ Z2[i]) = cost[i] ;
min Total_cost = sum{i in 1..n}
(y1[i]  + y2[i]) ;
solve ;
print  F1  F2   ;

quit ;

Thanks

Swapneel

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

I suspect you have introduced a < or > instead of <= or >=.  The CLP solver allows such strict inequalities, but only if all variables are declared to be integer/binary.  The MILP solver does not allow strict inequalities.

9 REPLIES 9
SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

You have a few syntax errors:

1. Remove the two blank lines in the DATA step.
2. Change INIT to INTEGER in the VAR statement.
3. You have not supplied a value for Nobs, but you don't need it if you change 1..n to indx in the MIN statement.

After I corrected those, with c2 = 0.5 I got the following:

NOTE: Problem generation will use 4 threads.
NOTE: The problem has 80 variables (80 free, 0 fixed).
NOTE: The problem has 0 binary and 40 integer variables.
NOTE: The problem has 100 linear constraints (20 LE, 70 EQ, 10 GE, 0 range).
NOTE: The problem has 180 linear constraint coefficients.
NOTE: The problem has 0 nonlinear constraints (0 LE, 0 EQ, 0 GE, 0 range).
NOTE: The OPTMODEL presolver is disabled for linear problems.
NOTE: The initial MILP heuristics are applied.
NOTE: The MILP presolver value AUTOMATIC is applied.
NOTE: The MILP presolver removed all variables and constraints.
NOTE: Optimal.
NOTE: Objective = -66.23120545.

And with c2 = 0.13 I got:

NOTE: Problem generation will use 4 threads.
NOTE: The problem has 80 variables (80 free, 0 fixed).
NOTE: The problem has 0 binary and 40 integer variables.
NOTE: The problem has 100 linear constraints (20 LE, 70 EQ, 10 GE, 0 range).
NOTE: The problem has 180 linear constraint coefficients.
NOTE: The problem has 0 nonlinear constraints (0 LE, 0 EQ, 0 GE, 0 range).
NOTE: The OPTMODEL presolver is disabled for linear problems.
NOTE: The initial MILP heuristics are applied.
NOTE: The MILP presolver value AUTOMATIC is applied.
NOTE: The MILP presolver removed all variables and constraints.
NOTE: Optimal.
NOTE: Objective = 88.133995317.

By the way, I noticed that you did not use cr1 and cr2 anywhere.

And did you maybe forget to put lower bounds of 0 on F1 and F2?

Calcite | Level 5

## Re: Replicate excel output of solver using Proc Optmodel.

Hi Rob, Thanks for reply. It helped for this data. When scaled with multiple variables and 10,000 records. It gave out of memory error. Also, for some cases it was saying MILP does not solve inequality constraint. Could you please advice what to do. Thanks and Regards Swapneel
Calcite | Level 5

## Re: Replicate excel output of solver using Proc Optmodel.

Also, getting error "

The problem contains strict inequality or predicate constraints that reference non-integer variables"

Thanks

SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

I suspect you have introduced a < or > instead of <= or >=.  The CLP solver allows such strict inequalities, but only if all variables are declared to be integer/binary.  The MILP solver does not allow strict inequalities.

Calcite | Level 5

## Re: Replicate excel output of solver using Proc Optmodel.

Hi,

This Helps. It ran after we made <= in MILP. We were able to do it for 10,000 rows. But, when tried for 100k rows, it gave error. Any suggestions how do we run it as we need to run it for 2 million rows.

Thanks and Regards

Swapneel Kitcha

SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

Please share the new data and code.

SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

Please share the new code and data.
Calcite | Level 5

## Re: Replicate excel output of solver using Proc Optmodel.

Hi Rob, Thanks a lot for your help. Sincerely appreciate it. Will reach out to you further, in case of any issues. Thanks and regards Swapneel
SAS Super FREQ

## Re: Replicate excel output of solver using Proc Optmodel.

Glad to help.  If you are still running out of memory for larger instances, please share your new data and code.  The original code has several opportunities for efficiency improvements.

Discussion stats
• 9 replies
• 1098 views
• 0 likes
• 2 in conversation