BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dsklein12
Calcite | Level 5

Hi -

I currently have two datasets - forecast and guarantees (see attached). I have a portfolio of products each with a distinct product_id. In the forecast dataset I have projected sales and the current discount being realized by a client. In the guarantee dataset is the respective guarantee offered per product. You will notice that this particular client is getting a better deal on some products (discount > guarantee) and worse deals (discount < guarantee) on others. 

 

My goal is to solve for a  common Factor that would be used to multiply by the discount of all the products so that the good deals and bad deals zero out (or common as close to 0 as possible). 

 

See below what I've tried, which obviously is not working. Not sure where to go from here. Any help would be much appreciated! Thanks.

 

proc optmodel;
set <str> FORECAST;
set <str> GUARANTEES;
num discount {FORECAST};
num proj_sales {FORECAST};
num Guarantee {GUARANTEES};
read data work.forecast into FORECAST=[product_id] discount proj_sales;
read data work.guarantees into GUARANTEES=[product_id] Guarantee;

var factor {FORECAST};

impvar new_rate {fac in FORECAST} = discount[fac] * factor[fac];
impvar variance {fac in FORECAST} = new_rate[fac] - Guarantee[gtee]; /*ERROR: gtee is unknown*/
impvar sales_performance {fac in FORECAST} = variance[fac] * proj_sales[fac];

/* I'm not sure how to set a minimum objective with the constraint that it has to be >= 0 */
min x = sum {fac in FORECAST} sales_performance[fac];

con c1:
x >= 0;

solve;
print factor;

quit;

1 ACCEPTED SOLUTION

Accepted Solutions
RobPratt
SAS Super FREQ

If I understand correctly, you want a common value of factor across all products, so your problem has only one decision variable (not one per product), as follows:

proc optmodel;
   set <str> PRODUCTS;
   num discount {PRODUCTS};
   num proj_sales {PRODUCTS};
   num Guarantee {PRODUCTS};
   read data work.indata into PRODUCTS=[product_id] discount proj_sales Guarantee;

   var factor;

   impvar new_rate {p in PRODUCTS} = discount[p] * factor;
   impvar variance {p in PRODUCTS} = new_rate[p] - Guarantee[p];
   impvar sales_performance {p in PRODUCTS} = variance[p] * proj_sales[p];

   con c1: sum {p in PRODUCTS} sales_performance[p] = 0;

   solve;
   print factor;
quit;

 

The resulting value for factor is:

1.14663218567975

 

But that approach is overkill.  Your constraint reduces to the following equation, where factor is the only variable:

sum {p in PRODUCTS} (discount[p] * factor - Guarantee[p]) * proj_sales[p] = 0

 

Solving this linear equation algebraically for factor yields the explicit formula:

factor = (sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])

 

As you can check, this formula also yields:

1.14663218567975

View solution in original post

4 REPLIES 4
RobPratt
SAS Super FREQ

If I understand correctly, you want a common value of factor across all products, so your problem has only one decision variable (not one per product), as follows:

proc optmodel;
   set <str> PRODUCTS;
   num discount {PRODUCTS};
   num proj_sales {PRODUCTS};
   num Guarantee {PRODUCTS};
   read data work.indata into PRODUCTS=[product_id] discount proj_sales Guarantee;

   var factor;

   impvar new_rate {p in PRODUCTS} = discount[p] * factor;
   impvar variance {p in PRODUCTS} = new_rate[p] - Guarantee[p];
   impvar sales_performance {p in PRODUCTS} = variance[p] * proj_sales[p];

   con c1: sum {p in PRODUCTS} sales_performance[p] = 0;

   solve;
   print factor;
quit;

 

The resulting value for factor is:

1.14663218567975

 

But that approach is overkill.  Your constraint reduces to the following equation, where factor is the only variable:

sum {p in PRODUCTS} (discount[p] * factor - Guarantee[p]) * proj_sales[p] = 0

 

Solving this linear equation algebraically for factor yields the explicit formula:

factor = (sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])

 

As you can check, this formula also yields:

1.14663218567975
dsklein12
Calcite | Level 5

This worked!

 

For the linear equation would you just replace the solver call with the formula you mention?

RobPratt
SAS Super FREQ

You can replace everything after READ DATA with this one line:

   print ((sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])) best32.;
dsklein12
Calcite | Level 5

Wow, that simplifies things. Can't express how thankful I am!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 975 views
  • 0 likes
  • 2 in conversation