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
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;
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;
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);
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
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.
Ready to level-up your skills? Choose your own adventure.