Excel Solver in sas

[nw2014]

  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

[RobPratt]

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.