How do I use proc optmodel to solve for desired multiplier

[dsklein12]

I am wondering if I can replicate my current process of utilizing excel's solver function in SAS (using proc optmodel?). I have a dataset of cars, msrp, invoice, and discount. In attached file you can see each car has its own discount. In aggregate, all the cars have a weighted discount of 8.66%. My desire is to be able to target different aggregate discounts by using a multiplier - the multiplier would be used with the invoice amount to calculate an adjusted invoice. For example, if I wanted a target aggregate discount of 5.00%, what would my multiplier have to be? I currently use solver setting my objective to a value of 5.00% by changing the multiplier cell (B45). In this example, solver calculated 1.04. The invoice*multipler = adj_invoice for each car. In aggregate the sum of all the adj_invoice is $1,205,474 - when you compare this to the msrp you will find the discount is 5.00%. Is this possible with proc optmodel? If not, any other potential solutions? Thank you so much!

Here is my example dataset:

data indata;

input make $ msrp invoice discount;

datalines;

Acura 36945 33337 0.097658682

Audi 25940 23508 0.093754819

BMW 37000 33873 0.084513514

Buick 37895 34357 0.093363241

Cadillac 52795 48377 0.083682167

Chevrolet 42735 37422 0.124324324

Chrysler 17985 16919 0.059271615

Dodge 32235 29472 0.085714286

Ford 41475 36494 0.120096444

GMC 31890 28922 0.093069928

Honda 20140 18451 0.083862959

Hummer 49995 45815 0.083608361

Hyundai 21589 20201 0.064292001

Infiniti 28495 26157 0.082049482

Isuzu 31849 29977 0.058777356

Jaguar 29995 27355 0.088014669

Jeep 27905 25686 0.079519799

Kia 19635 18630 0.05118411

Land-Rover 72250 65807 0.089176471

Lexus 45700 39838 0.128271335

Lincoln 52775 46360 0.121553766

MINI 16999 15437 0.091887758

Mazda 21087 19742 0.063783374

Mercedes-Benz 76870 71540 0.069337843

Mercury 29995 27317 0.089281547

Mitsubishi 30492 28330 0.070903844

Nissan 33840 30815 0.089391253

Oldsmobile 18825 17642 0.062841965

Pontiac 21595 19810 0.082658023

Porsche 56665 49865 0.12000353

Saab 30860 29269 0.051555412

Saturn 20585 19238 0.065435997

Scion 12965 12340 0.04820671

Subaru 19945 18399 0.077513161

Suzuki 23699 22307 0.058736656

Toyota 20510 18926 0.077230619

Volkswagen 35515 32243 0.092130086

Volvo 41250 38851 0.058157576

;

[Reeza]

FYI - I've moved this post to the optimization forum so hopefully the right people can answer your questions.

[RobPratt]

Yes, you can replicate these results by calling the LP solver in PROC OPTMODEL, or you can just algebraically solve the equation:

%let target_discount = 0.05;
proc optmodel;
   set <str> CARS;
   num msrp {CARS};
   num invoice {CARS};
   num discount {CARS};
   read data indata into CARS=[make] msrp invoice discount;
   
   var Multiplier;
   impvar AdjInvoice {car in CARS} = invoice[car] * Multiplier;
   con MyCon:
      1 - (sum {car in CARS} AdjInvoice[car]) / (sum {car in CARS} msrp[car]) = &target_discount;

   solve noobj;
   print Multiplier;

   /* or just skip the solver call and algebraically solve for Multiplier */
   Multiplier = ((1 - &target_discount) * (sum {car in CARS} msrp[car]) / (sum {car in CARS} invoice[car]));
   print Multiplier;

   create data outdata from [make] msrp invoice discount AdjInvoice;
quit;

proc print data=outdata;
   sum msrp invoice discount AdjInvoice;
run;

[dsklein12]

Wow, that's great! Thank you!

If you don't mind, wondering if I can add a layer of complexity? What if the data was broken out into regions and each region had their own target discount - meaning each region would have their own multiplier. Would there be a way to loop through and calculate a multiplier for each region?

For example:

data indata;

input region $ make $ msrp invoice discount;

datalines;

Central Acura 36945 33337 0.097658682

Central Audi 25940 23508 0.093754819

Central BMW 37000 33873 0.084513514

Central Buick 37895 34357 0.093363241

Central Cadillac 52795 48377 0.083682167

Central Chevrolet 42735 37422 0.124324324

Central Chrysler 17985 16919 0.059271615

Central Dodge 32235 29472 0.085714286

Central Ford 41475 36494 0.120096444

