topic Re: Excel Solver in SAS (By changing variable cells) in SAS Enterprise Guide

Excel Solver in SAS (By changing variable cells)

BY2 — Tue, 28 Jul 2020 04:04:00 GMT

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!

Re: Excel Solver in SAS (By changing variable cells)

TomKari — Tue, 28 Jul 2020 18:16:49 GMT

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;

Re: Excel Solver in SAS (By changing variable cells)

BY2 — Tue, 28 Jul 2020 18:37:20 GMT

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.

Re: Excel Solver in SAS (By changing variable cells)

TomKari — Thu, 30 Jul 2020 23:28:41 GMT

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

Re: Excel Solver in SAS (By changing variable cells)

BY2 — Mon, 03 Aug 2020 06:47:32 GMT

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.

Re: Excel Solver in SAS (By changing variable cells)

TomKari — Wed, 05 Aug 2020 20:59:29 GMT

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

Re: Excel Solver in SAS (By changing variable cells)

BY2 — Thu, 06 Aug 2020 01:44:26 GMT

Yes, that is correct. Please proceed.