Solve an equation to get a parameter value for each observation

nazmul — Sun, 09 Feb 2020 23:29:13 GMT

Dear Everyone,

I am trying to get a parameter value (R) for each observation by solving the following equation. I put my SAS codes below. However, I am not getting any answers. I believe my code is wrong. I then use the excel solver to estimate the value (R) for your reference. I also put the data below. Please help me with your SAS code to solve the problem.

I just found the following link https://communities.sas.com/t5/General-SAS-Programming/Solve-Equation/td-p/231581 where they used proc model to solve an equation but I am not sure how to apply it in my case. Could you please help me?

SAS code

proc nlp noprint data=S1 absgconv=1e-15 OUTEST=T1; by obs;
bounds R>=0, R<=1;
min f;
decvar R=1;
f = abs(Price2_Y0-(B0_T1+((FROE1_T1_Med-R)/(1+R))*B0_T1
+((FROE2_T1_Med-R)/((1+R)**2))*B1_T1_Med
+((FROE3_T1_Med-R)/((1+R)**3))*B2_T1_Med
+((FROE4_T1_Med_I1-R)/((1+R)**4))*B3_T1_Med
+((FROE5_T1_Med_I1-R)/((1+R)**5))*B4_T1_Med_I1
+((FROE6_T1_Med_I1-R)/((1+R)**6))*B5_T1_Med_I1
+((FROE7_T1_Med_I1-R)/((1+R)**7))*B6_T1_Med_I1
+((FROE8_T1_Med_I1-R)/((1+R)**8))*B7_T1_Med_I1
+((FROE9_T1_Med_I1-R)/((1+R)**9))*B8_T1_Med_I1
+((FROE10_T1_Med_I1-R)/((1+R)**10))*B9_T1_Med_I1
+((FROE11_T1_Med_I1-R)/((1+R)**11))*B10_T1_Med_I1
+((FROE12_T1_Med_I1-R)/(R*(1+R)**11))*B11_T1_Med_I1));
run;
data T1; set T1;
if _TYPE_='PARMS';
keep obs R; run;
data O1; merge S1 (in=xx) T1(in=yy); by obs; if xx and yy; run;

Sample log file: I get errors for every single observation

NOTE: The above message was for the following BY group:
obs=56822
ERROR: There are references to missing variables when the program code is
executed for _OBS_= 1
WARNING: Your program statements cannot be executed completely.
WARNING: In a total of 786 calls an error occurred during execution of the
program statements. NLP attempted to recover by using a shorter step
size.
NOTE: The above message was for the following BY group:
obs=56823
WARNING: Your program statements cannot be executed completely.
WARNING: Your program statements cannot be executed completely.
ERROR: NRRIDG Optimization cannot be completed.
WARNING: Optimization routine cannot improve the function value.
WARNING: In a total of 788 calls an error occurred during execution of the
program statements. NLP attempted to recover by using a shorter step

data

obs	B0_T1	Price2_Y0	B1_T1_Med	B2_T1_Med	B3_T1_Med	B4_T1_Med_I1	B5_T1_Med_I1	B6_T1_Med_I1	B7_T1_Med_I1	B8_T1_Med_I1	B9_T1_Med_I1	B10_T1_Med_I1	B11_T1_Med_I1	FROE1_T1_Med	FROE2_T1_Med	FROE3_T1_Med	FROE4_T1_Med_I1	FROE5_T1_Med_I1	FROE6_T1_Med_I1	FROE7_T1_Med_I1	FROE8_T1_Med_I1	FROE9_T1_Med_I1	FROE10_T1_Med_I1	FROE11_T1_Med_I1	FROE12_T1_Med_I1	R_SAS	R_excel	1	2	3	4	5	6	7	8	9	10	11	12	Sum	dif
1	12.04	17.5	12.96	14.11	15.46	17.09186	19.06786	21.46758	24.39325	27.97684	32.38978	37.85617	44.67083	0.070988	0.081502	0.087322	0.095476	0.10363	0.111784	0.119937	0.128091	0.136245	0.144399	0.152553	0.160707	1	11%	-0.4457	-0.3212	-0.25475	-0.16857	-0.08567	-0.00377	0.079354	0.166052	0.258872	0.360704	0.474932	5.399746	17.5	-3.5E-06
2	4.1943	10.61	-2.66464	-9.81995	-17.441	-25.5259	-31.7687	-34.3922	-32.9443	-28.2985	-22.0324	-15.6856	-10.2866	-0.60796	-0.1721	-0.10321	-0.07481	-0.04641	-0.01802	0.01038	0.038776	0.067172	0.095568	0.123965	0.152361	0.53233	10%	-2.69333	0.594159	1.484183	2.062363	2.295877	2.08637	1.546039	0.9009	0.352807	-0.00055	-0.16366	-2.04941	10.61005	4.7E-05
3	53.42	168.11	63.08794	74.17822	86.74057	100.6832	116.0122	132.7046	150.7046	169.9214	190.2273	211.4572	233.4098	0.154071	0.150314	0.145607	0.139226	0.132844	0.126463	0.120082	0.113701	0.10732	0.100939	0.094558	0.088177	1	6%	4.80285	5.145318	5.419362	5.544526	5.595282	5.5635	5.442793	5.228879	4.919875	4.516513	4.022248	58.48874	168.1099	-0.00012
4	12.6785	39.26	12.364	11.99335	11.61146	11.22855	10.84555	10.46336	10.08285	9.704834	9.330102	8.959396	8.593415	0.045293	0.055031	0.058563	0.060722	0.062881	0.06504	0.067199	0.069358	0.071517	0.073675	0.075834	0.077993	0.018897	2%	0.331172	0.433054	0.452443	0.453282	0.452393	0.449901	0.445931	0.440604	0.434044	0.42637	0.417699	21.84461	39.26001	5.8E-06
5	11.24	52.87	13.16	15.37	17.89	20.72743	23.90499	27.44408	31.36423	35.68253	40.41294	45.56567	51.14654	0.145897	0.143787	0.140861	0.136893	0.132924	0.128956	0.124988	0.12102	0.117052	0.113084	0.109115	0.105147	1	5%	0.994257	1.080689	1.160364	1.225119	1.284677	1.337677	1.382758	1.418579	1.44386	1.457413	1.45817	27.38643	52.87	-3.7E-06

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

Tom — Sun, 09 Feb 2020 21:03:08 GMT

How is it going to solve for anything if there is only one observation? Isn't it just an equation you want to calculate at that point?

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

nazmul — Sun, 09 Feb 2020 21:06:38 GMT

Dear Tom,

I am just solving a basic equation where you know all the values except one (In this case R). I simply do not know how to solve it in SAS. Could you please suggest how can I do it? If possible please suggest a specific code.

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

PGStats — Sun, 09 Feb 2020 22:30:14 GMT

It says that one of your equation variables doesn't exist in your data. Check for a possible typo. Also, shouldn't the last exponent for (1+R) be 12?

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

nazmul — Sun, 09 Feb 2020 22:37:53 GMT

Dear PGStats,

The only variable not available is R and I want to calculate the variable solving the equation. I checked the equation and match the variable names to the sample data I provided in this post. All variables other than R are present in the dataset.

Thank you for pointing the last exponent. However, the equation is correct.

If possible, could you please provide me the code and log file to check. I highly appreciate your help.

topic Solve an equation to get a parameter value for each observation in SAS Programming

Solve an equation to get a parameter value for each observation

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation

Re: Optimize by minimizing the value of an equation to get a parameter value for each observation