Dynamic Arrays and Finance Function

[mmyoung27]

I am seeking to create a number of variables (dynamically) and then input those variables into the Finance (XIRR) function.

I am starting with a table with 200K rows full of static origination information for consumer loans with a number of early, irregular cash flows (i.e. loan amount, term, scheduled payment, etc.).

Specifically, let's assume I have variables 1 through 5 and dates 1 - 5 (v1, v2,v3,v4,v5 and d1,d2,d3,d4,d5) already established for my output table.  Each row in my output table will then need to dynamically add additional "v" and "d" variables based on the value of another existing variable (loan term).  For example, if Loan Term is 36, then I need to dynamically create v6 - v41 and d6 - d41.  My next row of data might have a Loan Term of 24, so I would then only need to create variables v6 - v30 and d6-d30.

The final step in the process for each row would be to then evaluate the variables and dates in the finance function.  Using the Loan Term of 36 as an example, I would need:  finance('xirr',v1,v2,v3,v4,v5,v6,.....v41,d1,d2,d3,d4,d5,d6,....d41).

[ballardw]

SAS data sets aren't going to have different numbers of variables dependent on rows. The number of variables with assigned values is another thing, but the data set will have the same number of variables on each row.

What is the variable that you have that tells you want the additional values? Do you know what the largest value of that variable is?

It seems like it may be easier to first create additional rows and then transpose the data into a structure you need.

It might help to provide a little bit of example data, perhaps with only 3 of your existing v1-v6 and d1-d6 varaibles, a value loan term that is not very large but varies from 4 to 6 or so. You really need to show some example of what you are calling "dates".

Also, which FINANCE might be good idea as well.

And then some idea of what the actual output should look like.

It may also be that you are looking for Proc Loan if you have SAS/ETS licensed and are looking to create amortization tables.

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_loan_sect001.h...

[mmyoung27]

Thank you for the questions - I should have been more clear in my original ask.
I have a table that starts with:
[cid:image003.png@01D46147.D3A4C430]
My end goal is to use this information to dynamically create variables that will feed into an output table that only has two columns:
Acct_ID Yield
Where Yield = FINANCE('XIRR',v1,v2,v3,v4....vx,d1,d2,d3,d4....dx) where x is a function of the loan term field.
In the table above, I have 4 values and 4 dates already established. What I need to do is, based on the value in the Loan Term field, dynamically create an additional 23 values (all the same as the first_pmt_amt) and 23 dates (incrementing one month at a time from the first_pmt_dt field). Ideally if I can put those values into an array and then reference the array in my FINANCE function that would be my first choice. I would output the result of the FINANCE function to a new table with the Acct_ID and Yield columns.

[Tom]

Not sure what "[cid:image003.png@01D46147.D3A4C430]" is supposed to be. Perhaps you tried to paste in a photograph of your data? Please post data as text. We cannot program from pictures.

It might just be easier to pass the function all of the possible variables, but just set the extra one's to values that will cause it to generate the answer you want.  That might be missing values.  Or it might just be zeros or other constants.

As others have said get it to work with 2 or 3 variables and then it can easily be extended to as many as you need.

So post sample input and output data for a few example observations with just a enough variables to demonstrate the idea.

[Tom · Accepted solution]

Looks like this question has been asked before.

https://communities.sas.com/t5/SAS-Programming/Passing-list-of-arguments-to-Finance-XIRR-function/td...

Looking at that question you should probably just set the extra amounts to zero and the extra dates to match the last date.

array amt (50) ;
array dt (50);

do i=loan_term +1 to dim(amt);
  amt(i)=0;
  dt(i)=dt(loan_term);
end;

result = finance('xirr',of amt(*), of dt(*));