Renaming a group of variables with the same prefix

[Walternate]

Hi,

I have a dataset with about 100 variables all starting with the same prefix (and ending in different numbers, like prefix1, prefix3, prefix7...prefix170, etc.). The numbers are not consecutive. All I want to do is rename the variables prefix1_2008, prefix3_2008, etc. There are too many variables to type them all by hand, so I'm hoping there's some kind of shortcut.

Any help is much appreciated.

Thanks!

[RW9]

Firstly my question would be why?  The reason is that SAS has native functionality to deal with lists of variables with a numeric suffix, if you apply your logic to them they lose all that functionality.  For instance an array statement of:

array some_name{200} $200. prefix1-prefix200;

becomes

array some_name{200} $200. prefix1_2008 prefix2_2008... prefix200_2008;

So far more typing.  Over and of functions and such like also work lists of variables so you would lose that as well.

If there is a specific reason why you need 2008 as part of the variable name then I would suggest you need to think about your data differently, normalize it:

...     prefix          year

...     1                 2008

...     2                 2008

Then use summary functions.

As for technically, then yes, there are many postings on here with methods to do this.

data _null_;

     set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;

     if _n_=1 then call execute('data want; set have (rename=('||strip(name)||'='||strip(name)||'_2008');

     else call execute(' '||strip(name)||'='||strip(name)||'_2008');

     if last then call execute(';run;');

run;

Though do think about what you are going to do further with the data as my question above.

[Lin_Clare]

You can try proc transpose to stack all these variables, which you get a column Old_vari with previous variable name prefix1, prefix3, prefix7....as value.

Then add a new column as New=Old-vari||'_2008', and use this newly created variable after ID statement in another proc transpose, in which you un-stack the table and get values in New column as variable names.

syntax of proc transpose for stack table

proc transpose data=you_input_data out=stack_table  (rename=(col1=value))

Name Old_vari;

By xxxxx   /*columns you want to keep unchanged*/

run;

Data stack_table1;

set stack_table;

New=Old_vari||'_2008';

drop Old_vari;

run;

Proc transpose data=stack_table1 out=you_output_data;

ID New;

Var Value;

By xxxx;

Run;

[data_null__]

Adding 2008 to the end will break your nice variable list.  Look how easy it could be if you just move 2008 to the "left".

data test;
   length prefix1 prefix3 prefix7 prefix170 8;
   retain _all_ 0;
   run;
proc print;
   run;

options dkricond=nowarning;
data test2;
   set test(rename=(prefix1-prefix170=prefix2008_1-prefix2008_170));
   run;
options dkricond=error;
proc print;
   run;

[sbb]

Explore using PROC DATASETS / MODIFY with RENAME -- SAS code can be generated then %INCLUDE, based on the output of a PROC CONTENTS with OUT=  or otherwise manually coded if/where the SAS variable names are known and expected to be present.

Scott Barry
SBBWorks, Inc.

[Ksharp]

proc datasets + call execute() , the benefit is fast speed, no need to overwrite dataset again.

data test;
   length prefix1 prefix3 prefix7 prefix170 8; 
   retain _all_ 0; 
   run; 

data _null_;
 set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='TEST' and name like 'prefix%')) end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist; modify test;rename ');
 call execute(cats(name,'=',name,'_2008'));
 if last then call execute(';quit;');
run;

Xia Keshan

[Zachary]

Below is a way that I have been able to put an "E_" in front of all of my variables. Many thanks especially to data _null_ and Joe Matisse for helping me along the way.

It also has a little code to prevent particular variables from receiving the E_.

proc sql noprint;

  select trim(name)||'= E_'||trim(name)

  into :varlist separated by ' '

  from DICTIONARY.COLUMNS

  WHERE LIBNAME EQ "WORK" AND MEMNAME EQ "FLATENDORSEMENT"

  and upcase(name) ne 'POL_PHR_CODE' and upcase(name) ne 'POL_CODE' and upcase(name) ne 'POL_CMP_CODE';

quit;

%put &varlist;

[sbb]

Do consider that SAS has a macro variable maximum length limitation which could result in variable-name truncation.  Best to avoid the potential situation, program to write SAS code (with a DATA step) to generate the statements needed to perform the RENAME, reading either DICTIONARY or SASHELP view for determining an input variable list.

[PoornimaRavishankar]

data have;                                                      

   length prefix1 prefix3 prefix7 prefix170 8;                  

   retain _all_ 0;                                              

run;                                                         

proc contents data=have out=interim                             

(where=(index(upcase(name),'PREFIX') > 0));                     

run;                                                            

data interim ;                                                  

set interim;                                                    

name_2008 = cats(name,'_2008');                                 

rename_stmt = cats(name,' = ',name_2008);                       

run;     

proc sql;                                                       

select rename_stmt into :rn_st separated by ' ' from interim;   

quit;                                                           

data have (rename = ( &rn_st ) );                               

;                                                               

set have;                                                       

run;