I need to add a prefix to all variables in several data sets, with each data set using a different prefix. I have followed examples for doing this one data set at a time. The code below works exactly as intended.
data have;
set sashelp.demographics;
run;
proc sql noprint;
select catx('=',name,catt('test_',name))
into :rename_list
separated by ' '
from sashelp.vcolumn
where libname = 'WORK' and memname = 'HAVE';
quit;
proc datasets library= work nolist;
modify have;
rename &rename_list;
quit;
proc contents data=have; run;
I am looking to generalize this code with a macro, but the rename_list2 isnt being populated (see the put statement), and I'm not sure why. I have given the log output below the code.
%macro name_prefix(library, dataset, prefix);
proc sql noprint;
select catx('=',name,catt(&prefix,name))
into :rename_list2
separated by ' '
from sashelp.vcolumn
where libname = '&library' and memname = '&dataset';
quit;
%put &rename_list2;
proc datasets library= &library nolist;
modify &dataset;
rename &rename_list2;
quit;
%mend name_prefix;
data have2;
set sashelp.demographics;
run;
%name_prefix(WORK,HAVE2,'test');
LOG MESSAGE
rename_list2
WARNING: Apparent symbolic reference RENAME_LIST2 not resolved.
NOTE: Line generated by the invoked macro "NAME_PREFIX".
26 rename &rename_list2; quit;
_
22
76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
This is your main culprit:
where libname = '&library' and memname = '&dataset';
Macro variables will not be resolved when enclosed in single quotes. Use double quotes instead.
I also suggest another modification by moving the quotes away from the macro call into the macro itself:
%macro name_prefix(library, dataset, prefix);
proc sql noprint;
select catx('=',name,catt("&prefix",name))
into :rename_list2
separated by ' '
from sashelp.vcolumn
where libname = upcase("&library") and memname = upcase("&dataset");
quit;
%put &rename_list2;
proc datasets library= &library nolist;
modify &dataset;
rename &rename_list2;
quit;
%mend name_prefix;
%name_prefix(WORK,HAVE2,test);
And I hardened the code against lowercase library and dataset names.
This is your main culprit:
where libname = '&library' and memname = '&dataset';
Macro variables will not be resolved when enclosed in single quotes. Use double quotes instead.
I also suggest another modification by moving the quotes away from the macro call into the macro itself:
%macro name_prefix(library, dataset, prefix);
proc sql noprint;
select catx('=',name,catt("&prefix",name))
into :rename_list2
separated by ' '
from sashelp.vcolumn
where libname = upcase("&library") and memname = upcase("&dataset");
quit;
%put &rename_list2;
proc datasets library= &library nolist;
modify &dataset;
rename &rename_list2;
quit;
%mend name_prefix;
%name_prefix(WORK,HAVE2,test);
And I hardened the code against lowercase library and dataset names.
Thanks Kurt!
What a little change to make all the difference. The uppercase is also a good tip.
Ballard, thanks for the reminder. The test_ is just an example, and I have checked that none of my datasets have large variable names. I'll work in some checks for the full product, I was just trying to give the simplest example.
Cheers
With an automated process like this you really need to consider the lengths of your variables when combined with the prefix.
Since you are adding 4 letters with a prefix of "Test" then any variable that may have 29 or more characters will create a name that exceeds SAS name rules and will cause an error in the rename.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.