Starting 2009, BASAS provides our members with high quality and practical SAS tips. Thanks to Kirk Paul Lafler, a well-known SAS trainer & author, we will publish his SAS tips on a quarterly basis.
The SAS System can be directed to automatically display additional information directly to the SAS log by specifying the MSGLEVEL= SAS System option using an Options statement. The MSGLEVEL= option supports two options: N (which is the default) and I. By specifying MSGLEVEL=I, SAS displays to the SAS Log helpful information pertinent to merge and sort processing; as well as index usage and suggestions on what can be done to influence SAS to use an index; along with the usual assortment of notes, warnings, and error messages.
To demonstrate the effect of using a MSGLEVEL=I option, the following code example illustrates a program that performs a simple SQL join on two tables, MOVIES and ACTORS. As can be seen in the resulting SAS Log, an informative message was automatically generated explaining that the SAS system chose to use an available index called Rating to optimize WHERE clause processing. This type of information is not only helpful in gaining a better understanding of what the SAS system did to improve processing, but provides the specific name of the index that was selected to achieve improved processing.
When it comes to performing PROC SQL joins, users supply the list of tables for joining along with the join conditions, and the PROC SQL optimizer has the task of determining which of the available join algorithms to use for performing the join operation. There are three basic algorithms used in joining:
Under Version 8.2 or later, PROC SQL supports a powerful “undocumented” option called _METHOD. Although undocumented features like the _METHOD option should be used with caution, SAS users may find this option to provide far greater value than risk. In fact, the _METHOD option is worth exploring because the benefits associated with gaining a better understanding associated with what happens during specific PROC SQL processes, including joins.
The various codes and their corresponding descriptions associated with the _METHOD option appear in the table below.
|SQXCRTA||Create table as Select.|
|SQXSLCT||Select statement or clause.|
|SQXJSL||Step loop join (Cartesian).|
|SQXJM||Merge join operation.||SQXJNDX||Index join operation.|
|SQXJHSH||Hash join operation.|
|SQXSRC||Source rows from table.|
|SQXSUMG||Summary stats (aggregates) with GROUP BY clause.|
|SQXSUMN||Summary stats with no GROUP BY clause.|
The following PROC SQL code example, and corresponding SAS Log, illustrates the results from running the _METHOD option with a simple two-way equi-join. The _METHOD option displays information that can help users better understand, as well as tune and debug their join queries. The SAS Log illustrates that the two-source tables are MOVIES and ACTORS, and the join algorithm used by the PROC SQL optimizer is a hash join. Because a hash join utilizes available real memory to perform the join, it is often faster than a merge or index-join operation because of the speed of real memory as well as no sort operation on the source tables being required.
If you would like more information or have any questions about this tip, please contact: Kirk Paul Lafler, Software Intelligence Corporation at KirkLafler@cs.com. Kirk has been working with the SAS System since 1979 and is a SAS Certified Professional®. His company provides custom SAS programming, application design and development, consulting services, and hands-on SAS training to clients around the world. Kirk is the author of four books including PROC SQL: Beyond the Basics Using SAS by SAS Institute, and more than three hundred peer-reviewed articles and papers that have appeared in professional journals and SAS User Group proceedings. Kirk can be reached at KirkLafler@cs.com with questions.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.
Other brand and product names are trademarks of their respective companies.