topic Re: Using REPEAT function to calculate IRR in SAS Programming

Using REPEAT function to calculate IRR

jitb — Wed, 25 Mar 2026 20:36:38 GMT

Hi,

I have a dataset of loans with variables loan or financed_amount, payment_amount (per month), and term of the loan in months. I would like to calculate the internal rate of return (IRR) as:

IRR = irr(1,-financed_amount, of %sysfunc(repeat(%str(payment_amount ),term-1))).

However, this is giving me an error

ERROR: Argument 2 to function REPEAT referenced by the %SYSFUNC or %QSYSFUNC macro function is

not a number.

If I substitute 60 for (term-1) in the equation, it works.

Any help would be much appreciated. Thanks.

Re: Using REPEAT function to calculate IRR

PaigeMiller — Wed, 25 Mar 2026 21:20:10 GMT

It would be extremely helpful if you showed us the ENTIRE log for this data step (not a small portion of the log).

Also, as far as I can tell, there is no need for %sysfunc here, yet you chose to use it even though no macro variables are present. As far as I can tell, there is no need for %str here, yet you chose to use it even though no macro variables are present. If payment_amount and term are data set variables, %sysfunc and %str cannot access the values of a data set variable. Therefore, %sysfunc sees text as the second argument of the REPEAT function. The text is

term-1

which is not a number, and not a reference to a data step variable, hence the error.

So if you remove %sysfunc and %str and their associated parentheses, this might work.

Re: Using REPEAT function to calculate IRR

quickbluefish — Wed, 25 Mar 2026 22:43:48 GMT

You can do this:

%let trm=60;
%let pmt=4000;
%let fin=18000;

%let IRR = %sysfunc(irr(1,-&fin, %sysfunc(repeat(&pmt, %eval(&trm-1)))));
%put &=IRR;

Re: Using REPEAT function to calculate IRR

Kurt_Bremser — Wed, 25 Mar 2026 22:45:57 GMT

The macro code (%SYSFUNC) is resolved before the DATA step is even compiled, much less executed. Therefore you cannot use DATA step variable values there.

Please describe in detail what you want to achieve, including the data that shall drive the code.

Re: Using REPEAT function to calculate IRR

jitb — Wed, 25 Mar 2026 23:47:36 GMT

Thank you, Paige. I will try without the %sysfunc and %str.

Re: Using REPEAT function to calculate IRR

jitb — Wed, 25 Mar 2026 23:53:01 GMT

Hi Kurt,

The dataset has a row for each loan given to a customer. Each row has the monthly payment to be made by the customer and is in the variable Payment_Amount. All Payment_Amount values are identical. The loan amount given to the customer is in Financed_Amount. The number of payments to be made (monthly) is in the variable term. I would like to calculate the IRR based on these variables in a data step for each row.

Re: Using REPEAT function to calculate IRR

jitb — Wed, 25 Mar 2026 23:54:17 GMT

Thank you for this code. However, I need to calculate in a data step as variable values are different for each row.

Re: Using REPEAT function to calculate IRR

quickbluefish — Thu, 26 Mar 2026 01:57:55 GMT

* create some test data ;
data test;
infile cards dsd truncover firstobs=1 dlm=',';
length finamt pmtamt term 8;
input finamt pmtamt term;
cards;
18000,4000,60
12000,7500,60
32000,11000,72
;
run;

proc sql noprint; 
select max(term) into :maxterm trimmed from test; 
quit;

data irr;
set test;
array T {&maxterm} _temporary_;
call missing(of T[*]);
do i=1 to (term-1);
	T[i]=pmtamt;
end;
irr=irr(1, -finamt, of T[*]);
drop i;
run;

Re: Using REPEAT function to calculate IRR

Tom — Thu, 26 Mar 2026 02:08:59 GMT

The REPEAT() function is for making a long string by repeating shorter strings N+1 times.

Example:

 87         data test;
 88           s='AB ';
 89           l=repeat(s,2);
 90           put (s l) (=:$quote./);
 91         run;
 
 s="AB"
 l="AB AB AB"

That is not going to be very useful for working with NUMBERS.

Now it might be useful when used in macro code to generate CODE. But then it needs to generate the COMMAs also.

With actual data it would probably be better to put the values into an ARRAY and then pass the ARRAY of values to the IRR() function.

It is not clear what data you have from the description. Do you have one observation per payment per load ID? If so then perhaps something like this:

data want;
  do i=1 by 1 until(last.id);
    set have end=eof;
    by id;
    pmts[i] = payment_amount ;
  end;
  irr = irr(12,-financed_amount,of pmts[*]);
  drop payment_amount i pmts: ;
run;

