05-08-2017 02:02 PM
Generally what is the most efficient way to access big tables (SQl Table or Oracle table) in SAS?
1) Using Libname then set the table
2) Using PROOC SQL pass through
3) Or other options to deal with big tables?
05-08-2017 02:53 PM
I don't think there's one "best" way, it really depends on the database, the table and what your trying to do. That being said, I've found that when working in Oracle with relatively large tables the fastest way to process stuff is to generally rely on explicit pass-through SQL, so I'm basically just using Oracle SQL with SAS as a glue language to hold everything together and then format the final output (although I've been experimenting with using Python for this as well). There are some exceptions to that though, I often use PROC MEANS on datasets accessed via Oracle libraries since for providing bulk analysis of numeric columns it seems to be faster than using straight SQL and usually uses implicit SQL passthrough in any case. On large tables in particular it's critical to make good use of where clauses to minimize the amount of data you're actually working with as well as to leverage indexes when appropriate and minimize the amount of data that needs to be transferred from machine to machine for processing. Oracle is pretty good about using available indexes when applicable, but you can often tweak your queries to ensure your more likely to be able to use them.
I can list out some additional SAS<->Oracle specific stuff I've learned if you're interested, since most of my professional life over the past few years has involved SAS<->Oracle data analysis, although I don't know how much of what I've learned is generally applicable since we kind of use an odd setup.