Go home on time with these 5 PROC SQL tips

How can you go home on time? Take a quick tour with these 5 tips that I was able to summarize for my students in the SAS SQL 1: Essentials class yesterday. Since they found it handy and asked if it could become a blog post, I thought I’d share with you. If you’re already an SQL user, hope this helps to review.

1. Begin with output
Since your SQL query can get pretty complex, its best to start with defining your output. What does your manager or team need? Before you go running away to work on the task, do stop to ask them for a sample of the output. If they’re unable to find one, see if you can draw up the report –once you get confirmation from them you’re good to go looking for the data sources. And then you can define the SQL process to get you from input to output.

2. So Few Workers Go Home On Time
Sigh, has the world really come to this? Really? The answer is a resounding NO! You can go home on time by remembering that this acronym is just the sequence of statements that SQL expects from you.

So	SELECT
Few	FROM
Workers	WHERE
Go	GROUP BY
Home	HAVING
On Time	ORDER BY

3. Combining Tables
When to use Joins or SET Operators? Much has been written about this topic. I won’t try to tell you all about it, but just try to summarize a little here. Want to debug a query? Try this real quick visual.

Definition	Joins stack columns & align rows	Set operators stack rows & align columns
Visual
Syntax (Look how even the syntax matches the visual!)	Select * from table1, table2	Select * from table1 SET operator(Except/Intersect/Union/Outer Union) Select * from table2

4. Subquery or Inline View
If not carefully presented or monitored, this can become a hair pulling experience for learners. Here’s my summary to hopefully serve as a clear guide:

Definition	Subquery –is just a query within an outer query.	Inline View-is a datasource that you add inline into your query.
Syntax Structure	Since it’s a subquery, ask yourself, what statements would I use to query my data? WHERE or HAVING of course. So it makes sense that the Subquery returns values to be used on your WHERE or HAVING.	It takes the place of a table. Where does your table definition happen in PROC SQL? In your FROM clause of course, so that’s where you would plug in an inline view.
Usage	A subquery is used to do subsetting of the rows returned by the outer query.	In-line views are generally used to do joins where the query data is not needed in a table or view for subsequent processing.
# of Columns, # of Rows	Can only return 1 column but any number of rows.	Given that it replaces a table, it can return any # of columns and any # of rows just like your table can.
Replace Data Source or Adds Data Source	Since it returns a column, you get an additional data stream	Since it takes the place of a table it replaces a data stream.
Replace Data Source or Adds Data Source	(I just love this distinction between replace and add – don’t you?)

5. Know thy Data
If you’ve read my previous blog post, you already know my number one rule for a SAS programmer. Of course, know thy data. But you don't know your data? No problem, use dictionary tables. You can extract a wealth of information about your system, e.g. what titles are in effect, what macros are available and their values, what system options are turned on...you can even go and query your data for all columns that have the word Date in them. Why is this useful? Consider all the joins you do and you need to know what tables have ORDER_DATE. You could choose to run a PROC CONTENTS on your tables one by one. What an enormous manual task that would be! Or you could check out your dictionary tables for columns. One swift piece of code will get you all tables that have ORDER_DATE. For more on dictionary tables, check this useful paper titled A Hands-on Tour Inside the World of PROC SQL by SQL expert, Kirk Paul Lafler.

Hope these 5 tips were able to summarize PROC SQL for you just like it did for my class yesterday. Try them on for size. Let me know how you make out. Don’t be surprised if you find yourself going home on time. Want more? Check out our SAS SQL 1: Essentials course where we teach you the topics above and much, much more.