BookmarkSubscribeRSS Feed
Siddhartha
Calcite | Level 5
can anyone tell me what exactly the difference between proc sql and sql pass through facility?
1 REPLY 1
GertNissen
Barite | Level 11
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 756 views
  • 0 likes
  • 2 in conversation