topic Re: Rename variables in all tables in SAS Data Management

Rename variables in all tables

AZIQ1 — Mon, 04 Mar 2019 16:43:52 GMT

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

Re: Rename variables in all tables

SuryaKiran — Mon, 04 Mar 2019 17:14:14 GMT

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;

Re: Rename variables in all tables

Tom — Mon, 04 Mar 2019 17:21:35 GMT

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 ;

Re: Rename variables in all tables

AZIQ1 — Mon, 04 Mar 2019 17:43:30 GMT

Thank you so much. It worked