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 */
 
QUIT;

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.

OPTIONS  MSGLEVEL=I ;
PROC SQL ;
 
QUIT ;

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  SETINIT ;
RUN ;

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.

PROC  PRODUCT_STATUS ;
RUN ;

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.