topic Re: How to rename multiple batches of variables in PROC SQL? in SAS Data Management

How to rename multiple batches of variables in PROC SQL?

NR13 — Thu, 18 May 2017 18:42:19 GMT

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;

Re: How to rename multiple batches of variables in PROC SQL?

Astounding — Thu, 18 May 2017 18:55:04 GMT

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

Re: How to rename multiple batches of variables in PROC SQL?

NR13 — Thu, 18 May 2017 19:00:45 GMT

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.

Re: How to rename multiple batches of variables in PROC SQL?

Astounding — Thu, 18 May 2017 19:34:41 GMT

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.

Re: How to rename multiple batches of variables in PROC SQL?

ballardw — Thu, 18 May 2017 20:15:10 GMT

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.

Re: How to rename multiple batches of variables in PROC SQL?

Patrick — Fri, 19 May 2017 04:50:34 GMT

@NR13

Share your actual code which leads to this error.

Re: How to rename multiple batches of variables in PROC SQL?

ballardw — Fri, 19 May 2017 15:15:23 GMT

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