topic Re: Replicate excel output of solver using Proc Optmodel. in Mathematical Optimization, Discrete-Event Simulation, and OR

Replicate excel output of solver using Proc Optmodel.

Swapneel — Fri, 17 Aug 2018 13:35:45 GMT

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

Re: Replicate excel output of solver using Proc Optmodel.

RobPratt — Fri, 17 Aug 2018 20:54:32 GMT

You have a few syntax errors:

Remove the two blank lines in the DATA step.
Change INIT to INTEGER in the VAR statement.
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:

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?

Re: Replicate excel output of solver using Proc Optmodel.

Swapneel — Tue, 21 Aug 2018 13:56:50 GMT

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

Re: Replicate excel output of solver using Proc Optmodel.

Swapneel — Tue, 21 Aug 2018 14:00:27 GMT

Also, getting error "

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

Thanks

Re: Replicate excel output of solver using Proc Optmodel.

RobPratt — Tue, 21 Aug 2018 15:02:45 GMT

Please share the new data and code.

Re: Replicate excel output of solver using Proc Optmodel.

RobPratt — Tue, 21 Aug 2018 15:05:04 GMT

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.

Re: Replicate excel output of solver using Proc Optmodel.

Swapneel — Wed, 22 Aug 2018 09:26:39 GMT

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

Re: Replicate excel output of solver using Proc Optmodel.

RobPratt — Wed, 22 Aug 2018 12:15:30 GMT

Please share the new code and data.

Re: Replicate excel output of solver using Proc Optmodel.

Swapneel — Fri, 24 Aug 2018 14:57:13 GMT

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

Re: Replicate excel output of solver using Proc Optmodel.

RobPratt — Fri, 24 Aug 2018 15:02:30 GMT

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.