BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chrisengel
Obsidian | Level 7

I'm trying to use a simple batch renaming macro to simplify a project, here's the SUGI paper:

 

http://support.sas.com/resources/papers/proceedings09/075-2009.pdf

 

/* Creating a dataset */
DATA A;
input id $4. before_var1_after before_var2_after before_var3_after;
datalines;
i001 1 2 3
i002 3 4 5
i003 6 7 8
i004 9 10 12
;
run;

/* Adding Prefix on all variables */
%macro rename(lib,dsn,newname);
proc contents data=&lib..&dsn;
title 'before renaming';
run;
proc sql noprint;
 select nvar into :num_vars
 from dictionary.tables
 where libname="&LIB" and memname="&DSN";
 select distinct(name) into :var1-:var%trim(%left(&num_vars))
 from dictionary.columns
 where libname="&LIB" and memname="&DSN";
 quit;
run;
proc datasets library = &LIB;
modify &DSN;
rename
%do i = 1 %to &num_vars.;
&&var&i = &newname._&&var&i.
%end;
;
quit;
run;
proc contents data=&lib..&dsn.;
title 'after renaming';
run;
%mend rename;
DATA B;
set A;
run;
%rename(WORK,B,Try1); 

 

 

The raw test code seems to work fine in a local 9.4 version of SAS, but when I copy word for word this code into SAS 9.3 on a server, it doesn't work. I get the following error printed to log (and SAS freezes, forcing me to kill the process to restart it):

 

NOTE: no rows were selected.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

WARNING: Apparent symbolic reference NUM_VARS not resolved.

NOTE: Line generated by the macro function "SUBSTR".

1    var&num_vars

           -

           22

           26

ERROR 22-322 Syntax error, expecting one of the following: ','|,FROM, NOTRIM.

 

 

So, it seems like the proc sql of select nvar into :num_vars is failing. Or am I missing something?

 

One thing I should add: I can't copy and paste code into the server because of how the security settings are, so maybe it's as simple as a typo from the code, but I have checked it a few times and can't figure it out.

 

Any help would be appreciated, thanks.

 

EDIT: 

 

Ive isolated the proc sql to run it and see:

 

proc sql noprint;
 select nvar into :num_vars
 from dictionary.tables
 where libname="WORK" and memname="B";
NOTE: NO rows were selected

So it gives me an error just in the basic checking of the num vars from the dataset, which exists. Why would it fire this No rows selected error?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

That code is too clumsy . Try this one .

 

 

%macro rename(lib,dsn,newname);
 data _null_;
  set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&dsn)")) end=last;
  if _n_=1 then call execute("proc datasets library=&lib  nodetails nolist;modify &dsn;rename ");
  call execute(catt(name,'=&newname._',name));
  if last then call execute(';quit;');
 run;
%mend rename;
DATA B;
set sashelp.class;
run;
%rename(WORK,B,Try1) 

View solution in original post

7 REPLIES 7
Loko
Barite | Level 11

Hello,

 

The NOTE about no rows selection means that there is no dataset B in the WORK library.

You shall check the condition

where libname="WORK" and memname="B";

 

chrisengel
Obsidian | Level 7

Hi Loko,

 

thanks for the response. you helped me to focus on this issue

 

I think I have figured it out: the Proc SQL is case sensitive on the libname references, so i was using "Work" not "WORK" in the macro.

Ksharp
Super User

That code is too clumsy . Try this one .

 

 

%macro rename(lib,dsn,newname);
 data _null_;
  set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&dsn)")) end=last;
  if _n_=1 then call execute("proc datasets library=&lib  nodetails nolist;modify &dsn;rename ");
  call execute(catt(name,'=&newname._',name));
  if last then call execute(';quit;');
 run;
%mend rename;
DATA B;
set sashelp.class;
run;
%rename(WORK,B,Try1) 
chrisengel
Obsidian | Level 7
Thanks ksharp -- your solution actually fixes the original issue also -- the case-sensitivity of libname reference.

Much appreciated, sir.
chrisengel
Obsidian | Level 7

@Ksharp I am wondering if something similar can be done to "Relabel", is there a proc datasets that can be used to modify the dataset by relabeling the variables in additio nto rename? So the existing label should be intact, but with the prefix (or suffix, as i have modified the code to have) being applied.

 

Any pointers in the right direction would be appreciated, thanks again.

Ksharp
Super User

Yes. Just change RENAME into LABEL.

But better post an example to explain your question.

chrisengel
Obsidian | Level 7
I will try it and then post a new question in a new thread to be solved if there's an issue (thanks again)

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2809 views
  • 3 likes
  • 3 in conversation