Three Proc SQL Tips from Bestselling Author Kirk Paul Lafler


We are very lucky to have a great pool of SAS experts to write our SAS Press books. And we love it when some of them share their favorite tips and tricks. This week we asked Kirk Paul Lafler for some tips from his book, PROC SQL: Beyond the Basics Using SAS(R), Second Edition.

Structured Query Language (SQL) is a standardized, widely used language that retrieves and updates data in relational tables and databases. PROC SQL is a powerful Base SAS® Procedure which allows you to write SQL code within the SAS data step. It allows you to retrieve information without having to learn SAS syntax, and can often do this with fewer and shorter statements than traditional SAS code. Additionally, SQL often uses fewer resources than conventional DATA and PROC steps.

Tip #1: Application of the _METHOD PROC SQL Option

PROC SQL’s _METHOD option provides a wealth of information about the processes performed during specific PROC SQL operations, including query analysis and evaluation, any algorithms selected by the optimizer, and/or during testing and debugging operations. The following syntax illustrates the use of the _METHOD option.

PROC SQL  _METHOD;  /* Produces codes related to the SQL query process */


Tip #2: Displaying Additional SAS Log Messages with MSGLEVEL=

SAS users are able to control how much information the SAS System writes to the SAS log by specifying the MSGLEVEL= SAS System option in an Options statement. The MSGLEVEL= option supports two possible values: N (which is the default) to print standard notes, warnings, and error messages; and I to print standard notes, warnings, error messages, plus additional information about sort, merge, and index processing.

When specifying MSGLEVEL=I in an options statement, SAS displays the sort product that was used in a sort operation, a warning when variables are overwritten during merge processing; and the name of the available index that was used in index processing (or helpful suggestions on what can be done to influence SAS to use an available index); along with the usual assortment of notes, warnings, and error messages.

The following example demonstrates how the MSGLEVEL=I system option statement is specified.


Tip #3: Determining the SAS Product(s) Your Organization Licenses and Has Installed

Have you ever wondered which SAS product(s) your organization licenses along with each products expiration date? This tip shows an undocumented procedure, PROC SETINIT, familiar and frequently used by SAS Administrators and Technical Support staff and available only in SAS versions 8 and 9.

The SETINIT procedure displays the SAS product(s) currently licensed, site name and number, the expiration dates for each product licensed, system birthday, and grace and warning periods. Because PROC SETINIT is an undocumented procedure, users should enjoy the information it provides, but use with care.


PROC PRODUCT_STATUS is another SAS procedure used by Administrators, Technical Support staff, and SAS users to assist and support software deployment strategies, planning and scheduling upgrades, contacting SAS Technical Support, and applying fixes. Available starting in SAS version 9.2, the PRODUCT_STATUS procedure provides important information about the specific SAS versions for each licensed and installed product.


We hope you find these tips useful. If you would like to learn more about PROC SQL then check out our FREE SAS documentation, or Kirk's book, PROC SQL: Beyond the Basics Using SAS(R), Second Edition for a step-by-step example-driven guide that helps readers master the language of PROC SQL.


About Author

Sian Roberts

Publisher, SAS Press

Sian is currently Publisher at SAS Press. She has over 20 years of publishing and marketing experience in technology and holds a BEng in Electronic Engineering from Brunel University, London, UK, and a MSc in Cognitive Science & Intelligent Computing from the University of Westminster, London, UK. When Sian is not busily leading SAS Press, she is a devoted soccer/baseball mom to her two boys and walking Chuck, the family chocolate lab.

Related Posts

Comments are closed.

Back to Top