SAS Proc Optmodel Constraint Syntax

Question

I have an optimization exercise I am trying to work through and am stuck again on the syntax. Below is my attempt, and I'd really like a thorough explanation of the syntax in addition to the solution code. I think it's the specific index piece that I am having trouble with.

The problem: I have an item that I wish to sell out of within ten weeks. I have a historical trend and wish to alter that trend by lowering price. I want maximum margin dollars. The below works, but I wish to add two constraints and can't sort out the syntax. I have spaces for these two constraints in the code, with my brief explanation of what I think they may look like. Here is a more detailed explanation of what I need each constraint to do.

inv_cap=There is only so much inventory available at each location. I wish to sell it all. For location 1 it is 800, location 2 it is 1200. The sum of the column FRC_UNITS should equal this amount, but cannot exceed it.

price_down_or_same=The price cannot bounce around, so it needs to always be less than or more than the previous week. So, price(i)<=price(i-1) where i=week.

Here is my attempt. Thank you in advance for assistance.

*read in data;
data opt_test_mkdown_raw;
    input 
        ITM_NBR
        ITM_DES_TXT $
        LCT_NBR
        WEEK
        LY_UNITS
        ELAST 
        COST
        PRICE
        TOTAL_INV;
cards;
1 stuff 1 1 300 1.2 6 10 800
1 stuff 1 2 150 1.2 6 10 800
1 stuff 1 3 100 1.2 6 10 800
1 stuff 1 4 60 1.2 6 10 800
1 stuff 1 5 40 1.2 6 10 800
1 stuff 1 6 20 1.2 6 10 800
1 stuff 1 7 10 1.2 6 10 800
1 stuff 1 8 10 1.2 6 10 800
1 stuff 1 9 5 1.2 6 10 800
1 stuff 1 10 1 1.2 6 10 800
1 stuff 2 1 400 1.1 6 9 1200
1 stuff 2 2 200 1.1 6 9 1200
1 stuff 2 3 100 1.1 6 9 1200
1 stuff 2 4 100 1.1 6 9 1200
1 stuff 2 5 100 1.1 6 9 1200
1 stuff 2 6 50 1.1 6 9 1200
1 stuff 2 7 20 1.1 6 9 1200
1 stuff 2 8 20 1.1 6 9 1200
1 stuff 2 9 5 1.1 6 9 1200
1 stuff 2 10 3 1.1 6 9 1200
;
run;

data opt_test_mkdown_raw;
    set opt_test_mkdown_raw;
    ITM_LCT_WK=cats(ITM_NBR, LCT_NBR, WEEK);
    ITM_LCT=cats(ITM_NBR, LCT_NBR);
run;

proc optmodel;

*set variables and inputs;

set<string> ITM_LCT_WK;
number ITM_NBR{ITM_LCT_WK};
string ITM_DES_TXT{ITM_LCT_WK};
string ITM_LCT{ITM_LCT_WK};
number LCT_NBR{ITM_LCT_WK};
number WEEK{ITM_LCT_WK}; 
number LY_UNITS{ITM_LCT_WK}; 
number ELAST{ITM_LCT_WK}; 
number COST{ITM_LCT_WK}; 
number PRICE{ITM_LCT_WK};
number TOTAL_INV{ITM_LCT_WK}; 

*read data into procedure;
read data opt_test_mkdown_raw into
    ITM_LCT_WK=[ITM_LCT_WK]
    ITM_NBR
    ITM_DES_TXT
    ITM_LCT
    LCT_NBR
    WEEK
    LY_UNITS
    ELAST
    COST
    PRICE
    TOTAL_INV; 

var NEW_PRICE{i in ITM_LCT_WK};
impvar FRC_UNITS{i in ITM_LCT_WK}=(1-(NEW_PRICE[i]-PRICE[i])*ELAST[i]/PRICE[i])*LY_UNITS[i];

con ceiling_price {i in ITM_LCT_WK}: NEW_PRICE[i]<=PRICE[i];
/*con inv_cap {j in ITM_LCT}: sum{i in ITM_LCT_WK}=I want this to be 800 for location 1 and 1200 for location 2;*/
con supply_last {i in ITM_LCT_WK}: FRC_UNITS[i]>=LY_UNITS[i];
/*con price_down_or_same {j in ITM_LCT} : NEW_PRICE[week]<=NEW_PRICE[week-1];*/

*state function to optimize;
max  margin=sum{i in ITM_LCT_WK}
    (NEW_PRICE[i]-COST[i])*(1-(NEW_PRICE[i]-PRICE[i])*ELAST[i]/PRICE[i])*LY_UNITS[i];

/*expand;*/
solve;

