Calcite | Level 5

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

@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);

%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;

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.);

2 REPLIES 2
SAS Super FREQ

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

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;``

Calcite | Level 5

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

great @RobPratt ! It worked. Thanks.

Discussion stats
• 2 replies
• 322 views
• 1 like
• 2 in conversation