- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have a bunch of variables named as product_a, product_b, product_c etc. Is there a quick way to rename all of them into xlm_a, xlm_b, xlm_c... i.e., replace the "product" into "xlm" for all variables starting with "product".
Please note, I have a lot of these types of data, so simply doing rename on each data will be tedious. So ideally, I need some macro looping through all files.
Thanks in advance.
Sunny
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If they were product_1 product_2 etc. with a sequential number, renaming is very easy with the data step RENAME statement.
If they are really variables whose name ends with characters instead of sequential numbers, I think you'd have to write a macro. or maybe trick PROC SQL to do it.
So which case is it?
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Assuming that the variables are in a table called PRODUCT in the WORK library, I would use dictionary tables to get the variable names and then run through a macro loop using the TRANWRD() function to change all the names like this:
data product;
product_a='blah';
product_b='blah';
product_c='blah';
product_d='blah';
product_e='blah';
product_f='blah';
run;
proc sql;
select compress(put(count(name),best.)) into :nvars from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";
select name into :var1-:var&nvars. from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";
quit;
option mprint;
%macro ChangeVarName;
data product;
set product;
%do i=1 %to &nvars.;
rename &&var&i. = %sysfunc(tranwrd(&&var&i.,'product','xml'));
%end;
run;
%mend ChangeVarName;
%ChangeVarName;
option nomprint;
There is a bug in this with the tranwrd function. Maybe someone else on this forum can help me correct it if you can't see my mistake on your own.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I figured it out. I needed to use the %str() functionality for the parameters of the tranwrd() function:
data product;
product_a='blah';
product_b='blah';
product_c='blah';
product_d='blah';
product_e='blah';
product_f='blah';
run;
proc sql;
select compress(put(count(name),best.)) into :nvars from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";
select name into :var1-:var&nvars. from sashelp.vcolumn where libname="WORK" & memname="PRODUCT";
quit;
option mprint;
%macro ChangeVarName;
data product;
set product;
%do i=1 %to &nvars.;
rename &&var&i. = %sysfunc(tranwrd(&&var&i.,%str(product),%str(xml)));
%end;
run;
%mend ChangeVarName;
%ChangeVarName;
option nomprint;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It's likely the bug is because of the quotes. Since macro language is executing the function (via %SYSFUNC), the quotes around 'product' and 'xml' should be removed.
One issue to watch for is the list of variable names. In the sample code, every variable name begins with "product_". Some of the logic may have to be adjusted if there are other variable names in the data set.
All in all, this is a very viable approach for a single data set. Looping through many data sets may require some knowledge about how you plan to identify the location and names of the data sets.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is what I think would be an efficient way to do what you want:
data have;
test_a='blah';
product_a='blah';
product_something='blah';
product_c='blah';
product_d='blah';
product_e='blah';
product_f='blah';
run;
proc sql noprint;
create table vars as
select name
from dictionary.columns
where libname='WORK' and
memname='HAVE' and
name =: 'product'
;
quit;
data _null_;
set vars end=lastone;
if _n_ eq 1 then call execute('proc datasets library=work; modify have;rename ');
exstmt=catt(name,'=xlm',substr(name,8))||' ';
call execute(exstmt);
if lastone then call execute(';run;');
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you search this forum with key words "rename variables" you will find quite a few threads with all sorts of use cases and solutions to it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A reply amongst many possible others.
Suppose one wants to rename variables starting with "a" with "z";
A cat has more than 1 way to lick its paw (this is a family show).
/*******************/
/*** sample data ***/
/*******************/
data t_a;
input aaa aab aac pa $ pb $ pc $ @@;
cards;
1 2 3 A B C 4 5 6 D E F
;
run;
/******************/
/*** a solution ***/
/******************/
data _null_;
set t_a;
length xx xy $50.;
array v_num(*) _numeric_;
array v_chr(*) _character_;
if _N_>1 then stop;
if _N_ =1 then do;
call execute('proc datasets noprint; modify t_a; rename ');
do i = 1 to dim(v_num);
if substr(vname(v_num(i)),1,1)='a' then do;
xx= compress('z'||substr(vname(v_num(i)),2));
call execute(vname(v_num(i))||'='||xx||' ');
end;
end;
do i = 1 to dim(v_chr);
if substr(vname(v_chr(i)),1,1)='a' then do;
xx= compress('z'||substr(vname(v_chr(i)),2));
call execute(vname(v_chr(i))||'='||xx||' ');
end;
end;
call execute('; quit; run;');
end;
run;