Help using Base SAS procedures

rename a bunch of variables

Reply
Contributor
Posts: 33

rename a bunch of variables

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

Trusted Advisor
Posts: 1,615

Re: rename a bunch of variables

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?

Regular Contributor
Posts: 220

Re: rename a bunch of variables

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.

Regular Contributor
Posts: 220

Re: rename a bunch of variables

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;


Super User
Posts: 5,083

Re: rename a bunch of variables

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. 

PROC Star
Posts: 7,363

Re: rename a bunch of variables

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;

Respected Advisor
Posts: 3,893

Re: rename a bunch of variables

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.

Contributor
Posts: 52

Re: rename a bunch of variables

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;

Ask a Question
Discussion stats
  • 7 replies
  • 333 views
  • 0 likes
  • 7 in conversation