BookmarkSubscribeRSS Feed
MikeFranz
Quartz | Level 8

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!

 

 

 

 

 

 

 

 

 

8 REPLIES 8
error_prone
Barite | Level 11

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

 

MikeFranz
Quartz | Level 8
Ok, I haven't done macro coding, but I'll update the line to look like this. Thanks 🙂
Astounding
PROC Star

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.

MikeFranz
Quartz | Level 8
Ah ok, that makes sense, I'll get rid of the superfluous code. Thanks!
ballardw
Super User

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.

MikeFranz
Quartz | Level 8
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!
ballardw
Super User

@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;
s_lassen
Meteorite | Level 14

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

  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1360 views
  • 0 likes
  • 5 in conversation