Diff between proc sql and sql pass through facility

[Siddhartha]

can anyone tell me what exactly the difference between proc sql and sql pass through facility?

[GertNissen]

SQL is SQL is SQL – or not 😉

Well PROC SQL without pass through is SAS implementation of the SQL Standard querying SAS data. You can access other data sources with the Libname engine.

The SQL Pass Through facility "moves" your SQL code away from SAS, directly to the SQL Database. The SAS System will not process it, the code will only run on the Database vendors SQL implementation - returning the resulting rows to SAS. The SQL Pass Through facility requires SAS/ACCESS. You cannot use any SAS functions.

When using ODBC between SAS SQL and database SQL, you will have a "bridge" where ODBC will "translate" you SAS SQL (or data step) to the database SQL. This is not always effective – some SAS functions cannot be translated into the Database SQL, in some cases to many rows will be transferred back to SAS instead of being processed at the Database.

SQL is implemented differently by all SQL vendors - Not 2 implementations are alike, though they all strive to implement SQL according to the ANSI SQL standard (adding their own “inventions” – or improvements).

If you want to investigate more into the how the 4 different SQL works, try using options SASTRACE or some of the Proc SQL options FEEDBACK, SORTMSG – You will find many papers at lexjansen.com, support.sas.com or the Online Doc on this subject.

As a rule of thumb - The SQL Pass Through facility will generally run much faster (if the data you query is selected from i.e. DB2, Oracle, SQL Server etc.) Because you save I/O between SAS and the Database.