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);
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'))
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'))
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 ?
@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'
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),'_',' ')))
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);
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.