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

[LAIO]

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

[RobPratt]

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;

[LAIO]

great @RobPratt ! It worked. Thanks.