topic Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly? in SAS Procedures

Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

chrisengel — Mon, 18 Jul 2016 08:58:44 GMT

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.

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

1 var&num_vars

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?

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

Loko — Mon, 18 Jul 2016 09:06:14 GMT

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";

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

Ksharp — Mon, 18 Jul 2016 09:18:52 GMT

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)

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

chrisengel — Mon, 18 Jul 2016 09:21:36 GMT

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.

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

chrisengel — Mon, 18 Jul 2016 09:22:24 GMT

Thanks ksharp -- your solution actually fixes the original issue also -- the case-sensitivity of libname reference.

Much appreciated, sir.

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

chrisengel — Mon, 18 Jul 2016 09:49:05 GMT

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

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

Ksharp — Mon, 18 Jul 2016 09:54:39 GMT

Yes. Just change RENAME into LABEL.

But better post an example to explain your question.

Re: Proc SQL Code working in SAS 9.4 but not SAS 9.3 - nvar to variable not setting properly?

chrisengel — Mon, 18 Jul 2016 09:55:36 GMT

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