PROC OPTMODEL large datasets

[JG-RQ]

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.

[RobPratt · Accepted solution]

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;