Libname statement vs Pass thru query

[BrahmanandaRao]

Hi Guys

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

liname statement or pass thru query

[Kurt_Bremser]

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.

[Tom]

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;

[s_lassen]

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.