topic Re: Excel Solver code for SAS in Mathematical Optimization, Discrete-Event Simulation, and OR

Excel Solver code for SAS

ismahero2 — Thu, 13 Feb 2014 21:58:13 GMT

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.

Re: Excel Solver code for SAS

RobPratt — Thu, 13 Feb 2014 22:26:27 GMT

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 = &target;
   solve;
   print wages UseRecord;
quit;

Re: Excel Solver code for SAS

ismahero2 — Thu, 13 Feb 2014 23:03:32 GMT

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

ERROR: Procedure OPTOMODEL not found.

Re: Excel Solver code for SAS

Reeza — Thu, 13 Feb 2014 23:16:42 GMT

Do you have SAS/OR licensed?

Re: Excel Solver code for SAS

ismahero2 — Fri, 14 Feb 2014 00:57:08 GMT

Yes I think i have a license.

any way I can do it without optomodel?

Re: Excel Solver code for SAS

RobPratt — Fri, 14 Feb 2014 01:00:33 GMT

You have an extra O in the procedure name.

Re: Excel Solver code for SAS

ismahero2 — Fri, 14 Feb 2014 13:23:14 GMT

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?

Re: Excel Solver code for SAS

PGStats — Fri, 14 Feb 2014 15:44:55 GMT

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

Re: Excel Solver code for SAS

asdfasdadsf — Thu, 24 Jul 2014 08:36:24 GMT

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

Re: Excel Solver code for SAS

Ksharp — Thu, 24 Jul 2014 12:17:09 GMT

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