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

Data Handling in PROC OPTMODEL - Good practice with READ DATA?

Reply
New Contributor
Posts: 2

Data Handling in PROC OPTMODEL - Good practice with READ DATA?

Hi guys,

I am still fairly new to SAS and would be very happy if I could get some help/your opinion with a question. I have started working with PROC OPTMODEL and the data handling remains a bit of a mistery to me: I constructed a simple nonlinear price optimization model: I have 4 products {N} over 2 weeks {wee}. Each product has a cost and elasticity that is stable over the two weeks (hence only only a vector with index {N}) and  "base sales" which vary over the two weeks (hence an array with index {T,N}). Decision variable is price {T,N}. No constraints except for non-negativity. I then formulate an objective function and want to optimize profit (pi) with two nested sums (one for weeks, one for products):  pi = sum{w in wee}(sum{i in N}(base[w,i]*price[w,i]+cost*price[w,i]+(price[w,i]^2)*elast-cost*elast*price[w,i])) 

With a lot of work, I got the code below to go through, however, from a data handling perspective, I have a feeling that I am missing the point.

I have 2 (very similar) questions:

1 - In the actual model later, the data will not be in matrix style but will be one solid table. So, I will have one file that will have all the data in it: product data and and product/week specific data:

(i.e.      week prod cost elast base

            1      1     3.99  -3     200 

            2     1     3.99  -3      180

            1      2     1.22  ....)

What is best practice to handel this in SAS: Would you prep the data via a seperate DATA step or some SQL queries so that they end up in the matrix format already, then pick it up with PROC OPTMODEL ... OR... is there a convenient way that I can handle a table like the above in PROC OPTMODEL? (somehow it feels wrong to transfer it into array/vector format and then use the read data statement in the procedure itself).

2 - I am still struggeling to fully understand the read data statement: So similar to the above, let's say I have the 'move' datalines in the example code in a different, table like, format:

(i.e.

1 Prod1 200

1 Prod2 105

1 Prod3 40

1 Prod4 70

2 Prod1 180

2 Prod2 135

2 Prod3 60

2 Prod4 71)

How would I transfer these into the array/index format I need for PROC OPTMODEL?

I have done a lot of searching and none of the examples that I have found did actually supply the data in any other format but the exact one needed later in the procedure.

Any help with this is highly appreciated. So are any useful reading recommendations.

Many thanks & kind regards

TimoK 

********************************

Here is the code:

data Prod;

      input prods cost elast @@;

      datalines;

1 3.99 -3

2 1.22 -1.6

3 1.66 -1

4 2.89 -2

  data move;

       input week prod1 prod2 prod3 prod4;

       datalines;

    1 200 105 40 70

    2 180 135 60 71

;

    proc optmodel;

    set N = 1..4;  /* Number of Products */

    set wee; /* Weeks */

    set S;   /* A set to read the array since it will not let me 'recycle' N in that case */

    number base{wee, N};       

    number cost{N};

    number elast{N};

    var price{wee, N};

        read data move

        into wee=[week]

        {d in N} < base[week, d]=col("prod"||d) >;   /*read array*/     

        read data Prod into S=[prods] cost elast;

        print cost elast;                            /*read vector*/

   max pi = sum{w in wee}(sum{i in N}(base[w,i]*price[w,i]+cost*price[w,i]+(price[w,i]^2)*elast-cost*elast*price[w,i]));

   solve with nlpc / printfreq=1;

   print pi price; 

quit;

SAS Employee
Posts: 448

Re: Data Handling in PROC OPTMODEL - Good practice with READ DATA?

We have a new book of mathematical programming examples available as part of the SAS/OR documentation:

https://support.sas.com/pubscat/bookdetails.jsp?pc=64980 (click on View This Title Online for a free pdf version).

READ DATA and CREATE DATA are very powerful and expressive statements that are probably best learned via examples, and the book includes several examples of reading and creating dense and sparse data sets.

For your particular example, the code below does what I think you have in mind.  I have also eliminated set S and populated set N during the READ DATA.  You could also populate wee by using READ DATA if you have a new data set with one observation per week.

data move2;
    input week prod base;
    datalines;
1 1 200
1 2 105
1 3 40
1 4 70
2 1 180
2 2 135
2 3 60
2 4 71
;

proc optmodel;
    set N;  /* Number of Products */
    set wee = 1..2; /* Weeks */
    number base{wee, N};       
    number cost{N};
    number elast{N};
    read data Prod into N=[prods] cost elast;
    read data move2 into [week prod] base;

New Contributor
Posts: 2

Re: Data Handling in PROC OPTMODEL - Good practice with READ DATA?

Ooops. Rob, I guess I never said thank you for your help. Very helpful source and I have made some real progress. Thanks again.

Valued Guide
Posts: 2,175

Re: Data Handling in PROC OPTMODEL - Good practice with READ DATA?

Hi

for a recent client we loaded (with read data) the matrix in a similar way to the example desmonstrated at "figure 5.26 Demand data"  http://support.sas.com/documentation/cdl/en/ormpug/66851/HTML/default/ormpug_optmodel_syntax11.htm#o...

However this seemed to demand the input data have a series of columns with consecutive numeric suffix. That I was able to "semi-automate".

The key for the rows is the defined (string type) column.

I just thought it would be good to have had the original column names as the key to the columns and I couldn't see how.

Is there an example somewhere?

peterC

SAS Employee
Posts: 448

Re: Data Handling in PROC OPTMODEL - Good practice with READ DATA?

Here's an approach that uses PROC TRANSPOSE to get the names:

data mydata;

   input a b c;

   datalines;

1 2 3

4 5 6

;

proc transpose data=mydata(obs=0) out=trans;

run;

proc optmodel;

   /* Declare a set for the data set variable names */

   set <str> VARNAMES;

   read data trans into VARNAMES=[_name_];

   put VARNAMES=;

   /* Declare a set for the data set observations */

   set OBS;

   /* Read the data */

   num c {OBS, VARNAMES};

   read data mydata into OBS=[_N_] {j in VARNAMES} <c[_N_,j]=col(j)>;

   print c;

quit;

Valued Guide
Posts: 2,175

Re: Data Handling in PROC OPTMODEL - Good practice with READ DATA?

thank you Rob

think I'm beginning to understand.....

using the vector of varnames as index/key to the column names

neat

peterC

Ask a Question
Discussion stats
  • 5 replies
  • 1998 views
  • 0 likes
  • 3 in conversation