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;
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 |
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 |
This worked!
For the linear equation would you just replace the solver call with the formula you mention?
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.;
Wow, that simplifies things. Can't express how thankful I am!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.