Hi,
I want to rename a variable in my data tables. I have 50 data sets. The field in all tables has the same starting name "Table_" etc. example:
Table_1 in data set 1
Table_2 in data set 2
Table_3 in data set 3
I want to use a single rename statement in a macro to rename all "Table_:" to "ID_Number"
I tried using wild card ":" but it did not change it.
I used this code:
rename Table: = Id_Number;
Thank you
The RENAME statement does not use variable lists, so it will not recognize TABLE: . Even if the list would only generate one name.
If it is as simple as your sample then just use code generation.
data _null_;
call execute('proc datasets lib=mylib nolist;');
do i=1 to 50;
call execute(cats('modify mylib.dataset',i,';'));
call execute(cats('rename table',i,'=ID_number;run;'));
end;
call execute('quit;');
run;
If not then use metadata to generate the data needed to generate the code.
proc sql ;
create table rename as
select libname,memname,name,'ID_Number' as new_name
from dictionary.columns
where upcase(name) like 'TABLE%'
order by 1,2
;
quit;
filename code temp;
data _null_;
set rename ;
by libname memname;
if first.libname then put 'proc datasets nolist lib=' libname ';' ;
if not (first.memname and last.memname) then
putlog 'ERROR: Duplicate fields in ' libname= memname= name=
;
else do;
put 'modify ' memname ';rename ' name '=' new_name ';run;' ;
end;
if last.libname then put 'quit;';
run;
%include code ;
Hello,
What your trying is not allowed, perhaps you can try an alternative way using dictionary tables.
proc sql;
select cats(name, '=', tranwrd(name,'Table','ID_Number')) into: rename_vars separated by " "
from dictionary.columns
where libname='WORK' and memname='HAVE' and name like 'Table%';
quit;
data have;
set have(rename=(&rename_vars));
run;
The RENAME statement does not use variable lists, so it will not recognize TABLE: . Even if the list would only generate one name.
If it is as simple as your sample then just use code generation.
data _null_;
call execute('proc datasets lib=mylib nolist;');
do i=1 to 50;
call execute(cats('modify mylib.dataset',i,';'));
call execute(cats('rename table',i,'=ID_number;run;'));
end;
call execute('quit;');
run;
If not then use metadata to generate the data needed to generate the code.
proc sql ;
create table rename as
select libname,memname,name,'ID_Number' as new_name
from dictionary.columns
where upcase(name) like 'TABLE%'
order by 1,2
;
quit;
filename code temp;
data _null_;
set rename ;
by libname memname;
if first.libname then put 'proc datasets nolist lib=' libname ';' ;
if not (first.memname and last.memname) then
putlog 'ERROR: Duplicate fields in ' libname= memname= name=
;
else do;
put 'modify ' memname ';rename ' name '=' new_name ';run;' ;
end;
if last.libname then put 'quit;';
run;
%include code ;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.