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=13See 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;
quit;
%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;
%end;
%mend NoteColon;
%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
[snip]
I=18, NAME18=Thomas, AGE18=11
I=19, NAME19=William, AGE19=15Macro 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.
