CODEWITHSUNDEEP
optimizationsasconstraints
pyll
pyll

Reputation: 1764

Proc Optmodel SAS Variable not unique

I am using proc optmodel to solve a problem in which several items must be priced the same within the same location (let's say they are different colors of same product and are not currently priced the same). I know that volume will increase/decrease depending on direction of price change, and I have some MIN/MAX constraints as well.

The problem I am running into is that the procedure is only reading one group of unique SKUs....I think because they repeat. How can I get the procedure to optimize all unique combinations of SKU/LOCATION? I tried just changing the item numbers, which of course works, but is not practical for my business solution. Thanks.

data input_data;
    input SKU DESC $ LOCATION $ OLD_PRICE MIN MAX LIFT OLD_UNITS;
cards;
111 black NY 12.99 10 15 1.3 100
222 white NY 13.45 11 15 .9 150
333 red NY 13.29 13 15 1.6 200
111 black DC 11.75 10 14 1.2 300
222 white DC 11.75 10 14 1.5 100
333 red DC 11.99 10 14 1.7 140
111 black LA 14.21 12 17 2.0 600
222 white LA 14.79 14 17 1.5 500
333 red LA 15.99 13 17 .3 200
444 orange LA 14.11 12 17 .6 300
;
run;

proc optmodel;

set<num> SKU;
string LOCATION{SKU};
string DESC{SKU};
set LOCATIONS = setof{i in SKU} LOCATION[i];
set SKUperLOCATION{gi in LOCATIONS} = {i in SKU: LOCATION[i] = gi};
number OLD_PRICE{SKU}; 
number MIN{SKU};
number MAX{SKU}; 
var NEW_PRICE{gi in LOCATIONs} >= max{i in SKUperLOCATION[gi]} MIN[i] <= min{i in SKUperLOCATION[gi]} MAX[i];
impvar NEW_PRICEbySKU{i in SKU} = NEW_PRICE[LOCATION[i]];
number LIFT{SKU}; 
number OLD_UNITS{SKU}; 

read data input_data into
    SKU=[SKU]
    DESC
    LOCATION
    OLD_PRICE 
    MIN
    MAX 
    LIFT 
    OLD_UNITS;

max  sales=sum{gi in LOCATIONs}
        sum{i in SKUperLOCATION[gi]}
        (NEW_PRICE[gi])*(1-(NEW_PRICE[gi]-OLD_PRICE[i])*LIFT[i]/OLD_PRICE[i])*OLD_UNITS[i];

expand;
solve;

create data results_FAM_maxsales 
    from [SKU]={SKU} 
            DESC
            LOCATION 
            OLD_PRICE
            NEW_PRICE=NEW_PRICEbySKU
            MIN 
            MAX 
            LIFT 
            OLD_UNITS;

print NEW_PRICE sales;
quit;

Upvotes: 1

Views: 112

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

One way would be to set your unique key to be SKU & Location. I haven't used OPTMODEL in a while, but something like this should work.

set<num,str> SKU_Loc;
num old_price{SKU_Loc};

<code>

read data input_data into SKU_Loc = [SKU Location];

<code>

Then change the rest of the code to reference the unique combination of SKU & location.

Upvotes: 1

Related Questions