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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.