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

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc Optmodel Help

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic to the Top
- Bookmark
- Subscribe
- Printer Friendly Page

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

02-20-2012 10:47 AM

I'm working on a pricing optimization, but can't think how to draw up the correct constraint.

Hypothetical simple sales example.

stores.sas

Store_id State model_inputs....

1 1 15

2 1 20

3 1 12

4 2 13

5 2 30

set Store;

read data stores into Store [store_id];

max sales = sum{x in Store}(B * price

So I have a store level objective to find optimal price, but I need to add a constraint such that the price of each store in a given state has to be the same. How do I do that, given that the number of stores in each state can be different? If I create another set for State and do {x in Store, y in State}, I'll get an error because [4,1] doesn't exist. Do I need to expand my initial dataset such that [4,1] does exist but is populated with zeros or nulls for the model inputs? Or can I create a multidimensional set that will help solve this? Thanks.

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

02-20-2012 02:45 PM

With respect to what, are we maximizing the function? B??

What's model_input(x) ??

To find the optimal price, we first need to find supply and demand function. In your example we maximize sales (assuming wrt B) which would result in B going infinitiy even with the constraint that each store in a given state has to be the same. This is due to not specifying the demand function.

once you estimated supply and demand function with constraint you can use Lagrangian functions to solve the problem using PROC NLMIXED or PROC NLP.

I could come up with something if you would provide a sample of your dataset.

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

02-20-2012 03:08 PM

Thanks for your reply. That is a sample of the dataset, essentially. Every store and state has a surrogate key like that. Ignore the model_input part... those are just known values that will go into the price elasticity model. They are not variables, just known numbers that do not require constraints. The only variable impacting the objective function is price, with B being the model coefficient (a constant value). I realize the actual objective function I provided is just going to go to infinity. There are other constraints involved that I didn't provide, as they didn't seem relevant to the constraint I'm trying to build.

So with a price elasticity model at the store level, but a price variable at the state level, how do I build a constraint? I was originally thinking something like this.

con region_price{x in 1..N, y in State}:

price[x+1,y] = price[x,y];

max sales = sum{x in 1..N, y in State}(B * price[x,y] + ...... )

N = (number of stores in a state - 1)

State = set of all states

The problem is that in any given state, N is a different value. Can I populate my dataset such that every state has the same number of stores, some are just blanks, for the sake of uniformity? Or is there a better way?

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

02-21-2012 01:11 AM

I'm still not sure what to do with your model but;

I can help you with how to populate your dataset so that every state would have the same number of stores.(If you haven't already done so, sorry for the late reply.)

Say you have a dataset called 'have' below;

Obs | store | state | value |
---|---|---|---|

1 | 1 | 1 | 10 |

2 | 2 | 1 | 10 |

3 | 3 | 1 | 10 |

4 | 4 | 1 | 10 |

5 | 5 | 1 | 10 |

6 | 1 | 2 | 20 |

7 | 2 | 2 | 20 |

8 | 3 | 2 | 20 |

**proc transpose** data= have out = have1;

by state;

**run**;

**data **have2(drop = i);

set have1;

array c(**5**) col1-col5; * change 5 tonumber of columns;

do i = **1** to **5**; * change 5 tonumber of columns;

if c(i) = **.** and _name_ = 'store' then c(i) = i;

if c(i) = **.** and _name_ = 'value' then c(i) = **0**;

end;

**run**;

**proc transpose ** data=have2 out = final(drop = _name_);

by state;

**run**;

**proc print** data=final;

**run**;

Obs | state | store | value |
---|---|---|---|

1 | 1 | 1 | 10 |

2 | 1 | 2 | 10 |

3 | 1 | 3 | 10 |

4 | 1 | 4 | 10 |

5 | 1 | 5 | 10 |

6 | 2 | 1 | 20 |

7 | 2 | 2 | 20 |

8 | 2 | 3 | 20 |

9 | 2 | 4 | 0 |

10 | 2 | 5 | 0 |

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

02-21-2012 01:36 PM

Thanks for the code tips. I used that to create a dataset (store_data.sas) like the one you have above, where "value" is "units" in the code below. Then I have the unique store ids in store_id.sas and unique state ids in state_id.sas. Lastly, I have the model coefficients in store_model.sas.

Running the code below is giving me an "out of memory" error though. And it does so in only 1 or 2 seconds or trying to run. The idea is to find the optimal price point to maximize revenue through the trade-off in price and reduced rental of available units at each store. It's being bogged down by the non-linearity in the objective statement, because price is interacting with rental_rate which is also a function of price. Any ideas?

proc optmodel;

set Stores;

set States;

/* Model Coefficients*/

num intercept;

num price_coeff;

/* Model Variables */

num units{Stores,States};

read data stores_id into Stores = [store];

read data state_id into States = [state];

read data store_model into

intercept

price_coeff;

read data store_data into [store state]

units;

var rental_rate{Stores,States} >= 0 init .5;

var price{State} >= 0 <= 200 init 100;

con rental{x in Stores, y in States}:

rental_rate[x,y] = intercept

+ price_coeff * price

max rev = sum{x in Stores, y in States}

(price

solve;

quit;