topic Re: Libname vs sql pass thru in SAS Programming

Libname vs sql pass thru

BrahmanandaRao — Mon, 21 Dec 2020 09:04:04 GMT

What is difference between
Libname statement and sql pass thru query

Re: Libname vs sql pass thru

Ksharp — Mon, 21 Dec 2020 12:05:08 GMT

Libname statement need translate sas sql into local sql,
while pass thru don't , just pass local sql into DB and run sql at DB side ,so it is faster then libname .

Re: Libname vs sql pass thru

SASKiwi — Tue, 22 Dec 2020 02:41:02 GMT

A LIBNAME statement connecting to an external database versus an SQL Passthru doing the same - both have advantages and disadvantages:

LIBNAMEs are easier and can be automated for all SAS sessions via SAS metadata or SAS AUTOEXECs but can't handle table names greater than 32 characters long. They can be directly used in DATA steps and many SAS procedures.

SQL Passthru is more powerful, more customisable, and is the only way to access table names greater than 32 characters long, but you need to know the SQL syntax of the database you are querying and can only be used in PROC SQL.

Re: Libname vs sql pass thru

ChrisNZ — Tue, 22 Dec 2020 03:19:55 GMT

This is a badly worded question.

Libnames enable a connection. SQL (passthru or not) can use the connection to access data.

So they are not mutually exclusive. They don't do the same thing.

This means that they be used together:

libname ORADATA oracle user= ....;
proc sql;
  connect using ORADATA;
  select * from (        /* Use SAS SQL before the (         */
     select * from ...   /* Use Oracle SQL between ( and  )  */
  ) ;                    /* Use SAS SQL after the (          */
quit;