Tuesday 22 January 2013

NOTE: Macro Arrays, Straight From a Data Set

I love the SAS macro language, and I do a lot of work in it. It's not uncommon to want to have an array of macro variables with values taken from a data set. Here's how I most-often do it...

Of course, macro language doesn't explicitly support arrays of variables, but we can achieve something usable by placing values into macro variables whose names follow a numeric sequence, e.g. NAME1, NAME2, NAME3, etc.

This can be achieved from PROC SQL, as this log shows:

17 proc sql noprint;
18   select name,age into :name1-:name999, :age1-:age999
19     from sashelp.class;
20 quit;
21 %put SQLOBS=&sqlobs, NAME1=&name1, NAME2=&name2, AGE1=&age1, AGE2=&age2;

SQLOBS=19, NAME1=Alfred, NAME2=Alice, AGE1=14, AGE2=13

See how we need to code an arbitrarily large number as the top index (999 in this case), unless we figure it out beforehand; and see how SQLOBS tells us how many members of the array have been populated. This is an effective and simple means of loading a macro array from a data set.

To use a macro array you'll most likely want to loop across it (or down it, depending on your predilection!); and you'll need a %do loop to do that; and you'll need to be within a macro definition to do that. The following code shows how to list-off the array's values. Note how we've saved the size of the array into NAME0 to avoid the danger of SQLOBS being subsequently over-written.

%macro NoteColon;
  proc sql noprint;
    select name,age into :name1-:name999 ,:age1-:age999
      from sashelp.class;
  %put SQLOBS=&sqlobs, NAME1=&name1, NAME2=&name2, AGE1=&age1, AGE2=&age2;  

  %let NAME0=&sqlobs;

  %do i=1 %to &name0;
    %put I=&i, NAME&i=&&&name&i, AGE&i=&&&age&i;
%mend NoteColon;


This produces the following log:

SQLOBS=19, NAME1=Alfred, NAME2=Alice, AGE1=14, AGE2=13
I=1, NAME1=Alfred, AGE1=14
I=2, NAME2=Alice, AGE2=13
I=3, NAME3=Barbara, AGE3=13
I=18, NAME18=Thomas, AGE18=11
I=19, NAME19=William, AGE19=15

Macro arrays are a valuable tool, and it's always useful to find new ways to use them and/or to load them! If you haven't come across this particular use of PROC SQL's INTO expression then I hope you've found this post useful.