topic Re: Libname statement vs Pass thru query in SAS Programming

Libname statement vs Pass thru query

BrahmanandaRao — Sun, 23 Jan 2022 08:59:58 GMT

Hi Guys

How to connect external databases using Libname statement which is better for optimizing code

liname statement or pass thru query

Re: Libname statement vs Pass thru query

Kurt_Bremser — Sun, 23 Jan 2022 09:18:10 GMT

For the first part of your question: read the documentation.

For the second part: If you have a task where the remote SQL provides a tool that has no counterpart in SAS (like using partitions), you need to use explicit pass-through. To compare implicit and explicit, see Maxim 4.

Re: Libname statement vs Pass thru query

Tom — Sun, 23 Jan 2022 16:31:19 GMT

Not sure what the question is here.

You can use a previously defined LIBREF with PROC SQL to connect to the database to push explicit query into the database

libname mydb oracle ..... ;
proc sql;
connect using mydb;
create table want as select * from connection to mydb
( select * from myschema.mytable)
;
quit;

Re: Libname statement vs Pass thru query

s_lassen — Mon, 24 Jan 2022 10:26:47 GMT

I assume your question is: "Which is better for optimizing code - libname or passthrough SQL?".

That depends.

There are obviously things that work better with pass through SQL, such as adding "FOR FETCH ONLY" when getting stuff from DB2 (can speed up things tremendously), or using hints in ORACLE, and a lot of other powerful stuff that various databases let you do.

There are also things that are a lot easier, and at time also faster, using libname access. Especially when you want to to use the sequential processing available in the SAS datastep, or you want to merge data from an SQL database with data from other sources.

One little hint about the latter: SAS has an implicit ORDER BY in setting data from SQL databases, so with code like this

libname accts db2 schema=acct;

data x;
  merge work.recieved accts.ledger;
  by ledger_ID received_date;

The SQL statement executed behind the scenes will include an "ORDER BY ledger_ID recieved_date". If the DB2 table is indexed on these fields, this can be very fast. At times it may also work quite well without indexes, depending on the power of the database server.

In an example like the above, you may also want to put a WHERE clause on the DB2 table, if possible, to speed things up.

Unless performance is a high priority, I would work with the type of connection that makes for the most readable code. Again, that depends on what you want to do, and how much the people who should maintain the code know about native SQL and about SAS.