topic Re: Use SAS number range list in an expression in SAS Programming

Use SAS number range list in an expression

MikeFranz — Wed, 27 Sep 2017 17:42:32 GMT

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!

Re: Use SAS number range list in an expression

error_prone — Wed, 27 Sep 2017 18:05:25 GMT

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.)} ...

Re: Use SAS number range list in an expression

Astounding — Wed, 27 Sep 2017 18:09:16 GMT

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.

Re: Use SAS number range list in an expression

ballardw — Wed, 27 Sep 2017 18:15:37 GMT

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.

Re: Use SAS number range list in an expression

MikeFranz — Wed, 27 Sep 2017 18:24:44 GMT

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

Re: Use SAS number range list in an expression

MikeFranz — Wed, 27 Sep 2017 18:25:05 GMT

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

Re: Use SAS number range list in an expression

MikeFranz — Wed, 27 Sep 2017 18:26:28 GMT

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!

Re: Use SAS number range list in an expression

ballardw — Wed, 27 Sep 2017 19:28:14 GMT

@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;

Re: Use SAS number range list in an expression

s_lassen — Thu, 28 Sep 2017 09:49:37 GMT

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(*));