Implicit And Explicit PASS THROUGH Facility In SAS : Libname And ProcSQL

Hi All,

The most important job for us to retrieve or fetch the data from the database like : Oracle, DB2, Teradata, Sybase, Mysql, and many more.....

Now we have two facilities to fetch the data from any database to the SAS ( we use SAS/Access for it).

(a) IMPLICIT - Libname

(b) EXPLICIT - SQL Pass Through facility

We are going to see the comparison, difference and many other things between both of them (Libname and SQL pass through).

                  Comparison between LIBNAME and SQL PASS THROUGH

1.  If we are using LIBNAME then SAS passes some functions to database to process the data. We expect that these functions would react in the same manner whether we pass them through the implicit with LIBNAME or explicit with PASS - THROUGH. What i can tell, using these functions would be in equal but LIBNAME would be preferable as the coding for LIBNAME is simple.  We can use following SAS function while interfacing with the ODBC: ABS, AVG, CEIL, LOG, Min, Max, Sin, Sqrt, SUM, Count and many more......


2.   We should not forget that if we use LIBNAME statement, it is used to create a library to store and access the SAS files or datasets in the specified particular folder of an operating system. We can use this one with the data step and proc steps to run and to access the files or datasets but should not be used to access the databases.


3. If we are trying to join more than one table means multiple tables and looking for only aggregate result, it this situation SQL PASS THROUGH facility is much better option. We can leave the aggregation part for the database or RDBMS and it would provide the result which would be in less data volume.


4. Suppose if we are going to do the same aggregation while using LIBNAME facility or engine then  Libname would fetch all the big table (High in volume) over the SAS server and then it would do the  calculation ( like : Joining tables and merging) on the SAS session.

Now the difference would be from few minutes to few hours.. ( for upper two points ).

5.  SQL PASS THROUGH facility which we use as a part of Proc SQL is used to make a connection between the databases such as RDBMS (Teradata / DB2...). We can use explicit facility to access and to create the tables in different RDBMS / Databases.

Some Important Points (I am fan of explicit facility)

a)     Whenever we submit any data source specific (ODBC) SQL statements directly to the any data source like Teradata by using an extension of the Proc SQL, then it would called as SQL Pass through facility.

b)     Explicit facility (SQL Pass through) uses SAS / Access to talk or to connect to the mentioned or referenced database or data sources and passes the statements directly to the database for execution.

c)     Explicit facility / SQL Pass through facility is only an alternate way of SAS   /Access LIBNAME statement to connect to the database.

d)     SQL Pass Through facility / explicit facility support any type of non ANSI standard SQL which is supported by our data source or ODBC.

e)     SQL Pass through facility and Libname facility both are providing the access to the database.

f)       SQL pass through (Explicit facility), which allow us to pass the code directly to the database while libname (Implicit facility) coding is much easier than pass through and system efficiency is optimized by SAS.

We can use SQL pass through facility for the following operations

I. To terminate or establish connection with data source (via ODBC to RDBMS) using connect and disconnect statement

(a)  Use of CONNECT TO statement: It establishes a connection to the data source / database.

(b)  Use of DISCONNECT Statement: It terminates the connection from the data source / database.

II. Explicit facility or SQL Pass through sends dynamic and data source specific (ODBC) statements of SQL to a data source (Database) using execute and connect statements

Use of Execute statement in SQL Pass Through Facility

(a)     Execute Statement Sends data source-specific and non-query SQL statements directly to the data source (Database)

(b)    We cannot store Execute Statement as a part of explicit facility in Sql view.

(c)     We can use following statements for the data source by using explicit facility Execute statement : CREATE, DROP, INSERT, UPDATE and DELETE

Examples for Execute Statement:

• Execute (DROP table 'table name' ) by database_name;


• Execute (CREATE table ' table_name' ( 'Col_1' varchar (10), 'Amount' Currency)) by database_name;


• Execute (INSERT INTO 'table_name' values ('Uma18752',12345)) by database_name;

III. We can use CONNECT TO component in SQL Pass Through to directly retrieve data from a data source by using FROM clause of select statement.

Example:

• Select * from CONNECTION TO database_name (Select * from 'table_name');


• Select * from CONNECTION TO database_name (Select 'Col_1', Amount from 'table_name');

       SYNTAX for SQL PASS THROUGH FACILITY ( EXPLICIT FACILITY)

PROC SQL <options>;

CONNECT TO <database_name> AS <alias> (arguments for connect statements)

DISCONNECT FROM <database_name>/<alias>

EXECUTE <data_base_specific_sql_statements> BY <database_name>

SELECT <list all required column>

FROM CONNECTION TO <database_name>;

For Pass - Through facility any error conditions would also be written to the SAS log and those would be in following macro variables:  SQLXRC, SQLXMSG

Now we are going to see the comparison for all three ways to access or fetch the data from any database.

As we are comparing with the some code and the same result so we need to pass each code in the same environment for the same ODBC (database ). Then only we could get the exact difference:

PASS-THROUGH Facility:

Proc sql;

Connect to teradata(dsn=Uma18752 user=xxxxxx password=yyyyyyy);

Create table practice.explicit as

Select * from connection to teradata

(select tera1.col_1

From tera2.tab01 as tera1

Inner join tera2.cab01 as col_1

On tera1.id_uma=tera2.id_uma

Where Col_1=5001710);

Disconnect from teradata;

Quit;



LIBNAME (Implicit facility):

Libname in teradata complete="dsn=UMA18752;Uid=xxxxxx;pwd=yyyyyyy"

Schema=tera2;

Proc sql;

Create table practice.implicit as

Select tera1.col_1

From in.tab01 as tera1

Inner join in.tera2.cab01 as tera2

On tera1.id_uma=tera2.id_uma

Where Col_1=5001710;

Quit;



DATA STEP Coding:

Data Practice.DSN;

Merge in.tera2.tab01 (in=tera1 in=tera1)

1. tera2.cab01 (in=tera2 keep=id_uma in=tera1

Where=(Col_1=5001710))

By id_uma;

If tera2 and tera1;

Run;

                                                  COMPARISON

The pass-through facility (explicit) and LIBNAME (Implicit) code produce very similar log for execution and  mean run-times. The DATA STEP code uses significantly more time for user cpu, though slightly less system cpu. (See Table 1) :

                                                           TABLE 1 

PASS-THROUGH                   1                  2                  3                     Mean

real time            01:15.7        01:05.0        01:21.2               01:14.1

user cpu time     03.90            3.91             3.94                    3.92

system cpu time  0.86             0.79             0.61                    0.75

Libname - SQL

real time           01:09.1        01:06.1         01:25.6               01:13.6

user cpu time     3.90             3.89              3.62                    3.80

system cpu time 0.55             0.62              0.8                      0.42



DATA STEP  Code

real time          02:58.4          01:13.1        02:05.0              02:05.5

user cpu time   4.79               5.14              4.56                   4.50

system cpu time 0.49             0.55              0.46                   0.50

By the above table we can calculate the data step is taking too much time for processing in comparison of Explicit and implicit so we can say that data step is not up to mark for fetching the data from database.

We can choose implicit facility or explicit facility as per the requirement to fetch the data from any database.

* Sooner we would have screen prints for table 1 result and code as well with easy example.