DATA Step, Macro, Functions and more

How to write a macro for renaming lots of variables?

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

How to write a macro for renaming lots of variables?

Hi,

I have a data set that has 200 columns, named VAR1, VAR2, ..., VAR200, say. I need to rename all these in a data step, something like this.

%let realname=A B C D E; /*200 different names*/

***Create a list of 200 fake vars VAR1, VAR2, ...., VAR200;

%macro create_200_vars;

     %global fakevars;

     %do i=1 %to 200 %by 1;

          %let fakevars=&fakevars. VAR&i.;

     %end;

     %put &fakevars;

%mend create_200_vars;

data renamed;

     set unnamed; /*Dataset with unnamed vars VAR1, VAR2, ..., VAR200*/

     do i=1 to 200 by 1;

          rename VAR&i.=scan(&fakevars., &i.); ***WRONG HERE, but don't know how to fix it in this datastep;

     end;

run;

Could you please help? Thanks


Accepted Solutions
Solution
‎09-24-2012 04:51 AM
Super User
Posts: 10,044

Re: How to write a macro for renaming lots of variables?

Arthur, Maybe you put it in a wrong direction.

OP want var1 -> a , not a -> var1 .

I often do it everyday. Call execute is good .

data have;
  input var1-var5;
  cards;
1 1 1 1 1
2 2 2 2 2
;
run;

data x;
input name : $40.;
cards;
a
b
c
d
e
;
run;
data _null_;
set x end=last;
if _n_ eq 1 then call execute('proc datasets library=work nolist; modify have; rename ');
call execute(cats('var',_n_,'=',name));
if last then call execute(';quit;');
run;


Ksharp

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: How to write a macro for renaming lots of variables?

Posted in reply to smilingmelbourne

example(using capital letters in RED ):

data fake;

   array _fake fake1-fake200;

   do over _fake; _fake=1;end;

data real;

   array _real real1-real200;

   do over _real;_real=2;end;

run;

proc sql noprint;

  select name into :fake separated by ' '

     from sashelp.vcolumn

    where libname='WORK' and memname='FAKE';

   select name into :real separated by ' '

     from sashelp.vcolumn

    where libname='WORK' and memname='REAL';

quit;

%macro test;

data real;

   set real;

   rename

     %do i=1 %to 200;

    %scan(&real,&i)=%scan(&fake,&i)

     %end;;

   run;

%mend;

%test

proc contents data=real;run;

Contributor
Posts: 73

Re: How to write a macro for renaming lots of variables?

I cannot use an array because I don't know which column is sure to be character- /numeric-type. They all are supposed to have corresponding columns of identical types, but because many first observations of a column in a data set may be missing, it is set to character-type.

Should I make a macro for converting a column of all data set to its correct type?

Super Contributor
Posts: 349

Re: How to write a macro for renaming lots of variables?

Posted in reply to smilingmelbourne

Hi,

Check this link...it will provide you some idea.

https://communities.sas.com/message/123403#123403

Thanks,

Shiva

PROC Star
Posts: 7,491

Re: How to write a macro for renaming lots of variables?

Posted in reply to smilingmelbourne

An alternative approach:

data have;

  input a b c d e;

  cards;

1 1 1 1 1

2 2 2 2 2

;

proc sql noprint;

  select catt(name,"=var",varnum)

    into :renames separtated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="HAVE"

  ;

quit;

data want;

  set have (rename=(&renames.));

run;

Solution
‎09-24-2012 04:51 AM
Super User
Posts: 10,044

Re: How to write a macro for renaming lots of variables?

Arthur, Maybe you put it in a wrong direction.

OP want var1 -> a , not a -> var1 .

I often do it everyday. Call execute is good .

data have;
  input var1-var5;
  cards;
1 1 1 1 1
2 2 2 2 2
;
run;

data x;
input name : $40.;
cards;
a
b
c
d
e
;
run;
data _null_;
set x end=last;
if _n_ eq 1 then call execute('proc datasets library=work nolist; modify have; rename ');
call execute(cats('var',_n_,'=',name));
if last then call execute(';quit;');
run;


Ksharp

Super User
Super User
Posts: 7,076

Re: How to write a macro for renaming lots of variables?

Posted in reply to smilingmelbourne

To rename variables you really want to generate code. So you could use a MACRO.  The code you basically want to generate are the OLD=NEW name pairs.

If your old names will always be VAR1-VARnnn then the macro only needs to receive the list of new names.

%macro rename(newnames);

%local i ;

%do i=1 %to %sysfunc(countw(&newnames));

  var&i = %scan(&newnames,&i)

%end;

%mend rename;

Then you can use the generated pairs in either RENAME statement or a RENAME dataset option.  The RENAME statement could be part of a DATA step or use PROC DATASETS to modify the names in an existing dataset.

data renamed;

  set unnamed;

  rename %rename(A B C D E);

run;


If the old names vary then you might want a macro that takes two lists.

%macro rename(oldnames,newnames);

%local i ;

%do i=1 %to %sysfunc(countw(&oldnames));

  %scan(&oldnames,&i) = %scan(&newnames,&i)

%end;

%mend rename;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2805 views
  • 8 likes
  • 6 in conversation