CODEWITHSUNDEEP
sqlstored-proceduressas
Winand
Winand

Reputation: 2433

Reuse connection in SAS proc sql

I've added remote library in such a way:

libname R rengine=db2 server=SYS1;

But i cannot use it inside proc sql:

25   proc sql;
26   connect using R;
ERROR: SERVER= option, required for connection, not found.
27   quit;

I don't want to connect every time again, because i need to make a lot of calls of stored procedures.

Local SAS9.4, remote SAS9.1.3

Upvotes: 1

Views: 143

Answers (1)

Tom
Tom

Reputation: 51611

I would suggest just saving the information needed to create the libref in macro variables and then reuse them in defining the connection.

%let rengine=db2;
%let server=sys1;
libname R rengine=&rengine server=&server;
proc sql ;
  connect to remote(server=&server dbms=&rengine);

Or you could create the macro variables from the metadata saved in SASHELP.VLIBNAM (or DICTIONARY.LIBNAMES from within PROC SQL.).

proc sql ;
  select sysvalue into :server trimmed
    from dictionary.libnames
    where libname='R' and sysname='Accessed through server'
  ;
  select sysvalue into :rengine trimmed
    from dictionary.libnames
    where libname='R' and sysname="Server's engine"
  ;
  connect to remote(server=&server dbms=&rengine);

Upvotes: 1

Related Questions