BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

## PROC OPTMODEL large datasets

It is my first time working with optimization in SAS. I want to recreate this following basic example, but using a table that would be thousands by hundreds.

Table 5.2 A Transportation Problem

Boston New York Supply

Detroit      30     20           200

Pittsburgh 40     10           100

Demand  150   150

proc optmodel;

/* specify parameters */

set O={'Detroit','Pittsburgh'};

set D={'Boston','New York'};

number c{O,D}=[30 20

40 10];

number a{O}=[200 100];

number b{D}=[150 150];

/* model description */

var x{O,D} >= 0;

min total_cost = sum{i in O, j in D}c[i,j]*x[i,j];

constraint supply{i in O}: sum{j in D}x[i,j]=a[i];

constraint demand{j in D}: sum{i in O}x[i,j]=b[j];

/* solve and output */

solve;

print x;

My first dataset would look like this:

EmployeeNumber Activity1 Activity2 ... Activity500 MaxHours

xxxxx                     0              1                  1                  40

xxxxx                     0              0                  1                  40

...

My second dataset would have the hours to complete in each activity. For instance,

Hours_Act1 Hours_Act2 Hours_Act3 ... Hours_Act500

#                  ##                  ###                ####

*note that the information may also be placed in 1 variable called Hours_Activity such that

Hours_Activity

#

##

###

...

####

I'll have thousands of distinct employee numbers, the  Activity# variables would be distinct activities that will have several 0's and a few 1's for the activities that the employee is able to complete and MaxHours will have maximum of hours that may be allocated to that employee.

My contraints are sum(activity1-activity500)<=MaxHours and, for all employee numbers and for i=1 to 500, sum(activityi)=Hours_Acti.

I have been researching, but am confused by all the examples. May someone give me the proper idea of an example of this?

Thank you!

If it is required, I can add an employee number called 'Total' that will hold all of the information of Hours_Activity# to resemble the basic example shown above such that

EmployeeNumber Activity1   Activity2 ... Activity500 MaxHours

xxxxx                     0                1                  1                  40

xxxxx                     0                0                  1                  40

...

TOTAL          Hours_activity1  ###               #

Thank you for your help as I am a bit overwhelmed.

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: PROC OPTMODEL large datasets

As a starting point, here's one way to rewrite the documentation example with data set input:

``````data origin_data;
input origin \$10. a;
datalines;
Detroit    200
Pittsburgh 100
;

data destination_data;
input destination \$10. b;
datalines;
Boston     150
New York   150
;

data cost_data;
input origin \$10. destination \$10. c;
datalines;
Detroit    Boston     30
Detroit    New York   20
Pittsburgh Boston     40
Pittsburgh New York   10
;

proc optmodel;
/* specify parameters */
set <str> O;
set <str> D;
number a{O};
number b{D};
read data origin_data into O=[origin] a;
read data destination_data into D=[destination] b;
number c{O,D};
read data cost_data into [origin destination] c;
/* model description */
var x{O,D} >= 0;
min total_cost = sum{i in O, j in D}c[i,j]*x[i,j];
constraint supply{i in O}: sum{j in D}x[i,j]=a[i];
constraint demand{j in D}: sum{i in O}x[i,j]=b[j];
/* solve and output */
solve;
print x;
quit;``````
1 REPLY 1
SAS Super FREQ

## Re: PROC OPTMODEL large datasets

As a starting point, here's one way to rewrite the documentation example with data set input:

``````data origin_data;
input origin \$10. a;
datalines;
Detroit    200
Pittsburgh 100
;

data destination_data;
input destination \$10. b;
datalines;
Boston     150
New York   150
;

data cost_data;
input origin \$10. destination \$10. c;
datalines;
Detroit    Boston     30
Detroit    New York   20
Pittsburgh Boston     40
Pittsburgh New York   10
;

proc optmodel;
/* specify parameters */
set <str> O;
set <str> D;
number a{O};
number b{D};
read data origin_data into O=[origin] a;
read data destination_data into D=[destination] b;
number c{O,D};
read data cost_data into [origin destination] c;
/* model description */
var x{O,D} >= 0;
min total_cost = sum{i in O, j in D}c[i,j]*x[i,j];
constraint supply{i in O}: sum{j in D}x[i,j]=a[i];
constraint demand{j in D}: sum{i in O}x[i,j]=b[j];
/* solve and output */
solve;
print x;
quit;``````
Discussion stats
• 1 reply
• 918 views
• 0 likes
• 2 in conversation