Hi there
Can anyone help me on renaming all variables starting with the same preffix to a different prefix?
Cheers
Sue
Hello,
data have;
input a_a a_b a_1 a_2 a_1000;
cards;
1 2 3 4 5
;
run;
data _NULL_;
call execute('data want; set have; rename');
do until(fend);
set sashelp.vcolumn end=fend;
where libname="WORK" and memname="HAVE" and NAME like 'a_%';
call execute(cats(NAME,'=b_', substr(NAME,3)));
end;
call execute('; run;');
stop;
run;
Rename x1-x100 to be y1-y100.
In a SAS DATA step, use:
rename x1-x100=y1-y100;
Hi Paige,
Thanks for the suggestions. It is working when I tried below:
rename Cat_:=Sohi_:;
the name of those datasets is not ending with digits and they are random combination
@Suzy_Cat wrote:
Hi Paige,
Thanks for the suggestions. It is working when I tried below:
rename Cat_:=Sohi_:;
Did you mean to say that it is NOT working??
Hi Paige , yes you were right. did not realize there was a type while I actually meant it not working as expected.
Thanks
It is impossible for that syntax to work given how the : suffix works in variable lists. When you use the : suffix you are asking SAS to list all variable in the current data step that start with that prefix. So when you use it on the right of the equal sign in a RENAME statement it will fail in two ways. Either it won't find the variables since they haven't been defined yet. Or it will find the variables, in which case they cannot be used as the new names since they already exist.
Cheers Tom 🙂
Hello,
data have;
input a_a a_b a_1 a_2 a_1000;
cards;
1 2 3 4 5
;
run;
data _NULL_;
call execute('data want; set have; rename');
do until(fend);
set sashelp.vcolumn end=fend;
where libname="WORK" and memname="HAVE" and NAME like 'a_%';
call execute(cats(NAME,'=b_', substr(NAME,3)));
end;
call execute('; run;');
stop;
run;
@Suzy_Cat wrote:
Hi there
Can anyone help me on renaming all variables starting with the same preffix to a different prefix?
Cheers
Sue
@gamotte 's solution works fine, but would perform poorly if the target dataset was large.
Here is another approach:
data have;
length foo a_a a_b x_y x_z a_c bar blah 8;
run;
proc sql noprint;
select name into :vars separated by " "
from dictionary.columns
where libname="WORK"
and memname="HAVE"
and name like "a_%"
;
quit;
%put &=vars;
%macro code;
&word=newprefix_%scan(&word,2,_)
%mend;
proc datasets lib=work nolist;
modify have;
rename %loop(&vars);
quit;
https://github.com/scottbass/SAS/blob/master/Macro/loop.sas
I'm glad you said that @ScottBass. Rename using PROC DATASETS is preferable to rename in a DATA step.
Even more compact code, with no macro looping
proc sql noprint;
select cats(name,'=',tranwrd(name,'a_','b_')) into :renames separated by " "
from dictionary.columns
where libname="WORK"
and memname="HAVE"
and name eqt "a_"
;
quit;
proc datasets library=work nolist;
modify have;
rename &renames;
quit;
@PaigeMiller Yep your code is more compact. I find my %loop macro so useful (to me anyway) that I may have a bit of tunnel vision.
@ScottBass you don't even show your %loop macro, so your code above won't work for anyone but you.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.