BookmarkSubscribeRSS Feed
Sunny_Sun
Calcite | Level 5

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

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
djbateman
Lapis Lazuli | Level 10

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.

djbateman
Lapis Lazuli | Level 10

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;


Astounding
PROC Star

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. 

art297
Opal | Level 21

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;

Patrick
Opal | Level 21

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.

billfish
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2067 views
  • 0 likes
  • 7 in conversation