topic Re: Renaming a group of variables with the same prefix in SAS Programming

Renaming a group of variables with the same prefix

Walternate — Mon, 02 Feb 2015 13:53:59 GMT

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!

Re: Renaming a group of variables with the same prefix

RW9 — Mon, 02 Feb 2015 14:07:39 GMT

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.

Re: Renaming a group of variables with the same prefix

Lin_Clare — Mon, 02 Feb 2015 14:21:03 GMT

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;

Re: Renaming a group of variables with the same prefix

data_null__ — Mon, 02 Feb 2015 15:05:41 GMT

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;

Re: Renaming a group of variables with the same prefix

sbb — Mon, 02 Feb 2015 17:38:31 GMT

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.

Re: Renaming a group of variables with the same prefix

Ksharp — Tue, 03 Feb 2015 08:39:53 GMT

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

Re: Renaming a group of variables with the same prefix

Zachary — Tue, 03 Feb 2015 14:56:19 GMT

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;

Re: Renaming a group of variables with the same prefix

sbb — Tue, 03 Feb 2015 15:50:03 GMT

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.

Re: Renaming a group of variables with the same prefix

PoornimaRavishankar — Wed, 11 Feb 2015 21:10:21 GMT

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;