topic Re: Renaming multiple variables with special characters using macro in SAS Programming

Renaming multiple variables with special characters using macro

betabr2005 — Mon, 05 Mar 2018 17:21:37 GMT

Hi all,

I am a novice to SAS programming, but I am trying to rename a large amount of variables existing in multiple files using macro. Following is my current code. I have put the old name of variables in a list variable num_vars and newname is also a list containing the new names. The old list of variables contain names with different special characters (such as #, ', ", etc). I have gone through some posts here but they did not answered my question.

In the following code I have right now, I am getting an issue whenever SAS reads the statement: %qscan(&vars,&i) = %qscan(&newname,&i," ");

I used qscan() in an attempt to contain the special characters, but it didn't work well. Any suggestions on how I can fix the below code would be greatly appreciated.

%macro rename(LIB,DSN,newname);
proc sql noprint;
select nliteral(name) into:vars1 -
from dictionary.columns
where libname=%upcase("&LIB") and memname=%upcase("&DSN")
order by VARNUM;
%let num_vars=&sqlobs;
run;

proc datasets library = &LIB;
modify &DSN;
rename
%do i = 1 %to &num_vars.;
%qscan(&vars,&i) = %qscan(&newname,&i," ");
%end;
run;
quit;

%mend rename;

Thanks in advance.

Re: Renaming multiple variables with special characters using macro

Reeza — Mon, 05 Mar 2018 17:23:34 GMT

Have you considered setting

Option validvarname=V7

It will limit the types of names that will be created. This assumes you're importing the data set.

Re: Renaming multiple variables with special characters using macro

betabr2005 — Mon, 05 Mar 2018 17:35:33 GMT

Reeza, thank you for your reply. We actually need to those variables in order to rename them. If we limit or strip them out before we import, we lose the information we need in order to rename. And also, it is over 200 of those.

Betabr2005

Re: Renaming multiple variables with special characters using macro

Astounding — Mon, 05 Mar 2018 17:51:46 GMT

I don't like macro language as the approach here, mainly because as someone new to SAS you need something that forces you to picture what your program looks like once macro language has done its duty. However, back to the problem at hand, the semicolon needs to be moved:

rename
%do i = 1 %to &num_vars.;
%qscan(&vars,&i) = %qscan(&newname,&i," ")
%end;

;

Re: Renaming multiple variables with special characters using macro

mkeintz — Tue, 06 Mar 2018 01:33:41 GMT

Not only do you need to remove the semicolon as @Astounding noted, but your proc sql does not generate a single variable named &VARS with a list of name literals. Instead it generated a SET of macrovars named VARS1 VARS2 VARS3, etc, each with a single name literal. As a result, you can drop one of the qscans in the loop and convert the other to scan.

Instead of

%do i = 1 %to &num_vars.;
%qscan(&vars,&i) = %qscan(&newname,&i," ")
%end;

you can use

%do i = 1 %to &num_vars.;
  &&vars&i = %scan(&newname,&i," ") 
%end;

What is the double &&? Because macro recursively parses, the && become &, and &I become 1,2,3, etc., generating &VARS1 &VARS2 &VARS3. Upon reparsing they become the name literals generated by proc sql.

Re: Renaming multiple variables with special characters using macro

ChrisNZ — Tue, 06 Mar 2018 01:51:05 GMT

Using macros to handle special characters will just cause confusion if you have no experience.

Stick to the data step. Something like this should be easy to adapt:


data T;
  'kjklj.nb bn'n = 'A';
  '14&a%ba"dsf'n = 'B';
run;
 
data _null_;
  set T (obs=1);
  length VAR_NAME $32;
  call execute('proc datasets noprint; modify T; rename');
  do while(1);
    call vnext(VAR_NAME);
    if VAR_NAME='VAR_NAME' then leave;
    call execute(catt(quote(trim(VAR_NAME),"'"),'n=_',translate(trim(VAR_NAME),'__________________________','`~!@#$%^&*()_-=+''".>,<;: ')));
  end;
  call execute('; quit;');
run;

NOTE: Renaming variable 'kjklj.nb bn'n to _kjklj_nb_bn.
NOTE: Renaming variable '14&a%ba"dsf'n to _14_a_ba_dsf.
NOTE: MODIFY was successful for WORK.T.DATA.

Re: Renaming multiple variables with special characters using macro

betabr2005 — Tue, 06 Mar 2018 12:49:20 GMT

Mkeintz, thank you for the information. It removed the error I was getting but it did not rename the variables. The old names still remain. Before asking another question, I think I will go back to the books and investigate further what the issue is.

Re: Renaming multiple variables with special characters using macro

betabr2005 — Tue, 06 Mar 2018 12:44:57 GMT

ChrisNZ, thank you for the different approach on the problem. I will try this one as well.

Re: Renaming multiple variables with special characters using macro

betabr2005 — Tue, 06 Mar 2018 12:48:21 GMT

I want to thank you all for taking the time to respond to my question and also for the patience with a newbie while going through the learning curve.

Greatly appreciated.

Re: Renaming multiple variables with special characters using macro

Tom — Tue, 06 Mar 2018 16:32:45 GMT

Don't introduce macro quoting where you don't need it. It will confuse you and worse it can confuse SAS in some cases. Change your use of %Qscan() to normal %scan().

Why not let PROC SQL handle generating the proper syntax for the RENAME statement?

%macro rename(LIB,DSN,newname);
%local varlist ;
proc sql noprint;
select catx('=',nliteral(name),scan("&newname",varnum,' '))
  into :varlist separated by ' '
from dictionary.columns
where libname=%upcase("&LIB")
  and memname=%upcase("&DSN")
  and varnum <= countw("&newname",' ')
;
quit;

proc datasets library = &LIB nolist;
  modify &DSN;
  rename &varlist ;
  run;
quit;

%mend rename;

Let's try it out.

1313  options validvarname=any mprint ;
1314  data class ;
1315   set sashelp.class ;
1316   rename name='First Name'n Sex='Student Gender'n ;
1317  run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.

1318  %rename(work,class,Name Sex);
MPRINT(RENAME):   proc sql noprint;
MPRINT(RENAME):   select catx('=',nliteral(name),scan("Name Sex",varnum,' ')) into :varlist separated by '
' from dictionary.columns where libname="WORK" and memname="CLASS" and varnum <= countw("Name Sex",' ') ;
MPRINT(RENAME):   quit;

MPRINT(RENAME):   proc datasets library = work nolist;
MPRINT(RENAME):   modify class;
MPRINT(RENAME):   rename "First Name"N=Name "Student Gender"N=Sex ;
NOTE: Renaming variable 'First Name'n to Name.
NOTE: Renaming variable 'Student Gender'n to Sex.
MPRINT(RENAME):   run;
NOTE: MODIFY was successful for WORK.CLASS.DATA.
MPRINT(RENAME):   quit;

Re: Renaming multiple variables with special characters using macro

betabr2005 — Fri, 09 Mar 2018 11:00:52 GMT

Thank you, Tom. It did the trick. 🙂 This is great!