Excel Solver in sas

nw2014 — Fri, 24 Jan 2014 16:59:16 GMT

Hi,

I am new enough to sas, and I want to create a code in sas similar to the excel solver tool in the attached example. The weighted average of data1 and data2 is 2%. However if the sum of data 2 increased to 4m, what would be the optimal split by customer to get a weighted average of 1.8%. I have seen other post that say using PROC NLP is the best way to go, however I am not sure where to start.

Tks

Re: Excel Solver in sas

RobPratt — Fri, 24 Jan 2014 17:47:34 GMT

PROC NLP is considered legacy and is no longer under active development. You should instead use PROC OPTMODEL, and there is a book of examples here:

SAS/OR(R) 13.1 User's Guide: Mathematical Programming Examples

If I understand your problem correctly, the following code does what you want:

data mydata;
   input cust $ data1 data2;
   datalines;
Cust1 0.0128 42815
Cust2 0.0128 159541
Cust3 0.06     575796
Cust4 0.004 256480
Cust5 0.003 107
Cust6 0.002 174744
Cust7 0.0128 336044
Cust8 0.0128 17032
Cust9 0.0128 314733
Cust10 0.003 217724
Cust11 0.0128 219205
Cust12 0.0104 452909
Cust13 0.0105 4263
Cust14 0.0128 230952
Cust15 0.07     34579
Cust16 0.0128 197932
;

proc optmodel;
   set CUSTOMERS;
   str cust {CUSTOMERS};
   num data1 {CUSTOMERS};
   num data2 {CUSTOMERS};
   read data mydata into CUSTOMERS=[_N_] cust data1 data2;

num wa = 0.018;
num n = 4000000;

   var X {CUSTOMERS} >= 0;
   con Cardinality:
      sum {i in CUSTOMERS} X = n;
   con WeightedAverage:
      sum {i in CUSTOMERS} data1 * X = wa * sum {i in CUSTOMERS} X;

min Error = sum {i in CUSTOMERS} (X - data2)^2;

solve;
print cust data2 X;

put (sum {i in CUSTOMERS} data2)=;
put ((sum {i in CUSTOMERS} data1 * data2)/sum {i in CUSTOMERS} data2)=;

put (sum {i in CUSTOMERS} X)=;
put ((sum {i in CUSTOMERS} data1 * X)/sum {i in CUSTOMERS} X)=;
quit;

The resulting optimal objective is 39,236,599,668, which is smaller than the sum of squares 40,718,058,821 attained by your solution in the "Optimal" column, so maybe I have misinterpreted your problem.

topic Re: Excel Solver in sas in Mathematical Optimization, Discrete-Event Simulation, and OR

Excel Solver in sas

Re: Excel Solver in sas