topic Problem to rename column names containing special characters in SAS Enterprise Guide

Problem to rename column names containing special characters

ctisseuil — Wed, 19 May 2021 14:11:13 GMT

Hi,

I ma trying to run a macro that replaces all spaces " " in my column names by a "_". Everything is going fine, except when column names contains some special characters such as : "," ".", "#" , etc...

Please find attached a reproducible example. I hope you can help me to debug this issue.

Thanks in advance

Clement

%macro colrename_compress(table);
  %local rename_list sqlobs;
  proc sql noprint;
    select catx('=',nliteral(name),translate(trim(name),'_',' '))
      into :rename_list separated by ' '
    from sashelp.vcolumn
      where libname=%upcase("%scan(work.&table,-2,.)")
        and memname=%upcase("%scan(&table,-1,.)")
        and indexc(trim(name),' ')
    ;
  quit;
  %if &sqlobs %then %do ;
      proc datasets lib=%scan(WORK.&table,-2);
        modify %scan(&table,-1);
          rename &rename_list;
        run;
      quit;
  %end;
%mend colrename_compress;

data faminc;
	input 'famid 1'n 'famid .1'n faminc1-faminc11;
	cards;
1 3281 3413 3114 2500 2700 3500 3114 -999 3514 1282 2434 2818
2 4042 3084 3108 3150 -999 3100 1531 2914 3819 4124 4274 4471
3 6015 6123 6113 -999 6100 6200 6186 6132 -999 4231 6039 6215
;
run;

%colrename_compress(faminc);

Re: Problem to rename column names containing special characters

Tom — Wed, 19 May 2021 14:40:26 GMT

You are only changing the spaces.

translate(trim(name),'_',' ')

You could add those other characters.

translate(trim(name),'____',' #.,')

Or modify it to replace all non digit non alpha characters. Perhaps by using COMPRESS() to get the list.

translate(trim(name),repeat('_',255),compress(name,,'ad'))

Re: Problem to rename column names containing special characters

CarmineVerrell — Wed, 19 May 2021 14:44:27 GMT

I added a compress function to keep the valid sas characters and a substr to only extract the variable name and left your translate . Seems to be working now.

%macro colrename_compress(table);
%local rename_list sqlobs;
proc sql ;
select catx('=',nliteral(name),compress(translate(substr(name,1,length(name)),"_"," "),,'kn'))
into :rename_list separated by ' '
from sashelp.vcolumn
where libname=%upcase("%scan(work.&table,-2,.)")
and memname=%upcase("%scan(&table,-1,.)")
and indexc(trim(name),' ')
;
quit;
%if &sqlobs %then %do ;
proc datasets lib=%scan(WORK.&table,-2);
modify %scan(&table,-1);
rename &rename_list;
run;
quit;
%end;
%mend colrename_compress;

data faminc;
input 'famid 1'n 'famid .2'n faminc1-faminc11;
cards;
1 3281 3413 3114 2500 2700 3500 3114 -999 3514 1282 2434 2818
2 4042 3084 3108 3150 -999 3100 1531 2914 3819 4124 4274 4471
3 6015 6123 6113 -999 6100 6200 6186 6132 -999 4231 6039 6215
;
run;

options symbolgen mprint mlogic;
%colrename_compress(faminc);

Re: Problem to rename column names containing special characters

ctisseuil — Wed, 19 May 2021 15:00:51 GMT

Thanks in works perfectly

Re: Problem to rename column names containing special characters

ctisseuil — Wed, 19 May 2021 15:08:48 GMT

Oups...

Your solution works fine. However, I would like to replace the space in columns names by "_" only, while keeping the special characters.

Example : replacing 'famid 1'n and 'famid .[2'n to 'famid1'n and 'famid.[2'n.

Do you see what I mean ?

Re: Problem to rename column names containing special characters

ballardw — Wed, 19 May 2021 15:22:04 GMT

@ctisseuil wrote:

Oups...

Your solution works fine. However, I would like to replace the space in columns names by "_" only, while keeping the special characters.

Example : replacing 'famid 1'n and 'famid .[2'n to 'famid1'n and 'famid.[2'n.

Do you see what I mean ?

I can see what you think you want but do not understand any reason for it.

Any characters other than letter, digit or _ in a variable name means that you will have to use the name literal syntax, 'somestupid*inname'n for example, and have the system option Validvarname=any set.

Personally I think you going for next to worst of two worlds, replacing one character that makes things readable and keeping characters that make things hard to read. I suspect that it won't take long to get tired of the code errors created when missing one of the quotes or the n if you have a lot of these names to use in code.

Why are having special characters in the variable names desirable? I would suggest using standard names and assigning LABELS to the variables to make "nice" output when needed. You can make the labels much nicer text such as

label famid1 ='Family ID 1'

Re: Problem to rename column names containing special characters

Tom — Wed, 19 May 2021 15:26:49 GMT

Sounds like a silly idea, but in that case just include the extra NLITERAL() function call so that the string generated for the RENAME statement includes the extra quotes and letter n when the values are non standard names.

   select catx('=',nliteral(name),nliteral(translate(trim(name),'_',' ')))

Re: Problem to rename column names containing special characters

ctisseuil — Thu, 20 May 2021 09:35:41 GMT

Hi,

Thanks for your answer,

I have set the system option Validvarname=V7 and it solved my issue.

Thanks