Minimize Function with Opt Model

[harmonic · Accepted solution]

Yes but I would like to use the read data after I imported my xlsx, is that possible?

[Ksharp]

Sure. the Warning message in LOG you can ignore , since it has nothing to do with optimal problem.

proc import datafile="C:\Users\xiakeshan\Downloads\input.xlsx" out=have dbms=xlsx replace;
run;

proc optmodel ;
set a ;
num a1{i in a}=i;
set b;
num b1{i in b}=i;
set c;
set d;
num d1{i in d}=i;
set e;
num e1{i in e}=i;

read data have into a=[a] ;
read data have into b=[b] ;
read data have into c=[c] ;
read data have into d=[d] ;
read data have into e=[e] ;
num min_c=min{i in c} i;
num max_c=max{i in c} i;

var var_a{a} binary;
var var_b{b} binary;
var var_c >=min_c  <=max_c;
var var_d{d} binary;
var var_e{e} binary;

impvar sum=-8*sum{_a in a} _a*var_a[_a] +
           -2*sum{_b in b} _b*var_b[_b] +
	       -2*var_c +
	       -4*sum{_d in d} _d*var_d[_d] +
            2*sum{_e in e} _e*var_e[_e] ;
min obj=sum;
con con_total:sum>=1;
con con_a:sum{i in a} var_a[i]=1;
con con_b:sum{i in b} var_b[i]=1;
con con_d:sum{i in d} var_d[i]=1;
con con_e:sum{i in e} var_e[i]=1;

solve ;

print a1 var_a ; 
print b1 var_b ;
print   var_c ;
print d1 var_d ;
print e1 var_e ;
quit;

[harmonic]

Thank you @Ksharp, just to add more complexity, if I have a categorical variable in my table, for instance in the variable "e" is a string, can I minimize the function group by string variable? An optimization for each category.

[Ksharp]

If you want to process the data like BY statement of PROC .
The most simple way is spliting this data into many sub-dataset and make a macro to run the code separatedly.
If you want more sophisticated method, I think @RobPratt would help you .

[RobPratt]

Here are a few ways to implement BY-group processing:

https://support.sas.com/kb/42/332.html