How to rename multiple batches of variables in PROC SQL?

[NR13]

Hello! I know you can use the code below to rename varables that start with score, but is there a way to add the same suffix in the same dataset to another variable (without doing this twice)? i.e. "and upcase(name) like 'SCORE%' and upcase(name) like 'AGE%'

48674 - Add a suffix or prefix to a group of variable names

proc sql noprint; select cats(name,'=',name,'_OLD')
into :list
separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'ONE'
and upcase(name) like 'SCORE%';
quit;

proc datasets library = work nolist;
modify one;
rename &list;
quit;

[Astounding]

It should work fine, although it sounds like you are looking for OR rather than AND:

and (upcase(NAME) like ('SCORE%') or upcase(NAME) like ('AGE%'));

[NR13]

You're right - definitely an "OR". When I try it, it changes the variables I need, but then repeats the changed variables with errors. So in my case, I'm using MEC1-MEC4 and adding the prefix of "R1_". This is what I'm getting in my log:

NOTE: Renaming variable MEC1 to R1_MEC1.
NOTE: Renaming variable MEC2 to R1_MEC2.
NOTE: Renaming variable MEC3 to R1_MEC3.
NOTE: Renaming variable MEC4 to R1_MEC4.
ERROR: Variable MEC1 is not on file WORK.R1.
ERROR: Variable MEC2 is not on file WORK.R1.
ERROR: Variable MEC3 is not on file WORK.R1.
ERROR: Variable MEC4 is not on file WORK.R1.
ERROR: Variable MEC1 is not on file WORK.R1.
ERROR: Variable MEC2 is not on file WORK.R1.
ERROR: Variable MEC3 is not on file WORK.R1.
ERROR: Variable MEC4 is not on file WORK.R1.

[Astounding]

Something's definitely screwy with that ... looks like &LIST contains more than expected.  At a minimum, show what &LIST looks like.  That may lead you back to DICTIONARY.COLUMNS to revise how &LIST is created.

[Patrick]

@NR13

Share your actual code which leads to this error.

[ballardw]

---

@NR13 wrote:

You're right - definitely an "OR". When I try it, it changes the variables I need, but then repeats the changed variables with errors. So in my case, I'm using MEC1-MEC4 and adding the prefix of "R1_". This is what I'm getting in my log:

NOTE: Renaming variable MEC1 to R1_MEC1.
NOTE: Renaming variable MEC2 to R1_MEC2.
NOTE: Renaming variable MEC3 to R1_MEC3.
NOTE: Renaming variable MEC4 to R1_MEC4.
ERROR: Variable MEC1 is not on file WORK.R1.
ERROR: Variable MEC2 is not on file WORK.R1.
ERROR: Variable MEC3 is not on file WORK.R1.
ERROR: Variable MEC4 is not on file WORK.R1.
ERROR: Variable MEC1 is not on file WORK.R1.
ERROR: Variable MEC2 is not on file WORK.R1.
ERROR: Variable MEC3 is not on file WORK.R1.
ERROR: Variable MEC4 is not on file WORK.R1.

---

Looks like either the selection code or the Proc datasets is pointing to the wrong dataset.

POST the entire code that generates that error. Please use a code box such as opened using the forum {i} menu icon.

[ballardw]

Note that you really should check on your existing variable names before adding anything to them with a blind rename. If you are working with someone that likes to give variables names like This_is_a_longish_variable_name then you can well bump up against the character limits in a variable name.