So for each ID you read in ALL of the PAYMENT_AMOUNT values and put them into the ARRAY. (Just make the size of the array larger than the maximum number of payments per loan.) The DO loop will collapse all of the observations per loan into one observation so that when it ends you have all of the payment amounts in the array. Then just pass the whole array to IRR() and it will ignore the missing values are the end of the array.

Since you said they are monthly payments use 12 as the frequency value to pass to IRR().

Now if instead you just have one observation per loan ID then instead use a different DO loop.

data want;
  set have end=eof;
  by id;
  array pmts[360] ;
  do i=1 to TERM ;
    pmts[i] = payment_amount ;
  end;
  irr = irr(12,-financed_amount,of pmts[*]);
  drop i pmts: ;
run;

Again just make the array size larger than the maximum value or the TERM variable across all of the loans in the dataset.

Re: Using REPEAT function to calculate IRR

Ksharp — Thu, 26 Mar 2026 07:28:38 GMT

That would be very helpful if you could post some real/dummy data to explain your issue.

Here is an example for solving your issue.

data have;
input financed_amount payment_amount term;
cards;
400 100 5
400 200 3
400 300 2
;

filename x temp;
data _null_;
 set have end=last;
 file x;
 length repeat $ 2000;
 do i=1 to term;
  repeat=catx(',',repeat,payment_amount);
 end;
 if _n_=1 then put 'data want;';
 put 'financed_amount=' financed_amount ';payment_amount=' payment_amount ';term=' term ';irr=irr(1,-' financed_amount ',' repeat ');output;';
 if last then put 'run;';
run;

%include x/source;

Re: Using REPEAT function to calculate IRR

PaigeMiller — Thu, 26 Mar 2026 08:57:09 GMT

So, @jitb , I would like to briefly summarize the responses so far. In order for you to have the best chance of getting good replies in the future, please keep these things in mind:

When you are having problems, we need a clear explanation of what you are trying to do. Do not assume that because you posted code that doesn't work, we know what you want.
We also need to know you think the correct answer is, or at least the correct thing for the code to be doing.
We need a portion of your SAS data set, or we need made up data as long as it illustrates the problem. Please follow these instructions and examples to provide the data.
If there are errors in the log, please show us the LOG (start to finish) for the DATA step or PROC that you are having errors in. Do not show us tiny portions of the log.

Re: Using REPEAT function to calculate IRR

jitb — Thu, 26 Mar 2026 13:06:25 GMT

Thank you, Tom for your solution. The second code is the appropriate one for my data as there is just one observation per loan ID. Since you used 12 as the frequency, I am assuming the IRR is an annual rate? Am I misunderstanding this? Thanks again!

Jit

Re: Using REPEAT function to calculate IRR

jitb — Thu, 26 Mar 2026 13:08:08 GMT

This did not work as the REPEAT function seems to be basically for characters and not numbers. I appreciate your notes on describing the problem more thoroughly.

Jit

Re: Using REPEAT function to calculate IRR

jitb — Thu, 26 Mar 2026 13:09:44 GMT

Thank you for the code. This would have worked if I had multiple observations per loan ID. However, I just have one obs per ID.

Jit

Re: Using REPEAT function to calculate IRR

jitb — Thu, 26 Mar 2026 13:19:47 GMT

Hi Quickbluefish,

Actually this code will work as well. Thank you!

Jit

Re: Using REPEAT function to calculate IRR

Tom — Thu, 26 Mar 2026 14:12:14 GMT

@jitb wrote:

Thank you, Tom for your solution. The second code is the appropriate one for my data as there is just one observation per loan ID. Since you used 12 as the frequency, I am assuming the IRR is an annual rate? Am I misunderstanding this? Thanks again!

Jit

Yes. If you use 1 as the frequency and the payments are monthly then that is the monthly IRR.

Example:

 73         data _null_;
 74           array pmts[24] _temporary_ (24*100);
 75           * Calculate monthly IRR ;
 76           irr1 = irr(1,-800,of pmts[*]);
 77           * Calculate annual IRR ;
 78           irr12 = irr(12,-800,of pmts[*]);
 79           * Convert annual to monthly;
 80           irr1_2  = 100* ( (1 + irr12/100)**(1/12) -1 )  ;
 81           * Convert monthly to annual ;
 82           irr12_2 = 100* ( (1 + irr1/100)**12 -1 )  ;
 83           put (irr:) (=/);
 84         run;
 
 irr1=11.603264268
 irr12=273.35568521
 irr1_2=11.603264268
 irr12_2=273.35568521

Re: Using REPEAT function to calculate IRR

jitb — Thu, 26 Mar 2026 17:43:27 GMT

Thank you, Tom. This illustrates it very well.

Jit