BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
betabr2005
Fluorite | Level 6

 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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Reeza
Super User

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.

betabr2005
Fluorite | Level 6

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

Astounding
PROC Star

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;

;

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
betabr2005
Fluorite | Level 6

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. 

 

ChrisNZ
Tourmaline | Level 20

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.

betabr2005
Fluorite | Level 6

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

 

betabr2005
Fluorite | Level 6

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.

 

Tom
Super User Tom
Super User

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;
betabr2005
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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