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);
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.