SAS Optimization, and SAS Simulation Studio

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2014 04:58 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

02-13-2014 05:26 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

02-13-2014 06:03 PM

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

ERROR: Procedure OPTOMODEL not found.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

02-13-2014 06:16 PM

Do you have SAS/OR licensed?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-13-2014 07:57 PM

Yes I think i have a license.

any way I can do it without optomodel?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

02-13-2014 08:00 PM

You have an extra O in the procedure name.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RobPratt

02-14-2014 08:23 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

02-14-2014 10:44 AM

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

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

07-24-2014 04:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ismahero2

07-24-2014 08:17 AM

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