BookmarkSubscribeRSS Feed
Jade_SAS
Pyrite | Level 9

Hi All,

 

   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?

 

Thank you!

 

Jade

2 REPLIES 2
Sven111
Pyrite | Level 9

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1362 views
  • 1 like
  • 2 in conversation