Excel Goal Seek in SAS

In Excel you have the function "Goal Seek". It is a nice function that allows you to "search" for a value for a input parameter to get your desired result.

Here is the program that does the same function in SAS Base. Here is the result:

/* -------------------------------------------------------------------- */

/* Excel Goal Seek */

/* - Fill macrovar target_result */

/* - Macro calc holds the calculation that you are performing */

/* - Macrovar counter controls how many time SAS should try */

/* - Macrovar wrong controls the rounding of difference between the */

/* calculated result and the target result */

/* -------------------------------------------------------------------- */

%LET target_result = 15237 ;

%LET counter = 1000 ;

%LET wrong = 0.0000001 ;

%MACRO calc(premium=, result=) ;

result = 0 ;

do i = 1 to 30 ;

result = result + (1 + 0.03) ** (1/i) * ((premium - 100)) ;

end ;

&result. = result ;

%MEND calc ;

DATA work.ds ;

wrong = 1 ;

premium = 100 ;

counter = 0 ;

delta = 1 ;

%calc(premium=premium, result=var1) ;

target_result = &target_result. ;

RETAIN result ;

OUTPUT ;

DO WHILE ( wrong > &wrong. AND counter < &counter. ) ;

premium = premium + delta ;

counter + 1 ;

%calc(premium=premium, result=var2) ;

premium = premium - delta ;

premium = (target_result - var1 + premium * ((var2- var1) / delta)) / ((var2 - var1) / delta) ;

%calc(premium=premium, result=var1) ;

wrong = ABS(target_result - var1) ;

OUTPUT ;

END ;

RUN ;