topic Re: Rename variable with macro in two datasets in SAS Procedures

Rename variable with macro in two datasets

My_SAS — Tue, 12 Jun 2012 12:05:39 GMT

tt
I want to Rename the variable names by using macro from dataset l2 and dataset x variable
names should change in the order they are but when u do the folloiwng macro
the renaming is happening but in different order,in the macro it was sorting and changing
the order how can i do it.

Log:

NOTE: Renaming variable v to b2.
NOTE: Renaming variable x2 to s3.
NOTE: Renaming variable z3 to z.

But it should be :

varible v to z
varible x2 to b2
varible z3 to s3

data l2;
input z b2 s3;
cards;
1 2 3
run;

data x;
input v x2 z3;
cards;
1 2 3
run;

proc sql noprint;
select nvar into :num_vars
from dictionary.tables
where libname="WORK" and
memname="L2";

quit;

proc sql;
select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="WORK" and
memname="L2";
quit;

%put &num_vars;

proc sql noprint;
select nvar into :s2num_vars
from dictionary.tables
where libname="WORK" and
memname="X";

quit;

proc sql;
select distinct(name) into :Svar1-
:Svar%TRIM(%LEFT(&s2num_vars))
from dictionary.columns
where libname="WORK" and
memname="X";
quit;

%put &s2num_vars;

%MACRO RENAME(DSN=);
proc datasets library=WORK;
modify &DSN;
rename
%do i=1 %to &num_vars;
&&Svar&i=&&var&i.
%end;
;
quit;
%MEND;

%RENAME(DSN=X);

Re: Rename variable with macro in two datasets

Alpay — Tue, 12 Jun 2012 12:50:39 GMT

You will need to either add an "order by varnum" statement into your sql code or get rid off "distinct" to have it return the variables in the order they are in the data set.

The same goes for data set X where the variables in it happened to be in alphabetical order.

/* got rid off distinct */

proc sql;

select name into :var1-

:var%TRIM(%LEFT(&num_vars))

from dictionary.columns

where libname="WORK" and

memname="L2"

;

quit;

/* added order by varnum */

proc sql;

select distinct(name) into :var1-

:var%TRIM(%LEFT(&num_vars))

from dictionary.columns

where libname="WORK" and

memname="L2"

order by varnum

;

quit;

Re: Rename variable with macro in two datasets

Ksharp — Wed, 13 Jun 2012 02:49:57 GMT

Dictionary table COLUMNS will keep variable name in the order they are stored in dataset .

As long as you can ensure they have the same number of variables.

data l2;
input z b2 s3;
cards;
1 2 3
;
run;

 

data x;
input v x2 z3;
cards;
1 2 3
;
run;

data _null_ ;
 merge sashelp.vcolumn(keep=name libname memname where=(libname='WORK' and memname='X'))
       sashelp.vcolumn(keep=name libname memname where=(libname='WORK' and memname='L2') rename=(name=_name)) end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist memtype=data; modify x; rename ');
 call execute(catx('=',name,_name));
 if last then call execute('; quit;');
run;

Ksharp