topic using proc optmodel nonlinear constraint and binary variable to achieve the same result solver excel in Mathematical Optimization, Discrete-Event Simulation, and OR

using proc optmodel nonlinear constraint and binary variable to achieve the same result solver excel

LAIO — Mon, 26 Sep 2022 18:26:36 GMT

@RobPratt , can you help please? I cannot achieve the same result as a can in solver in excel. My result is very bad in sas actually. What method i can use to make opmization with binary variables and have nonlinar constraint? Follow the code:

%macro doit(list);

*base agrupadas;
%let n=%sysfunc(countw(&list.));
%do i=1 %to &n.-1;

%do i2=&i.+1 %to &n.;

%let val = %scan(&list.,&i.);
%let val2 = %scan(&list.,&i2.);
proc sql;
create table &val._&val2. as
select
&val. ,
&val2. ,
sum(over) as over format commax15.0,
sum(qtd) as qtd format commax15.0

from import2
group by 1,2
;
quit;

data &val._&val2.;
set &val._&val2.;
rownum=_n_;
run;

*otimização;
proc optmodel;
set sset;
number over {sset};
number qtd {sset};
read data &val._&val2. into sset=[rownum] over qtd;

var x {sset} binary;

max Objective = (sum {rownum in SSET} qtd[rownum]*x[rownum]);
con overmax:
100*(sum {rownum in sset} over[rownum]*x[rownum])/(sum {rownum in sset} qtd[rownum]*x[rownum]) <=&over_target.;

solve with lso;
print x;

create data &val._&val2._opt from [rownum]=SSET x;

quit;

*resultado da otimização;
data &val._&val2._vf;
merge &val._&val2. (in=a) &val._&val2._opt (in=b);
by rownum;
if a and b;
run;

%end;
%end;

*loop;

%let n=%sysfunc(countw(&list.));
%do i=1 %to &n.-1;

%do i2=&i.+1 %to &n.;

%let val = %scan(&list.,&i.);
%let val2 = %scan(&list.,&i2.);

*printa;

%end;
%end;

%mend;

%doit(&variaveis.);

Re: using proc opmodel nonlinar constrain and binary variable to achieve the same result solver exce

RobPratt — Mon, 26 Sep 2022 18:21:54 GMT

You have not supplied everything to be able to run your code, but the NLP solver does not support integer variables. (Edit: I see now that you have changed your code to use LSO instead of NLP. The LSO solver, now called the black-box solver, supports integer variables but does not guarantee optimality. Better to use MILP here.)

Fortunately, you can easily linearize the nonlinear constraint as follows:

con overmax:
   100 * sum {rownum in sset} over[rownum]*x[rownum] <= &over_target. * sum {rownum in sset} qtd[rownum]*x[rownum];

Then you can call the MILP solver:

solve with milp;

Or just:

solve;

Alternatively, if you specify the LINEARIZE option (available in SAS Viya only) without changing your original model, OPTMODEL will do this linearization for you automatically:

solve linearize;

Re: using proc opmodel nonlinar constrain and binary variable to achieve the same result solver exce

LAIO — Mon, 26 Sep 2022 19:37:52 GMT

great @RobPratt ! It worked. Thanks.