topic Re: How to write a macro for renaming lots of variables? in SAS Programming

How to write a macro for renaming lots of variables?

smilingmelbourne — Sat, 22 Sep 2012 08:59:59 GMT

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

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

Linlin — Sat, 22 Sep 2012 13:11:47 GMT

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;

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

art297 — Sat, 22 Sep 2012 13:25:10 GMT

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;

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

Tom — Sat, 22 Sep 2012 15:37:11 GMT

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;

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

Ksharp — Mon, 24 Sep 2012 08:51:10 GMT

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

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

smilingmelbourne — Thu, 27 Sep 2012 05:26:13 GMT

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?

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

shivas — Thu, 27 Sep 2012 05:45:01 GMT

Hi,

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

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

Thanks,

Shiva