topic Re: Missing values incorrectly generated by finance('rate') function in SAS Programming

Missing values incorrectly generated by finance('rate') function

dmcwool — Mon, 11 Mar 2019 11:57:06 GMT

I have an issue with the finance('rate', , ,) function.

27         data _null_;
28           rate=FINANCE('rate',9,-162.45,1461.48);
29           put rate=;
30         run;

NOTE: Invalid argument to function FINANCE('rate',9,-162.45,1461.48) at line 28 column 8.
rate=.
rate=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to 
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 28:8   
NOTE: DATA statement used (Total process time):
      real time           0.36 seconds
      cpu time            0.04 seconds

When I attempt the same calculation in excel (=RATE(9,162.45, -1461.48)*12) , I get a value, so the calculation is valid (also I am attempting this calculation on thousands of rows, this issue only impacts a handful).

I have searched SAS support and found the following

http://support.sas.com/kb/55/525.html

suggesting this might be resolved by a hotfix Base SAS 9.3_M2

However current version here is 9.04.01M4P110916.

Any suggestions, or this better going via SAS administration on site here?

Re: Missing values incorrectly generated by finance('rate') function

andreas_lds — Mon, 11 Mar 2019 12:07:07 GMT

Talk to your sas-admin, using 9.5m5, i get the expected result:

26         data _null_;
27         rate=FINANCE('rate',9,-162.45,1461.48);
28         put rate=;
29         run;

rate=0.000077995
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

30         
31         %put &=SYSVLONG4;
SYSVLONG4=9.04.01M5P09132017

Re: Missing values incorrectly generated by finance('rate') function

dmcwool — Mon, 11 Mar 2019 13:10:43 GMT

Thanks - upgrade of the sas version is likely to be a long running battle, just on the off-chance, anyone got a code workaround for this?

Re: Missing values incorrectly generated by finance('rate') function

andreas_lds — Mon, 11 Mar 2019 17:37:37 GMT

The admins should - at least - check if a hotfix is available for the installed version.

Re: Missing values incorrectly generated by finance('rate') function

dmcwool — Tue, 12 Mar 2019 09:23:29 GMT

I can't find any details of a relevant hotfix that would resolve this, I'd rather go forearmed to sas admins, it's not usually an easy discussion (i.e. if I say, can you install hotfixes, then this doesn't solve the problem, I then have to go and ask them again so I'd rather just suggest something I'm confident will actually fix it).

Anyway, I found an alternative code solution in SAS - namely proc loan. Requires more coding as you can't pass a dataset to proc loan, so I need to pass each bit of data individually through some horrible macro code, then pick up the output each time and append it together. It's not a particularly good solution, but I can use it as an exception process....

  proc loan;
      fixed amount=1461.48 payment=162.45 life=9;
   run;

Re: Missing values incorrectly generated by finance('rate') function

andreas_lds — Tue, 12 Mar 2019 09:38:36 GMT

You don't need macro code, just call execute:

data have;
   input amountVar paymentVar lifeVar;
   datalines;
1461.48 162.45 9
3000 150 20
run;

data _null_;
   set have end=jobDone;

   if _n_ = 1 then do;
      call execute('proc loan noprint outsum=work.loans;');
   end;

   call execute(catx(' ', 'fixed amount=', amountVar, 'payment=', paymentVar, 'life=', lifeVar, ';'));

   if jobDone then do;
      call execute('run;');
   end;
run;

Re: Missing values incorrectly generated by finance('rate') function

dmcwool — Tue, 12 Mar 2019 09:53:58 GMT

Good point, thank you. Still not as neat as a single line sas function, however.