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).
Looks like this question has been asked before.
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(*));
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.
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.
Looks like this question has been asked before.
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(*));
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.