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.
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;
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.
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
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;
;
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.
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.
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.
ChrisNZ, thank you for the different approach on the problem. I will try this one as well.
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.
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;
Thank you, Tom. It did the trick. 🙂 This is great!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: