Go home on time with these 5 PROC SQL tips

5

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.
 (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.

Tags proc sql
Share

About Author

Charu Shankar

Technical Training Specialist

Charu Shankar has been a Technical Training Specialist with SAS since 2007. She started as a programmer, and has taught computer languages, business and English Language skills. At SAS, Charu teaches the SAS language, SQL, SAS Enterprise guide and Business Intelligence. She interviews clients to recommend the right SAS training to help them meet their needs. She is helping build a center for special needs kids in this project. http://www.handicareintl.org/pankaja/pankaja.swf

Related Posts

5 Comments

  1. Pingback: Rhymes, mnemonics and tips in learning SAS - The SAS Training Post

  2. Pingback: A database professional’s best friend - The SAS Training Post

    • Charu Shankar

      thanks Sunil I find myself reaching for your papers again & again, esp the one on 'Where vs. IF'. thanks for reading & taking the time to comment!

Leave A Reply

Back to Top