PROC SQL: :INTO different macro variables based on where logic

[SAStronomical]

My goal is to create 20 lists containing the names that have a specified value in their name, e.g.

dog_list for field names named, e.g.,  dog_breed dog_age dog_weight

vet_list for field names named, e.g., vet_city vet_name

and so on..

So I tried this:

%macro test (val) ;

proc sql ;
select name into :&val._list separated by " " 
from dictionary.columns where libname = "WORK" and memname = "TEST" and 
name contains "&val." ;
quit ;

%mend ; 

%test(dog) ;

%put &dog_list ;

And I got the warning: "Apparent symbolic reference DOG_LIST not resolved."

Is the issue here that &val._list resolves after the into statement creates &val._list as a macro variable? I'm really not sure how to resolve this.

I could just write this out in 20 select statements, but I feel like there's some big concept I'm missing.

[Patrick · Accepted solution]

A macro variable defined within a macro gets per default a scope of local. You need to use a %global statement to also use this macro variable outside of the macro.

data test;
  array createVars {*} dog_breed dog_age dog_weight  vet_city vet_name;
  stop;
run;

%macro test (val);
  %global &val._list;
  proc sql;
    select name into :&val._list separated by " " 
     from dictionary.columns where libname = "WORK" and memname = "TEST" and 
     upcase(name) contains "%upcase(&val.)";
  quit;
%mend;

%test(dog);
%put &dog_list;

You also need to upcase the names for the where clause to filter the data case insensitive.