BookmarkSubscribeRSS Feed
NR13
Fluorite | Level 6

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;

6 REPLIES 6
Astounding
PROC Star

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
Fluorite | Level 6

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
PROC Star

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
Opal | Level 21

@NR13

Share your actual code which leads to this error.

ballardw
Super User

@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
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1721 views
  • 0 likes
  • 4 in conversation