Central GMC 31890 28922 0.093069928

West Honda 20140 18451 0.083862959

West Hummer 49995 45815 0.083608361

West Hyundai 21589 20201 0.064292001

West Infiniti 28495 26157 0.082049482

West Isuzu 31849 29977 0.058777356

West Jaguar 29995 27355 0.088014669

West Jeep 27905 25686 0.079519799

West Kia 19635 18630 0.05118411

West Land-Rover 72250 65807 0.089176471

West Lexus 45700 39838 0.128271335

West Lincoln 52775 46360 0.121553766

South MINI 16999 15437 0.091887758

South Mazda 21087 19742 0.063783374

South Mercedes-Benz 76870 71540 0.069337843

South Mercury 29995 27317 0.089281547

South Mitsubishi 30492 28330 0.070903844

South Nissan 33840 30815 0.089391253

South Oldsmobile 18825 17642 0.062841965

South Pontiac 21595 19810 0.082658023

South Porsche 56665 49865 0.12000353

South Saab 30860 29269 0.051555412

East Saturn 20585 19238 0.065435997

East Scion 12965 12340 0.04820671

East Subaru 19945 18399 0.077513161

East Suzuki 23699 22307 0.058736656

East Toyota 20510 18926 0.077230619

East Volkswagen 35515 32243 0.092130086

East Volvo 41250 38851 0.058157576

;

%let central_target = 0.05;

%let west_target = 0.04;

%let south_target = 0.06;

%let east_target = 0.10;

Very much appreciated!

[RobPratt · Accepted solution]

data regiondata;
   input region $ target;
   datalines;
Central 0.05
West    0.04
South   0.06
East    0.10
;

proc optmodel;
   set <str> CARS;
   str region {CARS};
   num msrp {CARS};
   num invoice {CARS};
   num discount {CARS};
   read data indata into CARS=[make] region msrp invoice discount;

   set <str> REGIONS;
   num target {REGIONS};
   read data regiondata into REGIONS=[region] target;

   set CARS_reg {reg in REGIONS} = {car in CARS: region[car] = reg};
 
   var Multiplier {REGIONS};
   impvar AdjInvoice {car in CARS} = invoice[car] * Multiplier[region[car]];
   con MyCon {reg in REGIONS}:
      1 - (sum {car in CARS_reg[reg]} AdjInvoice[car]) / (sum {car in CARS_reg[reg]} msrp[car]) = target[reg];

   solve noobj;
   print Multiplier;

   /* or just skip the solver call and algebraically solve for Multiplier */
   for {reg in REGIONS} Multiplier[reg] = 
      ((1 - target[reg]) * (sum {car in CARS_reg[reg]} msrp[car]) / (sum {car in CARS_reg[reg]} invoice[car]));
   print Multiplier;

   create data outdata from [make] region msrp invoice discount AdjInvoice;
quit;

proc sort data=outdata;
   by region;
run;

proc print data=outdata;
   by region;
   sum msrp invoice AdjInvoice;
run;

[dsklein12]

Absolutely genius. Thank you so much!

[dsklein12]

Hi Robb -

After further using the code you've provided, I've found that I will have instances where the same car will be found in multiple regions. This creates problem with the read data into statement, resulting in observations being left out of the optmization. Is there a way to read in two key variables (thereby creating a unique region-car key)?

Thanks!

[RobPratt]

proc optmodel;
   set <str,str> REGIONS_CARS;
   num msrp {REGIONS_CARS};
   num invoice {REGIONS_CARS};
   num discount {REGIONS_CARS};
   read data indata into REGIONS_CARS=[region make] msrp invoice discount;

   set <str> REGIONS;
   num target {REGIONS};
   read data regiondata into REGIONS=[region] target;

   var Multiplier {REGIONS};
   impvar AdjInvoice {<reg,car> in REGIONS_CARS} = invoice[reg,car] * Multiplier[reg];
   con MyCon {reg in REGIONS}:
      1 - (sum {<(reg),car> in REGIONS_CARS} AdjInvoice[reg,car]) / (sum {<(reg),car> in REGIONS_CARS} msrp[reg,car]) = target[reg];

   solve noobj;
   print Multiplier;

   /* or just skip the solver call and algebraically solve for Multiplier */
   for {reg in REGIONS} Multiplier[reg] = 
      ((1 - target[reg]) * (sum {<(reg),car> in REGIONS_CARS} msrp[reg,car]) / (sum {<(reg),car> in REGIONS_CARS} invoice[reg,car]));
   print Multiplier;

   create data outdata from [region make] msrp invoice discount AdjInvoice;
quit;