How to select and rename variables if there's a certain string in the variable

[tutu_]

I have a dataset like this, the dataset name is data1

PIN      data1_S00                 data1_ZC00

1               xxx                                     xxx

2               xxx                                     xxx

I want to rename the variable if the variable name contains "S00" or "S96" or "S97"

Here is the code I wrote, it does the job but it does a lot of unnecessary computation.

The warning is like this "ERROR: Variable s805_S96 is not on file WORK.WANT", This variable is not even in the dataset, I don't know how to get rid of the warning.

%macro rename(data1);

data want_new; set want;run;

proc sql noprint ;
select cats(name,'=&data1') into :renames1 separated by ' '
from dictionary.columns
where libname='WORK' and memname='WANT_NEW' and upcase(name) like '%_S00%' or upcase(name) like '%_S96%';
;
quit;

proc datasets nolist library=work;
modify want_new;
rename &renames1;
run;
quit;

%mend;

%rename(data1);

[ballardw]

---

@tutu_ wrote:

I have a dataset like this, the dataset name is data1

PIN      data1_S00                 data1_ZC00

1               xxx                                     xxx

2               xxx                                     xxx

I want to rename the variable if the variable name contains "S00" or "S96" or "S97"

Here is the code I wrote, it does the job but it does a lot of unnecessary computation.

The warning is like this "ERROR: Variable s805_S96 is not on file WORK.WANT", This variable is not even in the dataset, I don't know how to get rid of the warning.

%macro rename(data1);

data want_new; set want;run;

proc sql noprint ;
select cats(name,'=&data1') into :renames1 separated by ' '
from dictionary.columns
where libname='WORK' and memname='WANT_NEW' and upcase(name) like '%_S00%' or upcase(name) like '%_S96%';
;
quit;

proc datasets nolist library=work;
modify want_new;
rename &renames1;
run;
quit;

%mend;

%rename(data1);

---

I suggest that you create a data set from your first sql so you can see the name(s) selected and what that string

"  name,'=&data1' " actually looks like.

You might consider looking into CALL EXECUTE with the data generated by the sql to have nicer tools to write lines of code instead of hoping that the macros you create, especially if there multiples that match your search requirement.

[tutu_]

Thank you!