turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Use SAS number range list in an expression

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-27-2017 01:42 PM

Hi,

I am looking to dynamically reference a variable name within an expression.

Examples below:

DATA out; set have; array values {&num_leases.}; array dates {&num_leases.}; array combined {2* &num_leases.} Var1-Var&num_leases.; RUN

In the above, I would like to create arrays with a length stored in the variable num_leases. For the "combined" array, I would like to then create variables names Var1 Var2... VarN, where N is the number of leases stored in num_leases.

I then go on to store values in these arrays, and then I would like to find the last non-missing (i.e. ne .) entry in the "combined" array and use all non-missing elements in an expression, as below:

do i = 1 to &num_leases.; if combined{i} = . then leave; end; IRR = finance('xirr',combined{1}:combined{i}); output;

So, in the above, I am looping through all elements in the "combined" array, and, when I find the first missing element, I am storing where in the array is it (i.e. element i). I then want to pass the non-missing elements in this array to the finance XIRR function to calculate an IRR.

Would anyone be able to help me understand how to dynamically reference variable names, and then pass them to an expression, as per the above?

Many thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

09-27-2017 02:05 PM

Can you post (an extract) of dataset HAVE as data step so simplify creating a solution?

You need %eval-macro-function in your array definition:

`array combined {%eval(2* &num_leases.)} ...`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to error_prone

09-27-2017 02:25 PM

Ok, I haven't done macro coding, but I'll update the line to look like this. Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

09-27-2017 02:09 PM

Just to get the ball rolling ...

So far you have a lot of the syntax correct. But I'm not sure about the SAS code. Here are some questions.

Do the arrays you mention represent variables that already exist, or variables that you want to create?

Why 2 * &n_leases for one of the arrays? Actually, the correct syntax for that would be to have macro language perform the math and insert the result as the number of array elements:

{ %eval(2 * &n_leases.) }

If you want to know the number of nonmissing elements, you don't need arrays. You can code:

i = n(of var1 - var&n_leases.) ;

If you name an array and provide the number of elements, those elements can be named automatically. For example, these statements would provide identical results:

array var {&n_leases} var1 - var&n_leases.;

array var {&n_leases} ;

When the array elements are not named, they will automatically be the name of the array with a numeric suffix.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

09-27-2017 02:24 PM

Ah ok, that makes sense, I'll get rid of the superfluous code. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

09-27-2017 02:15 PM

You have a fundamental logic problem with this line:

array combined {2* &num_leases.} Var1-Var&num_leases.;

You apparently want to create 2 time &num_leases variable but only provide &num_leases variables. If you provide a number of elements in {} then the number of names in a provided list of variables has to match.

if you use

array var {%eval(2*&num_leases)};

That will create variables named Var1-Var2*num_leases

So either provide enough variable names or use a different name for the array to create the matching number of variables.

But I don't think you need that combined array and the way you were attempting to use it was incorrect.

See this for an example of useing the SIRR with arrays.

data _null_; v1=−10000; d1=mdy(1, 1, 2008); v2=2750; d2=mdy(3, 1, 2008); v3=4250; d3=mdy(10, 30, 2008); v4=3250; d4=mdy(2, 15, 2009); v5=2750; d5=mdy(4, 1, 2009); array values v1-v5; array dates d1-d5; r=finance('xirr', of values(*), of dates(*), 0.1); put r=; run;

So if you can successfully create and populate your values and dates arrays that all you need.

If you only want to use a subset of the elements in the values and dates arrays then you may wish to copy them into other arrays with the unwanted elements set to missing.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

09-27-2017 02:26 PM

Thanks for the response. Understand everything until the final lines. How would I go about creating a new array from the existing ones, and setting the unwanted values to missing? And would the IRR function work with missing values? Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

09-27-2017 03:28 PM

MikeFranz wrote:

Thanks for the response. Understand everything until the final lines. How would I go about creating a new array from the existing ones, and setting the unwanted values to missing? And would the IRR function work with missing values? Thanks!

Actually it looks like we need to set the unwanted values to 0.

data _null_; v1=−10000; d1=mdy(1, 1, 2008); v2=2750; d2=mdy(3, 1, 2008); v3=4250; d3=mdy(10, 30, 2008); v4=3250; d4=mdy(2, 15, 2009); v5=2750; d5=mdy(4, 1, 2009); array values v1-v5; array dates d1-d5; array vs {5}; array ds {5}; /* to copy the first 3 of each array into a subset*/ do i=1 to 3; vs[i] = values[i]; ds[i] = dates[i]; end; do i=4 to dim(values); vs[i] = 0; ds[i] = 0; end; r=finance('xirr', of values(*), of dates(*), 0.1); r2=finance('xirr',of vs(*),of ds(*), 0.1); /* the next shows that the zeroes in the vs,ds array yield same as the specific variables */ r3=finance('xirr',vs1,vs2,vs3,ds1,ds2,ds3, 0.1); put r= r2= r3=; run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

09-28-2017 05:49 AM

Mike,

Looks like what you want to do is something like this:

Data out; set have; array dates (*) date1-date&Num_leases; array values(*) value1-value&Num_leases; /* code to populate arrays here */ IRR=finance('XIRR',<dynamic portion of values>,<dynamic portion of dates>); run;

Unfortunately, there is no way you can refer to a dynamic number of values in a function call like that. One way out of that could be to code a select statement:

select (n(of values(*)); /* select on non-missing values */ when(0); /* not sure what to do here... */ when(1) IRR=finance('XIRR',value1,date1); when(2) IRR=finance('XIRR',of value1-value2,of date1-date2); when(3) IRR=finance('XIRR',of value1-value3,of date1-date3);

Which can, of course, be coded as a macro. But I think the easy way out is to replace the missing values with zeros:

do i=1 to dim(dates); if missing(values(i)) then do;

values(i)=0;

dates(i)=0; /* does not matter what the date is, but it cannot be missing */

end;

end;

The FINANCE function does not accept missing values, but any number of zero values does not change the result, so you can then just do

IRR=finance('XIRR',of values(*),of dates(*));