Renames SAS variables to add an underscore

Question

I have about 600 variables that I want to rename dynamically. I used PROC SQL to create a macro variable containing the variable names. Right now they look like: aayyy, abcjjjjj, bbcjjjjj, etc. I want to add an underscore after the first 2, or 3 characters (depending on the variable) and keep the rest the same. So the final variables would look like aa_yyy, abc_jjjjj, bbc_jjjjj.

Any ideas?

Need more information here. Can you give example of your current code and some example data? — Joe, Sep 25 '13 at 20:07
Its going to be hard to answer explicitly without an explicit/formal idea of the pattern that determines whether the "_" is inserted. Either way, I'm thinking you're going to need to look into the perl regular expressions (PRX) functions given that the pattern looks pretty irregular to me at this stage. They can be used in macro code via the %SYSFUNC() macro function. You can loop the PRX pattern over each word and make changes based on whether it matches... — DJM, Sep 25 '13 at 22:33

score 3 · Answer 1 · answered Sep 26 '13 at 08:37

libname anylib "E:\";
data anylib.table1;
length aayyy eeeeee abcjjjjj bbcjjjjj abcdejd 8;
run;

data work.table2;
length aayyy abcjjjjj bbcjjjjj abcdejd 8;
run;

proc sql;
create table list as
select * from (
select libname, memname, name,
case
when 
    compress(substr(name, 3), substr(name, 3, 1)) = ''
    then catt(substr(name, 1, 2), '_', substr(name, 3))
when 
    compress(substr(name, 4), substr(name, 4, 1)) = ''
    then catt(substr(name, 1, 3), '_', substr(name, 4))
else '' end
    as newname
from dictionary.columns
where  libname in ('WORK', 'ANYLIB') and length(name) >= 5
and ( substr(name, 3, 1) = substr(name, 4, 1)
or substr(name, 4, 1) = substr(name, 5, 1)
    )
) where newname is not null
order by libname, memname
;
quit;

data _null_;
set list;
length stmt $200;
file "E:\renames.sas";
by libname memname;
if first.libname then do;
    stmt = 'proc datasets lib=' || libname || 'nolist nodetails;';
    put stmt;
end;
if first.memname then do;
    stmt = 'modify ' || memname || ';';
    put stmt;
    stmt = 'rename';
    put stmt;
end;
stmt = '    ' || name || ' = ' || newname;
put stmt;
if last.memname then do;
    stmt = ';';
    put stmt;
end;
if last.libname then do;
    stmt = 'quit;';
    put stmt;
end;
run;

%include "E:\renames.sas";

The idea behind compress(substr(... is to find names where 3rd or 4th character repeats until end of the name - compress removes this character and produces empty string. Then we produce the script fro PROC DATASETS in data step and run (%include) it.

Renames SAS variables to add an underscore

1 Answers1