Rename all variables.

[adil256]

Hi everyone,

I would like to rename all variables of dataset thanks to another dataset with the same structure.

I used the macro from Prasad Ravi (: http://www2.sas.com/proceedings/sugi28/118-28.pdf) with some modification but it doesn't work.

I took a simple exemple to illustrate what i want to achieve.

Data one;
 U=1; 
V=2; 
X=3; 
Y=4; 
Z=5; 
Run; 
Data two;
 a=25; 
b=265; 
c=346; 
d=454; 
e=54; 
Run; 
Data want;
 U=25; 
V=265; 
X=346; 
Y=454; 
Z=54; 
Run;



options macrogen mprint mlogic; 
%macro rename(lib,dsn,dsn1); 
options pageno=1 nodate;
 proc contents data=&lib..&dsn;
 title "Before Renaming All Variables"; 
run;
proc sql noprint;
select nvar into :num_vars
from dictionary.tables
where libname="&LIB" and
memname="&DSN"; 

proc sql noprint;
select nvar into :num_vars1
from dictionary.tables
where libname="&LIB" and
memname="&DSN1"; 

select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN"; 
quit;
run;

select distinct(name) into :varia1-
:varia%TRIM(%LEFT(&num_vars1))
from dictionary.columns
where libname="&LIB" and
memname="&DSN1"; 
quit;
run;


proc datasets library=&LIB;
modify &dsn1;
rename
%do i=1 %to &num_vars1;
 &&varia&i=&&var&i.
%end; 
;
quit;
run;
options pageno=1 nodate;
 proc contents data=&lib..&dsn;
 title "After Renaming All Variables"; 
run;
%mend rename;
 %rename(WORK,ONE,TWO);

Thank you in advance

[Ksharp · Accepted solution]

Data one;
 U=1; 
V=2; 
X=3; 
Y=4; 
Z=5; 
Run; 
Data two;
 a=25; 
b=265; 
c=346; 
d=454; 
e=54; 
Run;

proc transpose data=one(obs=0) out=t1;
var _all_;
run;
proc transpose data=two(obs=0) out=t2;
var _all_;
run;
data rename;
 merge t1 t2(rename=(_name_=name));
run;

data _null_;
 set rename end=last;
 if _n_=1 then call execute('proc datasets library=work nolist nodetails;modify two; rename ');
 call execute(catt(name,'=',_name_));
 if last then call execute(';quit;');
run;

[adil256]

Thank you very much. it works perfectly. 😉

I found my mistake in the previous code but I thinking I'm going to choose your method.

corrected code:

options macrogen mprint mlogic; 
%macro rename(lib,dsn,dsn1); 
options pageno=1 nodate;
 proc contents data=&lib..&dsn;
 title "Before Renaming All Variables"; 
run;
proc sql noprint;
select nvar into :num_vars
from dictionary.tables
where libname="&LIB" and
memname="&DSN"; 
select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN"; 
quit;
run;

proc sql noprint;
select nvar into :num_vars1
from dictionary.tables
where libname="&LIB" and
memname="&DSN1"; 
select distinct(name) into :vari1-
:vari%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN1"; 
quit;
run;




proc datasets library=&LIB;
modify &DSN1;
rename
%do i=1 %to &num_vars;
 &&vari&i=&&var&i.
%end; 
;
quit;
run;
options pageno=1 nodate;
 proc contents data=&lib..&dsn;
 title "After Renaming All Variables"; 
run;
%mend rename;
 %rename(WORK,ONE,TWO);