topic Re: Rename all 550 variables in New SAS User

Rename all 550 variables

Denali — Tue, 01 Oct 2019 20:33:06 GMT

Hi,

I have a dataset contains 551 variables (ID, var1, var2, var3.....var550). I would like to rename all the var1-var550 with a "_chg" at the end (var1_chg, var2_chg,......var550_chg). What's the best way to rename all 550 variables?

Thank you!

Re: Rename all 550 variables

novinosrin — Tue, 01 Oct 2019 20:50:05 GMT

Hi @Denali

/*Your sample HAVE*/
data have;
 array col(550);
run;
/*Rename*/
data _null_;
 if 0 then set have;
 array c col:;
 call execute('proc datasets lib=work; modify have;rename');
do over c;
  call execute(cats(vname(c),'=',vname(c),'_chg  '));
end;
 call execute(';quit;');
run;

Of course you can query dictionary columns /sashelp vcolumns and get the same . I just fancied something of the above kind

Re: Rename all 550 variables

mkeintz — Tue, 01 Oct 2019 20:52:24 GMT

This is a common request. The strategy is to (1) get the metadata (in this case all the variable names, each followed by and equal sign and a corresponding new variable name) into a macro variable, Then include this macrovar in a rename statement in a PROC DATASETS, as in:

data have;
  array v {500} var1-var500;
  do _n_=1 to dim(v);  v{_n_}=_n_; end;
  output;
  stop;
run;

proc sql noprint;
  select 
    cats(name,'=',name,'_chg')
   into :rename_list separated by ' '
  from dictionary.columns where libname='WORK' and memname='HAVE';
quit;


proc datasets library=work nolist;
  modify have;
  rename &rename_list ;
  run;
quit;

There are a number of metadata resource in various members of the DICTIONARY container, which is accessible only via a proc sql. This particular members here is COLUMNS.

regards,

Mark

Re: Rename all 550 variables

ballardw — Tue, 01 Oct 2019 20:53:45 GMT

This should get you started. In the PROC SQL put the library name and dataset name, in upper case, where I show LIB and DATASET. This extracts the names of the variables if they actually start with VAR. The data set is used to create proc datasets rename statement. Make sure to use the same library and dataset names that you did in the Proc SQL step.

proc sql;
   create table work.rename as
   select name, cats(name,'_chg') as newname
   from dictionary.columns
   where libname='LIB' and memname='DATASET'
   and upcase(name) like 'VAR%'
   ;
quit;

data _null_;
   set work.rename end=LastName;
   if _n_ = 1 then do;
      /* LIB and DATASET below are the library and data set name used above*/
      Call execute ("Proc datasets library=LIB nodetails nolist;");
      Call execute ("modify DATASET;");
      Call execute ("rename ")  ;
   end;
   Call execute(catx(' ',name,' = ',newname)) ;
   if LastName then do;
      Call execute (";") ;
      Call execute ("quit;");
   end;
run;

Re: Rename all 550 variables

Reeza — Tue, 01 Oct 2019 20:58:40 GMT

If you're willing to change the name slightly this can be a one liner.

rename var1-var550 = var_chg_1-var_chg_550;

@Denali wrote:

Hi,

I have a dataset contains 551 variables (ID, var1, var2, var3.....var550). I would like to rename all the var1-var550 with a "_chg" at the end (var1_chg, var2_chg,......var550_chg). What's the best way to rename all 550 variables?

Thank you!