topic Re: Prefix for all variables with macro in SAS Programming

Prefix for all variables with macro

jamesf — Wed, 31 Jul 2019 07:26:05 GMT

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.

Re: Prefix for all variables with macro

Kurt_Bremser — Wed, 31 Jul 2019 07:45:54 GMT

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.

Re: Prefix for all variables with macro

ballardw — Wed, 31 Jul 2019 16:57:38 GMT

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.

Re: Prefix for all variables with macro

jamesf — Wed, 31 Jul 2019 23:32:20 GMT

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