Operations Research topics: SAS/OR,
SAS Optimization, and SAS Simulation Studio

Excel Solver code for SAS

Reply
Contributor
Posts: 22

Excel Solver code for SAS

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.

Capture.JPG

SAS Employee
Posts: 416

Re: Excel Solver code for SAS

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;

Contributor
Posts: 22

Re: Excel Solver code for SAS

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

ERROR: Procedure OPTOMODEL not found.

Grand Advisor
Posts: 16,880

Re: Excel Solver code for SAS

Do you have SAS/OR licensed?

Contributor
Posts: 22

Re: Excel Solver code for SAS

Yes I think i have a license.

any way I can do it without optomodel?

SAS Employee
Posts: 416

Re: Excel Solver code for SAS

You have an extra O in the procedure name.

Contributor
Posts: 22

Re: Excel Solver code for SAS

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

Any other options that you can think of?

Respected Advisor
Posts: 4,606

Re: Excel Solver code for SAS

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

PG
SAS Employee
Posts: 1

Re: Excel Solver code for SAS

Grand Advisor
Posts: 9,451

Re: Excel Solver code for SAS

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

Ask a Question
Discussion stats
  • 9 replies
  • 2264 views
  • 0 likes
  • 6 in conversation