Saturday, September 26, 2015

Develop job via SAS DI studio: What is SQL Pass Through and why you use it?

If you are an ETL Developer  and using SAS DI Studio as your ETL tool then this post can be interesting for you. SQL Pass-Through Facility is  SQL procedure that enables developer to send DBMS-specific statements to a DBMS and to retrieve data from database.
You can specify database SQL syntax instead of SAS SQL syntax when you use the Pass-Through Facility.

What is SQL Pass Through?
When we make job via SAS DI studio and use PROC SQL to make operation in database  then your SQL statement will directly communicate with database and make all the operation on your SQL database and final result will bring back to SAS. To activate the feature what you need to do is to set  your SAS DI studio property SQL Pass Through 'Yes'.

Fig 1: How to activate SQL Pass through

Why will you use SQL Pass Through?

1) It's handy when you use database specific function like QUALIFY, RANK etc.
2) If your SQL query has calculation then it will be on SQL database so it's faster
3) Very little processing time on SAS side
4) If you are using store procedure or SQL macro in your ETL job then SQL Pass Through will surely pay off.


Clinnovo said...

This blog gives very important info about SAS Thanks for sharing.Very well written information. Many thanks.
SAS training
SAS course
SAS Certification
SAS Certification course
Online SAS Training
Online Pharmacovigilance course
class room pharmacovilance training

James Zicrov said...

I think there is a need to look for some more information about REST API and SQL.There is actually a wider scope to finding and contemplating some sources about database operations.

SQL Server Load Rest Api


scala online training
azure devops online training
app v online training
sccm online training
windows admin online training
mysql online training

Harsh Vardhan said...

Great Content. It will useful for knowledge seekers. Keep sharing your knowledge through this kind of article.
Mean Stack Training in Chennai
Go Lang Training in Chennai
Mean Stack Course in Chennai