Iterative Excel Solver in SAS changing variable

[georgel]

Dear SAS users, 

I have this in excel and I would like to have this 

CUSIP	DATE	MarketCap	EQ_RET	Assets_value	EQ_RET_StdDev	DPT_drift	DPT	DPT_i	r	d1	d2	FV	Mispricing
65540B10	6/28/2013	819.63525		822.6675019	0.118966659	-0.933719062	3.156	3.156	0.04	47.12774656	47.0087799	819.6352504	4.03E-07
65540B10	7/31/2013	1002.150565	0.222678704	1004.56934	0.118966659	-0.933719062	3.156	2.517209613	0.04	50.7186562	50.59968954	1002.150831	0.000266
65540B10	8/30/2013	1055.065616	0.052801498	1056.994619	0.118966659	-0.933719062	3.156	2.007713637	0.04	53.05215971	52.93319305	1055.065629	1.27E-05
65540B10	9/30/2013	990.3660341	-0.061322804	991.9043867	0.118966659	-0.933719062	3.156	1.601342226	0.04	54.42120528	54.30223862	990.365834	-0.0002
65540B10	10/31/2013	1015.139801	0.025014758	1016.366648	0.118966659	-0.933719062	3.156	1.277222448	0.04	56.52987526	56.4109086	1015.139506	-0.0003

I need to minimize the difference between the fair value (FV) and the MarketCap  by changing the Assets_value initially set as MarketCap

I use the following code but does not work: 

proc fcmp outlib=work.fcmp.stuff;
function FV(Assets_value, DPT_i);
return (Assets_value*cdf('NORMAL',(log(Assets_value/DPT_i)+(0.119**2/2+0.04)*1)/(0.119*sqrt(1)))-DPT_i*exp(-0.04*1)*cdf('NORMAL',(log(Assets_value/DPT_i)+(0.119**2/2+r)*1)/(0.119*sqrt(1))-0.119*sqrt(1)));
endsub;
function Assets_value(DPT_i, MarketCap);
return ( solve("FV", {0.1}, MarketCap, .,DPT_i) );
endsub;
run;

options cmplib=(work.fcmp);

data have;
set t2;
assets_value = Assets_value(DPT_i, MarketCap);
* format fV assets_value percent8.2;
fV = FV(assets_value, DPT_i);
run;

proc print data=have; run;

Also I need to use the variable names not the constants.

Thanks in advance

[ballardw]

If you want to use a variable in a function you have to make it available to the function as a parameter. Then use that parameter in the function calculations. Parameters in the function definition are in effect place holders and then you place the name of the variable in the appropriate function call in the proper position of the function call. Or pass an array of values to use.

Functions will work on one observation. I have a suspicion that you think you are working with all of the observations for a variable.

I don't speak Excel Solver so the above is a guess as to one issue.

If "solve("FV" ) is supposed to relate to Future Value you might look at the SAS FINANCE function with the FV calculation.

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[georgel]

Dear Ballard,

Thank you very much for your reply. No errors in logs. I use the same process as  of the link below:

https://communities.sas.com/t5/SAS-Programming/Iterative-Excel-Solver-in-SAS-By-changing-variable-ce...