topic Re: rename a bunch of variables in SAS Procedures

rename a bunch of variables

Sunny_Sun — Wed, 04 Mar 2015 20:32:31 GMT

Hi,

I have a bunch of variables named as product_a, product_b, product_c etc. Is there a quick way to rename all of them into xlm_a, xlm_b, xlm_c... i.e., replace the "product" into "xlm" for all variables starting with "product".

Please note, I have a lot of these types of data, so simply doing rename on each data will be tedious. So ideally, I need some macro looping through all files.

Thanks in advance.

Sunny

Re: rename a bunch of variables

PaigeMiller — Wed, 04 Mar 2015 20:39:12 GMT

If they were product_1 product_2 etc. with a sequential number, renaming is very easy with the data step RENAME statement.

If they are really variables whose name ends with characters instead of sequential numbers, I think you'd have to write a macro. or maybe trick PROC SQL to do it.

So which case is it?

Re: rename a bunch of variables

djbateman — Wed, 04 Mar 2015 21:12:53 GMT

Assuming that the variables are in a table called PRODUCT in the WORK library, I would use dictionary tables to get the variable names and then run through a macro loop using the TRANWRD() function to change all the names like this:

data product;

product_a='blah';

product_b='blah';

product_c='blah';

product_d='blah';

product_e='blah';

product_f='blah';

run;

proc sql;

select compress(put(count(name),best.)) into :nvars from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";

select name into :var1-:var&nvars. from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";

quit;

option mprint;

%macro ChangeVarName;

data product;

set product;

%do i=1 %to &nvars.;

rename &&var&i. = %sysfunc(tranwrd(&&var&i.,'product','xml'));

%end;

run;

%mend ChangeVarName;

%ChangeVarName;

option nomprint;

There is a bug in this with the tranwrd function. Maybe someone else on this forum can help me correct it if you can't see my mistake on your own.

Re: rename a bunch of variables

djbateman — Wed, 04 Mar 2015 21:35:11 GMT

I figured it out. I needed to use the %str() functionality for the parameters of the tranwrd() function:

data product;

product_a='blah';

product_b='blah';

product_c='blah';

product_d='blah';

product_e='blah';

product_f='blah';

run;

proc sql;

select compress(put(count(name),best.)) into :nvars from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";

select name into :var1-:var&nvars. from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";

quit;

option mprint;

%macro ChangeVarName;

data product;

set product;

%do i=1 %to &nvars.;

rename &&var&i. = %sysfunc(tranwrd(&&var&i.,%str(product),%str(xml)));

%end;

run;

%mend ChangeVarName;

%ChangeVarName;

option nomprint;

Re: rename a bunch of variables

Astounding — Wed, 04 Mar 2015 21:38:00 GMT

It's likely the bug is because of the quotes. Since macro language is executing the function (via %SYSFUNC), the quotes around 'product' and 'xml' should be removed.

One issue to watch for is the list of variable names. In the sample code, every variable name begins with "product_". Some of the logic may have to be adjusted if there are other variable names in the data set.

All in all, this is a very viable approach for a single data set. Looping through many data sets may require some knowledge about how you plan to identify the location and names of the data sets.

Re: rename a bunch of variables

art297 — Wed, 04 Mar 2015 21:47:38 GMT

Here is what I think would be an efficient way to do what you want:

data have;

test_a='blah';

product_a='blah';

product_something='blah';

product_c='blah';

product_d='blah';

product_e='blah';

product_f='blah';

run;

proc sql noprint;

create table vars as

select name

from dictionary.columns

where libname='WORK' and

memname='HAVE' and

name =: 'product'

;

quit;

data _null_;

set vars end=lastone;

if _n_ eq 1 then call execute('proc datasets library=work; modify have;rename ');

exstmt=catt(name,'=xlm',substr(name,8))||' ';

call execute(exstmt);

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

run;

Re: rename a bunch of variables

Patrick — Wed, 04 Mar 2015 21:53:13 GMT

If you search this forum with key words "rename variables" you will find quite a few threads with all sorts of use cases and solutions to it.

Re: rename a bunch of variables

billfish — Wed, 04 Mar 2015 22:12:32 GMT

A reply amongst many possible others.

Suppose one wants to rename variables starting with "a" with "z";

A cat has more than 1 way to lick its paw (this is a family show).

/*******************/
/*** sample data ***/
/*******************/
data t_a;
input aaa aab aac pa $ pb $ pc $ @@;
cards;
1 2 3 A B C 4 5 6 D E F
;
run;

/******************/
/*** a solution ***/
/******************/
data _null_;
set t_a;

length xx xy $50.;
array v_num(*) _numeric_;
array v_chr(*) _character_;

if _N_>1 then stop;

if _N_ =1 then do;
call execute('proc datasets noprint; modify t_a; rename ');

     do i = 1 to dim(v_num);
        if substr(vname(v_num(i)),1,1)='a' then do;
           xx= compress('z'||substr(vname(v_num(i)),2));
           call execute(vname(v_num(i))||'='||xx||' ');
        end;
     end;

     do i = 1 to dim(v_chr);
        if substr(vname(v_chr(i)),1,1)='a' then do;
           xx= compress('z'||substr(vname(v_chr(i)),2));
           call execute(vname(v_chr(i))||'='||xx||' ');
        end;
     end;
     call execute('; quit; run;');
end;

run;