Excel Solver in SAS

[SC13]

Hi,

I'm trying to create the below output from an excel solver in SAS. I don't fully understand how the excel solver works to help me understand how to produce the equivalent in SAS. In red below I have a weighted average of data_A and data_B that gives 2.42%. I have an aimed WA of 1.73%. The excel solver takes the distribution in the 'data_B' column and 'adjusts' it ('Optimal' column) to create the WA of 1.73%. I have read a similar question on this forum and I have attempted to use the PROC OPTMODEL statement within SAS however it seems to try and populate each 'Group' bucket, even though you can see there is nothing in Groups 1-9 for data_B. Any help would be greatly appreciated.

Group    data_A      data_B         Optimal

1           0.04%        0.00%           0.00%

2           0.06%        0.00%           0.00%

3           0.10%        0.00%           0.00%

4           0.12%        0.00%           0.00%

5           0.14%        0.00%           0.00%

6           0.16%        0.00%           0.00%

7           0.24%        0.00%           0.00%

8           0.38%        0.00%           0.00%

9           0.43%        0.00%           0.00%

10         0.69%        2.25%           4.24%

11         1.13%        51.46%         51.45%

12         1.27%        37.55%         37.54%

13         2.10%        3.64%           3.62%

14         3.30%        0.36%           0.31%

15         4.40%        0.03%           0.00%

16         5.50%        0.73%           0.65%

17         8.50%        0.99%           0.86%

18         10.50%      0.03%           0.00%

19         12.80%      0.88%           0.67%

20         23.60%      0.21%           0.00%

21         34.40%      0.28%           0.00%

22         55.00%      1.59%           0.66%

                              2.42%          1.73%

   Aimed WA= 1.73

[RobPratt]

Please attach the Excel file and what you tried in PROC OPTMODEL.

[SC13]

Hi, thanks for your reply. I am unable to attach files. I have included outputs from excel and SAS along with the code I used within SAS below.

Group      data_A   data_B                            Excel Output (Optimal)
1             0.0004    -                                     -
2             0.0006    -                                     -
3             0.0010    -                                     -
4             0.0012    -                                     -
5             0.0014    -                                     -
6             0.0016    -                                     -
7             0.0024    -                                     -
8             0.0038    -                                     -
9             0.0043    -                                     -
10           0.0069    0.022483486781891900    0.042355082898388600
11           0.0113    0.514594797489934000    0.514519715444687000
12           0.0127    0.375488401572479000    0.375389429796759000
13           0.0210    0.036393345861123800    0.036152742061940400
14           0.0330    0.003585545809150660    0.003140172814439060
15           0.0440    0.000285459561236310    -
16           0.0550    0.007315478157329430    0.006494694976113490   
17           0.0850    0.009936819714355660    0.008604113528427010
18           0.1050    0.000333424924714664    -
19           0.1280    0.008799090388477180    0.006732627836462830
20           0.2360    0.002066781726166860    -
21           0.3440    0.002838434738520030    -
22           0.5500    0.015878933274620600    0.006611420642782350

WA                       0.024239772965542800    0.017323217491320000
  

Group    data_A   data_B                       SAS Output (X)
1           0.0004    -                                0.0003064500000000
2           0.0006    -                                0.0003028200000000
3           0.0010    -                                0.0002955700000000
4           0.0012    -                                0.0002919400000000
5           0.0014    -                                0.0002883100000000
6           0.0016    -                                0.0002846900000000
7           0.0024    -                                0.0002701800000000
8           0.0038    -                                0.0002448000000000
9           0.0043    -                                0.0002357300000000
10         0.0069    0.02248349                 0.0226720700000000
11         0.0113    0.51459480                 0.5147036000000000
12         0.0127    0.37548840                 0.3755718100000000
13         0.0210    0.03639335                 0.0363262500000000
14         0.0330    0.00358555                 0.0033008600000000
15         0.0440    0.00028546                 -
16         0.0550    0.00731548                 0.0066318700000000
17         0.0850    0.00993682                 0.0087092200000000
18         0.1050    0.00033342                 -
19         0.1280    0.00879909                 0.0067917700000000
20         0.2360    0.00206678                 -
21         0.3440    0.00283843                 -
22         0.5500    0.01587893                 0.0062195000000000

WA                     0.0242397689610000   0.0173000058104274

SAS Code:

DATA EXAMPLE1;

   INPUT GROUP $ DATA_A DATA_B;

   DATALINES;

1 0.0004 0.00

2 0.0006 0.00

3 0.0010 0.00

4 0.0012 0.00

5 0.0014 0.00

6 0.0016 0.00

7 0.0024 0.00

8 0.0038 0.00

9 0.0043 0.00

10 0.0069 0.0224834867818919

11 0.0113 0.514594797489934

12 0.0127 0.375488401572479

13 0.0210 0.0363933458611238

14 0.0330 0.00358554580915066

15 0.0440 0.00028545956123631

16 0.0550 0.00731547815732943

17 0.0850 0.00993681971435566

18 0.1050 0.000333424924714664

19 0.1280 0.00879909038847718

20 0.2360 0.00206678172616686

21 0.3440 0.00283843473852003

22 0.5500 0.0158789332746206

;

RUN;

PROC OPTMODEL;

SET EXAMPLE;

STR GROUP {EXAMPLE};

NUM DATA_A {EXAMPLE};

NUM DATA_B {EXAMPLE};

READ DATA EXAMPLE1 INTO EXAMPLE=[_N_] GROUP DATA_A DATA_B;

NUM WA = 0.0173;

VAR X {EXAMPLE} >= 0;

CON WEIGHTEDAVERAGE:

SUM {I IN EXAMPLE} DATA_A * X = WA * SUM {I IN EXAMPLE} X;

MIN ERROR = SUM {I IN EXAMPLE} (X - DATA_B)^2;

SOLVE;

[RobPratt · Accepted solution]

Nothing in your optimization model prevents X from being positive for all groups, and the solution returned by PROC OPTMODEL has objective value 0.00011268, which is better than the Excel solution's objective value of 0.00050028.

Even if you fix these X to 0 as follows, you can get an objective value of 0.00011354:

     for {i in EXAMPLE: data_b = 0} fix X = 0;

By the way, you should be able to attach files if you use the "Use advanced editor" button.

[SC13]

Hi RobPratt,

Thank you for the above - exactly what I needed.