How do I array match 1:1 with more than one variable

[jfer]

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

[Tom]

Why are you using FCMP compiled functions (subroutines)?

Why not just use normal SAS code?

[ballardw]

I actually find the idea of a macro variable buried in a function definition to be somewhat scary and indicative of a required rethink. Functions should have all values passed in as parameters.

I am also not quit sure what those functions are expected to do.

There are a number of functions SAS supplies for Financial operations plus Proc LOAN (and others).

Your descriptions doesn't actually describe what you are calculating, or from which dataset.

1) The array will put the smallest interest rate (apr), starting principal (new_prin), and payment count (c_pmt) first then calculate.  For example:

just cuts off with out describing the needed calculation.

[Tom]

Can you explain what the purpose of the code is?

One thing that looks strange is that you are selecting only one value into your macro variables but you included the DISTINCT keyword in the select statement?  Why add the distinct when you are only taking one value? Did you think that it was going to select multiple values?

[Astounding]

Also no fcmp experience here. Since you commented on the order of selected values, note that adding DISTINCT to SQL changes the order. It forces SQL to store the selected values in lowest to highest order, to facilitate checking for "distinctness".