Hi,
Sample data:
ID | colA | colB | colC | colD | colE1 | colE2 | colE3 | colE4 | colE5 | colE6 | colE7 | colE8 | colE9 | ColW | ColR | Col_ip | col_i99 | col_IL | col_h | col_sr | col_c | col_ML |
123 | 0.006544 | 0.5018 | 30758444 | 5 | 0.566207 | -0.01396 | 0.245639 | -0.09455 | 0.780985 | 0 | 0 | 0 | 0 | 0.000595 | 0.2065 | -2.48137 | 3.090232 | 1748895 | ||||
456 | 0.00265 | 0.231654 | 28265257 | 4 | 0.42042 | -0.25934 | 0.423668 | 0.759272 | 0 | 0 | 0 | 0 | 0 | 0.000546 | 0.2251 | -2.78821 | 3.090232 | 435886.1 | ||||
789 | 0.006544 | 0.5018 | 20076895 | 8 | 0.174945 | 0.204222 | 0.217982 | 0.021944 | -0.03473 | 0.212329 | 0.041425 | 0.911967 | 0 | 0.000388 | 0.2065 | -2.48137 | 3.090232 | 1141552 | ||||
1122 | 0.012851 | 0.501829 | 20062860 | 4 | 0.42042 | -0.25934 | 0.423668 | 0.759272 | 0 | 0 | 0 | 0 | 0 | 0.000388 | 0.1831 | -2.23069 | 3.090232 | 1585257 | ||||
1455 | 0.035506 | 0.45 | 8535795 | 6 | 0.554116 | 0.341346 | 0.185772 | -0.13075 | 0.144423 | 0.709912 | 0 | 0 | 0 | 0.000165 | 0.1403 | -1.8054 | 3.090232 | 931034.2 | ||||
1788 | 0.00265 | 0.45 | 6898495 | 8 | 0.174945 | 0.204222 | 0.217982 | 0.021944 | -0.03473 | 0.212329 | 0.041425 | 0.911967 | 0 | 0.000133 | 0.2251 | -2.78821 | 3.090232 | 206655.2 | ||||
2121 | 0.029363 | 0.45 | 6614324 | 4 | 0.42042 | -0.25934 | 0.423668 | 0.759272 | 0 | 0 | 0 | 0 | 0 | 0.000128 | 0.1476 | -1.89024 | 3.090232 | 664473.7 | ||||
2454 | 0.029363 | 0.45 | 5936919 | 6 | 0.554116 | 0.341346 | 0.185772 | -0.13075 | 0.144423 | 0.709912 | 0 | 0 | 0 | 0.000115 | 0.1476 | -1.89024 | 3.090232 | 596421.8 | ||||
2787 | 0.029363 | 0.5018 | 5261641 | 5 | 0.566207 | -0.01396 | 0.245639 | -0.09455 | 0.780985 | 0 | 0 | 0 | 0 | 0.000102 | 0.1476 | -1.89024 | 3.090232 | 589429.3 | ||||
3120 | 0.012851 | 0.45 | 3664763 | 5 | 0.566207 | -0.01396 | 0.245639 | -0.09455 | 0.780985 | 0 | 0 | 0 | 0 | 7.09E-05 | 0.1831 | -2.23069 | 3.090232 | 259662.9 | ||||
3453 | 0.012851 | 0.45 | 2186744 | 4 | 0.42042 | -0.25934 | 0.423668 | 0.759272 | 0 | 0 | 0 | 0 | 0 | 4.23E-05 | 0.1831 | -2.23069 | 3.090232 | 154939.5 | ||||
3786 | 0.05013 | 0.45 | 98330.52 | 8 | 0.174945 | 0.204222 | 0.217982 | 0.021944 | -0.03473 | 0.212329 | 0.041425 | 0.911967 | 0 | 1.9E-06 | 0.1298 | -1.64359 | 3.090232 | 12604.4 | ||||
4119 | 0.001941 | 0.45 | 9.27E+08 | 3 | 0.589853 | 0.173527 | 0.788646 | 0 | 0 | 0 | 0 | 0 | 0 | 0.017929 | 0.2289 | -2.88759 | 3.090232 | 22651290 | ||||
4119 | 0.001336 | 0.45 | 17684656 | 3 | 0.589853 | 0.173527 | 0.788646 | 0 | 0 | 0 | 0 | 0 | 0 | 0.000342 | 0.2322 | -3.00315 | 3.090232 | 334349.2 |
Would like to perform Excel Solver in SAS that solves col_h, col_sr, col_c and col_ML. These 4 variables equation are interrelated as shown below:
1. col_sr = sqrt(col_R/Y)
2. col_c = colB*colW*cdf('normal',((Col_ip+Col_R*Col_99)/sqrt(1-Col_R**2)))
3. col_ML = colB*colC*cdf('normal',((Col_ip+col_h*col_sr*Col_99)/sqrt(1-(col_h*col_sr)**2)))
4. col_h = sumproduct of (colE1-colE9, b1-b9)
where b1-b9:
cy1 = sumproduct(col_c*colE1);
.
. (repeat till cy9)
.
.cy9 = sumproduct(col_c*colE9);
lm = sumproduct(cy1-cy9, cy1-cy9);
b1 = cy1/lm
.
. (repeat till b9)
.
b9 = cy9/lm
To solve Y, the difference of sum(col_ML) and sum(col_IL) is value of 0.
**Note: SAS IML and Proc OptModel procedure is not found in my SAS License.
Any help would be greatly appreciated. Thank you!
This is quite a chunk of work! To begin, can you verify that this code is producing what you expect, setting Y and col_h to 1:
data Have;
input ID colA colB colC colD colE1 colE2 colE3 colE4 colE5 colE6 colE7 colE8 colE9 ColW ColR Col_ip col_i99 col_IL col_h col_sr col_c col_ML;
cards;
123 0.006544 0.5018 30758444 5 0.566207 -0.01396 0.245639 -0.09455 0.780985 0 0 0 0 0.000595 0.2065 -2.48137 3.090232 1748895 . . . .
456 0.00265 0.231654 28265257 4 0.42042 -0.25934 0.423668 0.759272 0 0 0 0 0 0.000546 0.2251 -2.78821 3.090232 435886.1 . . . .
789 0.006544 0.5018 20076895 8 0.174945 0.204222 0.217982 0.021944 -0.03473 0.212329 0.041425 0.911967 0 0.000388 0.2065 -2.48137 3.090232 1141552 . . . .
1122 0.012851 0.501829 20062860 4 0.42042 -0.25934 0.423668 0.759272 0 0 0 0 0 0.000388 0.1831 -2.23069 3.090232 1585257 . . . .
1455 0.035506 0.45 8535795 6 0.554116 0.341346 0.185772 -0.13075 0.144423 0.709912 0 0 0 0.000165 0.1403 -1.8054 3.090232 931034.2 . . . .
1788 0.00265 0.45 6898495 8 0.174945 0.204222 0.217982 0.021944 -0.03473 0.212329 0.041425 0.911967 0 0.000133 0.2251 -2.78821 3.090232 206655.2 . . . .
2121 0.029363 0.45 6614324 4 0.42042 -0.25934 0.423668 0.759272 0 0 0 0 0 0.000128 0.1476 -1.89024 3.090232 664473.7 . . . .
2454 0.029363 0.45 5936919 6 0.554116 0.341346 0.185772 -0.13075 0.144423 0.709912 0 0 0 0.000115 0.1476 -1.89024 3.090232 596421.8 . . . .
2787 0.029363 0.5018 5261641 5 0.566207 -0.01396 0.245639 -0.09455 0.780985 0 0 0 0 0.000102 0.1476 -1.89024 3.090232 589429.3 . . . .
3120 0.012851 0.45 3664763 5 0.566207 -0.01396 0.245639 -0.09455 0.780985 0 0 0 0 7.09E-05 0.1831 -2.23069 3.090232 259662.9 . . . .
3453 0.012851 0.45 2186744 4 0.42042 -0.25934 0.423668 0.759272 0 0 0 0 0 4.23E-05 0.1831 -2.23069 3.090232 154939.5 . . . .
3786 0.05013 0.45 98330.52 8 0.174945 0.204222 0.217982 0.021944 -0.03473 0.212329 0.041425 0.911967 0 1.90E-06 0.1298 -1.64359 3.090232 12604.4 . . . .
4119 0.001941 0.45 9.27E+08 3 0.589853 0.173527 0.788646 0 0 0 0 0 0 0.017929 0.2289 -2.88759 3.090232 22651290 . . . .
4119 0.001336 0.45 17684656 3 0.589853 0.173527 0.788646 0 0 0 0 0 0 0.000342 0.2322 -3.00315 3.090232 334349.2 . . . .
run;
data Inter01;
set Have;
Y = 1;
col_h = 1;
col_sr = sqrt(ColR / Y);
col_c = colB * colW * cdf('normal', (Col_ip + (ColR * col_i99)) / sqrt(1 - (ColR**2)));
col_ML = colB * colC * cdf('normal', (Col_ip + (col_h * col_sr * col_i99)) / sqrt(1 - ((col_h * col_sr)**2)));
run;
Yes, Once Y is solved from getting sum of col_ML = sum of col_IL. The rest of the variables will be computed. The four equations are all interdependent on each other, and the tricky part is that the equation of col_h is quite complex.
Appreciate your help, Thanks.
Okay, I'm working on Step 4 now. I'm not familiar with SUMPRODUCT, but I've given something a try. Can you verify that I'm calculating cy1 to cy9 correctly?
cy1 | cy2 | cy3 | cy4 | cy5 | cy6 | cy7 | cy8 | cy9 |
616469143.51 | 159027663.37 | 787563937.92 | 38326186.54 | 32143193.48 | 16022889.35 | 1121528.87 | 24690339.68 | 0.00 |
Hi, cy1 to cy9 should be some smaller values instead of 6 digits number. Please take note of the difference of colC and col_c, hope you didnt use the wrong variable. Apologies for the confusion, let me explain sumproduct by using one of the example below:
1. In this example, In order to obtain cy1, will need to sum up every row of (col_c * colE1). Please refer below:
row1: col_c * colE1
row2: col_c * colE1
...
...
(do the same till the last row and then sum all of them up will be the sumproduct of col_c * colE1).
2. However, it is a little different for the equation of lm where it is the sumproduct of cy1-cy9. Please refer below:
row1: cy1*cy1 + cy2*cy2 + cy3*cy3 + .... + cy9*cy9
Note: lm should be same across all observation.
Thank you.
Yes, you are correct, I did exactly that! Thanks for the catch.
How does this look for cy1 to cy9?
cy1 | cy2 | cy3 | cy4 | cy5 | cy6 | cy7 | cy8 | cy9 |
8.20E-05 | 1.83E-05 | 9.46E-05 | 9.59E-06 | 1.22E-05 | 8.53E-06 | 2.83E-07 | 6.24E-06 | 0.00E+00 |
Yes, that is correct. Please proceed.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.