SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to rename multiple batches of variables in PROC SQL?

Reply
Occasional Contributor
Posts: 6

How to rename multiple batches of variables in PROC SQL?

[ Edited ]

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;

Super User
Posts: 5,499

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

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

Occasional Contributor
Posts: 6

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

Posted in reply to Astounding

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.

Super User
Posts: 5,499

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

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.

Respected Advisor
Posts: 4,173

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

@NR13

Share your actual code which leads to this error.

Super User
Posts: 11,343

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


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.

Super User
Posts: 11,343

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

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.

 

Ask a Question
Discussion stats
  • 6 replies
  • 192 views
  • 0 likes
  • 4 in conversation