Most of the times while working with SAS we need to pull in data from some external database or push the results back into external databases like DB2, ORACLE, Teradata etc.
SAS has two methods to come across this;
1. Explicit: SQL Pass through
2. Implicit: LIBNAME
We will look into those methods one by one and then we will talk about comparing them. First basic need from SAS setup side is to have SAS/ACCESS installed on your site. There could be SAS/ACCESS to ODBC engine which is quite generic and could be used for most of the external databases; but in turn it will work slower than the database specific drivers. For example if you connect to TERDATA via SAS/ACCESS to ODBC it will be much slower than the SAS/ACCESS to Teradata, because later one is native to Teradata database.
With this method SAS makes explicit connection to the external database meaning; we write SQL code which is native to that specific database; SAS just hands over the query to that database and code gets executed on database engine. After query gets finished the results are handed over back to SAS.
Syntax: For DML type of queries like select/create etc.
PROC SQL;
CONNECT TO TERADATA(USER= PASSWORD= SERVER= DATABASE= );
CREATE table/view _name_ AS /* Optional */
SELECT * FROM CONNECTION TO TERADATA
(SELECT * FROM EMP_DETAILS
WHERE DEPT_ID= 101
AND JOINING_DT = ‘2010-04-21’);
DISCONNECT FROM TERADATA;
QUIT;
Syntax: For DDL type of queries like DROP/UPDATE etc
PROC SQL;
CONNECT TO TERADATA(USER= PASSWORD= SERVER= DATABASE= );
EXECUTE
(UPDATE TABLE EMP_DETAILS DROP COLUMN HOBBIES);
BY TERADATA;
QUIT;
With this method SAS makes implicit connection to external database; meaning we use a SAS syntax to be operated on external database tables; SAS optimizes the code and converts into database specific SQL query which gets handed over to database engine.
External database engine then executes the query and hands over the results back to SAS engine.
Syntax: LIBNAME Method
LIBNAME tera TERADATA SERVER=XXXX USER=XXXX PWD=XXXXXX DATABASE=XXXX;
DATA WORK.EMP_101;
SET TDDB.EMP_DETAILS (WHERE=( JOINING_DT = ‘21APR2010’d));
IF DEPT_ID = 101;
RUN;
As we have discussed already SQL Pass through is explicit connection while LIBNAME is implicit one. So, for using SQL Pass through method one needs to know the coding/syntax, functions, options etc. of the particular database to make right use of potential that particular database engine. While with LIBNAME method you can use any way of SAS coding and SAS will try to optimize the query that has to be passed to external database. Remember you can use any PROC or DATA STEP with LIBNAME method.
With SQL Pass through you can make right use of indexes, constraints etc. which are specific to that database; while with LIBNAME method it is not guaranteed.
SQL method could be efficient in some scenarios; like when there is complex join between many tables residing in external database, or when you have to summarize the data and just get the summary back to SAS as result.
LIBNAME method on other hand could be efficient when let’s say; you have to get the whole table for record by record processing in SAS, or when there is only one table from external database that has to be joined with other SAS datasets etc.
In conclusion there are PRO’s and CON’s of each method and one needs to go by the scenario to choose the right method. However in recent developments on SAS side; SAS is providing many options which are native to external databases available to be used in SAS code via LIBNAME method. For example TPT and FASTLOAD etc. are Teradata specific options but now can be used in SAS DATA STEP coding via LIBNAME method.