topic Re: How to select and rename variables if there's a certain string in the variable in SAS Programming

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

tutu_ — Thu, 28 Oct 2021 20:20:20 GMT

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);

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

ballardw — Thu, 28 Oct 2021 20:53:47 GMT

@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.

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

tutu_ — Wed, 12 Jan 2022 23:12:50 GMT

Thank you!