Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-13-2018 01:01 PM
(950 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 |

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

This worked!

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

**Available on demand!**

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

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.