Wednesday Half-Day Class Russ Lavery

PROC SQL in Cartoons: The Logic Behind the Scenes

Presented: Wednesday September 5, 2018, 8:00am-11:30am

Presented by:
Russ Lavery is a frequent and multiple award-winning presenter at SAS Users Groups.  He has presented all over the U.S., in Europe, and in Asia.  He is a contractor and lives outside of Philadelphia. His hobbies include SAS, statistics, reading, salsa, bachata and tai chi.


SQL is very a powerful tool for querying data and is so versatile that it should be the entry point for learning SAS.  If you know PROC SQL, you can use it in place of many other SAS procedures.  It often allows you to “get the job done in SAS” without knowing all of SAS.  It is the Swiss army knife of procedures and should be understood by every programmer and statistician.

In addition to PROC SQL being important in SAS, learning SQL is the key to learning other software and is an important career skill.  SQL is the basis for queries in M.S. Access, Oracle, Business Objects, and many other packages.

SQL has been difficult to learn because SQL has been a “black box”.  The historical teaching method has been to have the student run dozens of SQL queries until she or he recognizes a pattern, but it has been impossible to explain why results come out as they do.  This seminar shows a graphical model of previously undocumented PROC SQL internal processes and makes PROC SQL easy to learn.

The major deliverable of this seminar is the graphical representation of the SQL process and we use that graphical model to develop rules for describing, and predicting, the SQL process.  These descriptive rules translate directly into coding rules and allow a programmer to quickly code PROC SQL queries.

Intended Audience: Beginner to intermediate SQL programmers

Tools Discussed: Base SAS, PROC SQL

Prerequisite:  This seminar starts by assuming that attendees know nothing about SQL and takes them to very complicated topics (e.g., correlated versus uncorrelated subqueries, how SQL uses an index, the SQL Optimizer, views and reflexive joins).

Class Outline:

  • The graphical model of SQL queries
    • Displaying query results
    • Presenting data & summarizing data
  • The graphical model of SQL sub-queries
    • Non-correlated sub-queries
    • Correlated sub-queries
  • SQL joins
  • SQL joins vs DATA step joins
    • Reflexive joins
  • Multiple SQL joins and the pivot table
  • Set operators – so useful Venn you need them
    • Introduction to set operators
    • The EXCEPT operator
    • The INTERSECT operator
    • The UNION operator
    • The OUTER UNION operator
  • SQL as an environment
    • Creating views with the SQL procedure
    • Integrity constraints
    • Dictionary tables
    • SQL options
  • SQL Performance
    • Indexes and the where clause
    • The SQL optimizer and the where clause