SAS Press has published 3 popular titles dedicated to demystifying PROC SQL. Today, I've selected Katherine Prairie's book The Essential PROC SQL Handbook for SAS Users to excerpt. One often cited highlight of Katherine's book is that it contains 300 examples of PROC SQL code. Katherine really knows SAS--she's been using it since 1983 (despite being very youthful), and perhaps you'll find a reason to add this book to your shelf.

The following excerpt is from SAS Press author Katherine Prairie's book The Essential PROC SQL Handbook for SAS Users. Copyright © 2005, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)

Remerging summary statistics

SQL queries that contain a GROUP BY and HAVING clause may have to complete at least two passes through a table. Remerging occurs any time a summary function result must be compared to or reported with columns that are not part of the GROUP BY clause.

In the first pass, the summary statistic for each group is calculated. The table is then processed a second time with a column value for each row in a group compared to the summary statistic. Essentially an extra column containing the summary statistic is merged with the table. As a result, a message is written to your SAS log indicating that the summary statistics were remerged with the original data. More detailed information on the remerging process is contained in Chapter 4 “Functions.”

Tip: Often remerging occurs by accident when an SQL statement has an incomplete or incorrect GROUP BY or HAVING clause. It is important to review the SAS log for the following remerging message when working with these clauses:

NOTE: The query requires remerging summary statistics back with the original data.

To read a free chapter from this book and to learn more about Katherine Prairie, visit her author page!