topic Re: Libname vs Proc Sql in SAS Programming

Libname vs Proc Sql

MadhuKorni — Thu, 29 Nov 2018 11:49:23 GMT

Hi,

If we want to pull the data from a database to SAS, which approach is more efficient? Libname or Proc SQL Connect to?

Regards,

Madhu

Re: Libname vs Proc Sql

andreas_lds — Thu, 29 Nov 2018 12:03:34 GMT

The libname statement does not pull anything, it establishes a connection to the database, so that the tables can be used as if they are datasets. Efficiency depends on the size of the tables and what you want to do with the data.

Re: Libname vs Proc Sql

LinusH — Fri, 30 Nov 2018 14:12:42 GMT

If you use "simple" filtering, they should be equally efficient.

But if you are doing advances logic in the query, explicit pass through (FROM CONNECTION TO) might be faster.

My preference is to use LIBNAME connections if possible, since it is simpler to code, and potentially RDBMS independent.

Re: Libname vs Proc Sql

Tom — Fri, 30 Nov 2018 14:43:49 GMT

The advantage of using a LIBREF is that SAS will convert your queries into appropriate code for the remote database.

The advantage of using SQL is that you can write your own query in pass-thru code and potentially do a better job of making that SQL efficient.

Of course you could just use your libref in your SQL code.

libname mylib oracle .... ;
proc sql ;
  connect using mylib;
  ....
quit;