Renaming multiple variables with special characters using macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Renaming multiple variables with special characters using macro

 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.

 


Accepted Solutions
Solution
‎03-09-2018 06:01 AM
Super User
Super User
Posts: 7,762

Re: Renaming multiple variables with special characters using macro

[ Edited ]
Posted in reply to betabr2005

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;

View solution in original post


All Replies
Super User
Posts: 22,575

Re: Renaming multiple variables with special characters using macro

Posted in reply to betabr2005

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.

Occasional Contributor
Posts: 6

Re: Renaming multiple variables with special characters using macro

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

Super User
Posts: 6,396

Re: Renaming multiple variables with special characters using macro

Posted in reply to betabr2005

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;

;

Trusted Advisor
Posts: 1,251

Re: Renaming multiple variables with special characters using macro

Posted in reply to betabr2005

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.

Occasional Contributor
Posts: 6

Re: Renaming multiple variables with special characters using macro

[ Edited ]

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. 

 

PROC Star
Posts: 2,136

Re: Renaming multiple variables with special characters using macro

Posted in reply to betabr2005

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.

Occasional Contributor
Posts: 6

Re: Renaming multiple variables with special characters using macro

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

 

Occasional Contributor
Posts: 6

Re: Renaming multiple variables with special characters using macro

Posted in reply to betabr2005

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.

 

Solution
‎03-09-2018 06:01 AM
Super User
Super User
Posts: 7,762

Re: Renaming multiple variables with special characters using macro

[ Edited ]
Posted in reply to betabr2005

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;
Occasional Contributor
Posts: 6

Re: Renaming multiple variables with special characters using macro

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 149 views
  • 0 likes
  • 6 in conversation