Implicit Pass through using data step

[jim_toby]

Hello,

I've read a lot about implicit and explicit pass-through facility in SAS. I'm wondering if implicit pass through only applies for proc sql? Or does it also apply when writing data steps? All the examples I saw so far regarding implicit pass-through are proc sqls. Can you clear this up for me?

Thanks

[LinusH · Accepted solution]

Pass through works best with SQL since that's the language of RDBMS.
That said, some stuff can be passed from a data step.
WHERE clauses with syntax that can be translated, and a BY will trigger an ORDER BY on the fly.
Bottom line is if you want as much logic to pushed down, use SQL whenever you can.

[r_behata]

I have noticed SAS doing implicit pass thru for Data step as well. However,  I believe it depends on case by case as not all the functions and statements are supported across all the databases. The connectors for several databases are continuously evolving. As the focus is growing more and more towards push down processing so does the support for more functions and proc's to run in the database rather than moving the data across the network.

[Reeza]

Yes, not all functions are passed to the server, so if your SQL or data step uses a function not passed to the server, then it has to operate locally within SAS. Unfortunately this means it does download the entire data set before doing anything, which can be quite time intensive. The documentation includes a list of what functions are passed for each RDMBS. For example PUT/INPUT() are not in the lists which can be problematic because type conversion is a common task. 

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1hwhalvtejwi2n1taei3dzfxvhq.htm&docset...

---

@r_behata wrote:

I have noticed SAS doing implicit pass thru for Data step as well. However,  I believe it depends on case by case as not all the functions and statements are supported across all the databases. The connectors for several databases are continuously evolving. As the focus is growing more and more towards push down processing so does the support for more functions and proc's to run in the database rather than moving the data across the network.

---

[johnsville]

This depends on your backend database.  Procs other than SQL might have SQL generated and passed through, e.g., means, freq.  You have to check the sas access documentation for your backend.  As far as actual data steps running in the database, this is accomplished using proc DS2, which allows you to run threaded code in the DB - but this is available only for Teradata and Hadoop.  Special add-on products may be required (something like in-database code accelerator), and your installation may already have them. 

see:

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acreldb&docsetTarget=p13t...