DATA Step, Macro, Functions and more

Renaming a group of variables with the same prefix

Reply
Frequent Contributor
Posts: 138

Renaming a group of variables with the same prefix

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!

Super User
Super User
Posts: 7,407

Re: Renaming a group of variables with the same prefix

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.

Occasional Contributor
Posts: 14

Re: Renaming a group of variables with the same prefix

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;

Respected Advisor
Posts: 3,777

Re: Renaming a group of variables with the same prefix

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;

2-2-2015 9-03-45 AM.png
Super Contributor
Super Contributor
Posts: 3,174

Re: Renaming a group of variables with the same prefix

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.

Super User
Posts: 9,682

Re: Renaming a group of variables with the same prefix

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

Frequent Contributor
Posts: 115

Re: Renaming a group of variables with the same prefix

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;

Super Contributor
Super Contributor
Posts: 3,174

Re: Renaming a group of variables with the same prefix

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.

Contributor
Posts: 53

Re: Renaming a group of variables with the same prefix

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;                                                           

Ask a Question
Discussion stats
  • 8 replies
  • 5738 views
  • 3 likes
  • 8 in conversation