Help using Base SAS procedures

Apply IRR function to a table

Reply
N/A
Posts: 0

Apply IRR function to a table

Hi, I'm trying to use IRR function the first time. My input dataset consists of two columns, month/year and cashflow, and up to 360 rows. It would be ideal IRR function can be applied to the dataset directly, but I don't see how that can be done. so I was thinking that I can build a string and use the string in the IRR function. There are two problems with this approach, one, I'm not sure how to use string in a function, i.e. - string="-1000,300,300,300,300" rate=irr(1,string) - doesn't seem to work. two, I believe string is limited to 256 chracters, and my input, with up to 360 terms of 10 digits each, can result in a length of 3600.

I googled and can't find any example of applying IRR function on a large dataset. any sample code or ideas would be greatly appreciated.
Super Contributor
Super Contributor
Posts: 3,174

Re: Apply IRR function to a table

Suggest using PROC TRANSPOSE to convert your vertical data to more appropriate horizontal, based on some defined key variable set.

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: Apply IRR function to a table

Scott, thanks for the input. once the data is transposed, what's the syntax to apply IRR function to the transposed dataset? I tried following, but seems IRR can't be applied to a dataset even it's horizontal.

temp dataset look like this

| -100 | 30 | 30 | 30| 30 |

data out1;
set temp;
rate=irr(1,temp);
run;

error message:

The IRR function call does not have enough arguments.
Super Contributor
Super Contributor
Posts: 3,174

Re: Apply IRR function to a table

The SAS DOC shows a much different list of arguments for the IRR function. So, with the transposed file, you need to list the individual SAS NUMERIC variables passed to the IRR function. Not sure where you got that a SAS CHARACTER variable would even be remotely suitable.

So, my suggestion would be to generate a SAS macro variable "set" listing the "1" to "n" variables for your DATA step assignment statement with the IRR function.

A SAS-maintained view SASHELP.VCOLUMN or DICTIONARY.COLUMNS can be used with either a DATA step or PROC SQL to generate the variable list as macro variables and use a SAS MACRO to build the assignment statement.

Check the SAS forums archives, or use the SAS support http://support.sas.com/ website's SEARCH facility (or Google advanced search using arguments along with the parameter site:sas.com), to find related SAS technical/conference papers (example link below for a start) on using VCOLUMN and COLUMNS along with SAS code for your assignment statement.


Scott Barry
SBBWorks, Inc.

Creating Data Driven Programs with the Macro Language
Brian Varney, Trilogy Consulting, Kalamazoo, Michigan
http://www2.sas.com/proceedings/sugi24/Posters/p254-24.pdf
Ask a Question
Discussion stats
  • 3 replies
  • 396 views
  • 0 likes
  • 2 in conversation