Excel Solver code for SAS

[ismahero2]

Hi, I am trying to find a way to create a code in SAS that would imitate the Excel Solver Add in. I am trying to obtain a list of records that when you sum some of them together will give you the target you are looking for. For example the data below was pasted from excel spreadsheet where I use Solver, You will see 10 records, but only the sum of 5 of them will give you the target figure. My ideal solution will be an exact match, but I can have a tolerance included in the match of 1% difference between the sum of records and the target figure. Can anyone help me with this. I am kind of new in programming and I am having a difficult time creating this.

[RobPratt]

Here's one way to do it with the MILP solver in PROC OPTMODEL, by minimizing the absolute error between the sum and the target:

data indata;
   input wages;
   datalines;
   14611
  250125
2000135
     850
  502525
   55458
  248788
    2211
11252458
2251215
;

%let target = 11520255;

proc optmodel;
   set RECORDS;
   num wages {RECORDS};
   read data indata into RECORDS=[_N_] wages;
   var UseRecord {RECORDS} binary;
   var Surplus >= 0;
   var Slack >= 0;
   min Error = Surplus + Slack;
   con Error_con:
      sum {i in RECORDS} wages * UseRecord - Surplus + Slack = ⌖
   solve;
   print wages UseRecord;
quit;

[ismahero2]

I am having this error...  Does this means I can do it?

ERROR: Procedure OPTOMODEL not found.

[Reeza]

Do you have SAS/OR licensed?

[ismahero2]

Yes I think i have a license.

any way I can do it without optomodel?

[RobPratt]

You have an extra O in the procedure name.

[ismahero2]

Thats ok, I just found out we dont have that OPTMODEL available to use in out license. 

Any other options that you can think of?

[PGStats]

For n<= 20, you could do an exhaustive search in a datastep with function GRAYCODE. - PG

[asdfasdadsf]

There's solve function in proc fcmp.

Have you tried it?

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003193738.htm

[Ksharp]

Check this session. HaiKuo has already give you answer by using graycode() . But first of all ,we should thank PG who bring graycode() in front of us.

https://communities.sas.com/message/139013#139013

Good Luck.

Xia Keshan