Hi
You have been told wrong.
What SAS actually does is translating the SAS SQL into Oracle SQL and then send this Oracle SQL flavour to the DB.
Using the following option in your code will show you in the Log what SQL is sent to the DB:
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;
There are some functions which SAS can't translate into Oracle syntax. What SAS then does is to pass a SQL without these functions to Oracle and then use the non translatable functions on the result set returned from Oracle.
This might cause performance issues as may be exactly this function would reduce the result set dramatically.
Using the option above shows you what SAS actually could pass to Oracle for execution.
Sometimes a small change to your SAS SQL code can result in SAS sending the full query to Oracle
Below the list of SAS functions which can be translated (and it's getting more and more with every release):
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...
The only cases where writing a pass through SQL makes sense in my opinion is:
- You want to use Oracle specific functionality or functions (i.e. row() ).
- SAS is not able to send part of your query to Oracle which results in a much bigger result set sent back from the DB and you can't re-formulate the SAS SQL in a way that the full code is sent to the DB.
And here some SAS Online doc examples for SQL pass-through to ORACLE:
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...
HTH
Patrick
Message was edited by: Patrick