topic Re: Proc Optmodel Help in SAS Procedures

Proc Optmodel Help

buffheman — Mon, 20 Feb 2012 15:47:31 GMT

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 + model_input);

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.

Re: Proc Optmodel Help

VX_Xc — Mon, 20 Feb 2012 19:45:13 GMT

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.

Re: Proc Optmodel Help

buffheman — Mon, 20 Feb 2012 20:08:54 GMT

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?

Proc Optmodel Help

VX_Xc — Tue, 21 Feb 2012 06:11:18 GMT

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

Re: Proc Optmodel Help

buffheman — Tue, 21 Feb 2012 18:36:56 GMT

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*rental_rate[x,y]*units[x,y];

solve;

quit;