Hello Sas-xperts,
I have code that will take values from arrays and calculate principal balance and interest. This works fine with only one account. Two problems arise with more than one account:
1) The array will put the smallest interest rate (apr), starting principal (new_prin), and payment count (c_pmt) first then calculate. For example:
data work.schedule;
input debt_key new_prin apr c_pmt;
cards;
71932 11239.07 0.18990 6
72140 8102.56 0.22950 5
;;;;
run;
data work.payments;
input debt_key pmt_date payment days_btwn_pmts dk_seq;
cards;
71932 07/31/2018 428.86 0.18990 31 1
71932 09/06/2018 492.5 0.18990 37 1
71932 10/10/2018 491.33 0.18990 34 1
71932 11/09/2018 490.22 0.18990 30 1
71932 11/30/2018 428.86 0.18990 21 1
71932 12/06/2018 0 0.18990 6 1
72140 10/29/2018 332.97 0.22950 38 2
72140 12/03/2018 333 0.22950 35 2
72140 12/31/2018 0 0.22950 7 2
72140 12/31/2018 0 0.22950 0 2
72140 12/31/2018 0 0.22950 7 2
;;;;
run;<code></code>
(work.payments needed for additional work)
...but after placing in macro variables using the following code:
proc sql ;
select distinct apr
into :rate
from work.schedule;
select distinct New_prin
into :Prin0
from work.schedule;
select distinct c_pmts
into :c_pmt
from work.schedule;
quit;
The output is as follows:
Rate
.018990
0.22950
Prin
8102.56
11239.07
pmt_ct
5
6
...then, when using in the following code:
proc sort data=payments out=new;
by debt_key pmt_date;
run;
proc fcmp outlib= jfer.functions.funcs;
function pmts( item );
array pmts[&c_pmt,1] / nosymbols;
rc = read_array('new', pmts, 'payment');
put pmts[item]=;
p = pmts[item];
return( p );
endsub;
function days( item );
array days[&c_pmt,1] / nosymbols;
rc = read_array('new', days, 'days_btwn_pmts');
put days[item]=;
p = days[item];
return( p );
endsub;
run;
options cmplib=jfer.functions;
data test;
set new;
array loan_bal{0:&c_pmt};
array interest{0:&c_pmt};
loan_bal{0}=&Prin0; /* initiate debt */
interest{0}=apr;
do i=1 to &c_pmt;
payments = pmts(i);
daysbetween = days(i);
loan_bal{i}=loan_bal{i-1}-payments+(loan_bal{i-1}*(&rate/365)*daysbetween);
interest{i}=(loan_bal{i-1}*(&rate/365)*daysbetween);
drop i payments daysbetween payment days_btwn_pmts pmt_date apr;
end;
run;
I get the following output:
Here is where the second problem presents itself. Not only are the values mismatched to the debt_keys, but the calculations continue on with only the first set numbers in the array.
A few notes:
-The Proc fcmp is borrowed - I know close to nothing about it.
-The dk_seq is a variable created while attempting to fix the problem.
-The code has been adjusted and not run in it's entirety. The first blocks of code with 'cards' are only to show what data I am working with. Please let me know if I am missing any information you need to answer my question.
-This code may be over-complicated. Please let me know if this is the case.
This feels like it has a simple solution but can't seem to wrap my brain around it. Lack of sleep trying to noodle this out isn't helping. Any help would be appreciated.
SAS EG Version: 7.15 HF7 (7.100.5.6177) (64-bit)
Thank you!
JFer
... View more