*write output dataset;
create data results_MKD_maxmargin
    from 
    [ITM_LCT_WK]={ITM_LCT_WK} 
    ITM_NBR
    ITM_DES_TXT
    LCT_NBR
    WEEK
    LY_UNITS
    FRC_UNITS
    ELAST
    COST
    PRICE
    NEW_PRICE
    TOTAL_INV; 

*write results to window;
print 
/*NEW_PRICE */
margin;
quit;

Answer 1

The main difficulty is that in your application, decisions are indexed by (Item,Location) pairs and Weeks, but in your code you have merged (Item,Location,Week) triplets. I rather like that use of the data step, but the result in this example is that your code is unable to refer to specific weeks and to specific pairs.

The fix that changes your code the least is to add these relationships by using defined sets and inputs that OPTMODEL can compute for you. Then you will know which triplets refer to each combination of (Item,Location) pair and week:

/* This code creates a set version of the Item x Location pairs 
   that you already have as strings */ 
set ITM_LCTS = setof{ilw in ITM_LCT_WK} itm_lct[ilw];
/* For each Item x Location pair, define a set of which 
   Item x Location x Week entries refer to that Item x Location */
set ILWperIL{il in ITM_LCTS} = {ilw in ITM_LCT_WK: itm_lct[ilw] = il};

With this relationship you can add the other two constraints.

I left your code as is, but applied to the new code a convention I find useful, especially when there are similar names like itm_lct and ITM_LCTS:

• sets as all caps;
• input parameters start with lowercase;
• output (vars, impvars, and constraints) start with Uppercase */

Here is the new OPTMODEL code:

proc optmodel;

*set variables and inputs;

set<string> ITM_LCT_WK;
number ITM_NBR{ITM_LCT_WK};
string ITM_DES_TXT{ITM_LCT_WK};
string ITM_LCT{ITM_LCT_WK};
number LCT_NBR{ITM_LCT_WK};
number WEEK{ITM_LCT_WK}; 
number LY_UNITS{ITM_LCT_WK}; 
number ELAST{ITM_LCT_WK}; 
number COST{ITM_LCT_WK}; 
number PRICE{ITM_LCT_WK};
number TOTAL_INV{ITM_LCT_WK}; 

*read data into procedure;
read data opt_test_mkdown_raw into
    ITM_LCT_WK=[ITM_LCT_WK]
    ITM_NBR
    ITM_DES_TXT
    ITM_LCT
    LCT_NBR
    WEEK
    LY_UNITS
    ELAST
    COST
    PRICE
    TOTAL_INV; 

var    NEW_PRICE{i in ITM_LCT_WK} <= price[i];
impvar FRC_UNITS{i in ITM_LCT_WK} = 
       (1-(NEW_PRICE[i]-PRICE[i])*ELAST[i]/PRICE[i]) * LY_UNITS[i];

* Moved to bound
con ceiling_price {i in ITM_LCT_WK}: NEW_PRICE[i] <= PRICE[i];

con supply_last{i in ITM_LCT_WK}: FRC_UNITS[i] >= LY_UNITS[i];

/* This code creates a set version of the Item x Location pairs 
   that you already have as strings */ 
set ITM_LCTS = setof{ilw in ITM_LCT_WK} itm_lct[ilw];
/* For each Item x Location pair, define a set of which 
   Item x Location x Week entries refer to that Item x Location */
set ILWperIL{il in ITM_LCTS} = {ilw in ITM_LCT_WK: itm_lct[ilw] = il};
/* I assume that for each item and location 
   the inventory is the same for all weeks for convenience,
   i.e., that is not a coincidence */
num inventory{il in ITM_LCTS} = max{ilw in ILWperIL[il]} total_inv[ilw];
con inv_cap {il in ITM_LCTS}: 
    sum{ilw in ILWperIL[il]} Frc_Units[ilw] = inventory[il]; 

num lastWeek = max{ilw in ITM_LCT_WK} week[ilw];
/* Concatenating indexes is not the prettiest, but gets the job done here*/
con Price_down_or_same {il in ITM_LCTS, w in 2 .. lastWeek}: 
    New_Price[il || w] <= New_Price[il || w - 1];*/

*state function to optimize;
max  margin=sum{i in ITM_LCT_WK}
    (NEW_PRICE[i]-COST[i])*(1-(NEW_PRICE[i]-PRICE[i])*ELAST[i]/PRICE[i])*LY_UNITS[i];

expand;
solve;

*write output dataset;
create data results_MKD_maxmargin
    from 
    [ITM_LCT_WK]={ITM_LCT_WK} 
    ITM_NBR
    ITM_DES_TXT
    LCT_NBR
    WEEK
    LY_UNITS
    FRC_UNITS
    ELAST
    COST
    PRICE
    NEW_PRICE
    TOTAL_INV; 

*write results to window;
print 
    NEW_PRICE FRC_UNITS
    margin
;

quit;