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

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-23-2012 02:56 PM

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;

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

Posted in reply to TimoK

05-25-2012 11:28 AM

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;

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

Posted in reply to RobPratt

07-01-2012 12:31 PM

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

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

Posted in reply to RobPratt

01-23-2014 12:36 PM

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

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

Posted in reply to Peter_C

01-23-2014 01:13 PM

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;

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

Posted in reply to RobPratt

01-23-2014 02:20 PM

thank you Rob

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

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

neat

peterC