The HAVING Clause

A Brief Overview

The HAVING clause tells PROC SQL how to filter the data after summarization. You can use a HAVING clause with a GROUP BY clause to filter grouped data. The HAVING clause affects groups in a way that is similar to how a WHERE clause affects individual rows. When you use a HAVING clause, PROC SQL displays only the groups that satisfy the HAVING expression.
Note: You can use summary functions in a HAVING clause but not in a WHERE clause. The HAVING clause is used with groups, but a WHERE clause can be used only with individual rows.

HAVING Clause Syntax

Syntax, HAVING clause:
PROC SQL <options>;
SELECT column-1 <,...column-n>
FROM input-tables
WHERE expression
GROUP BY column-name <,column-name>
HAVING expression;
QUIT;
expression
produces a value from a sequence of operands and operators.

Subsetting Grouped Data

The HAVING clause is used with at least one summary function and a GROUP BY clause to summarize groups of data in a table. A HAVING clause can be used in any valid SQL expression that is evaluated as either true or false for each group in a query. Alternatively, if the query involves remerged data, then the HAVING clause is evaluated for each row that participates in each group. The query must include one or more summary functions.
Typically, the GROUP BY clause is used with the HAVING clause and defines the group or groups to be evaluated. If you omit the GROUP BY clause, the summary function and the HAVING clause treat the table as one group.

Example: Selecting Groups by Using the HAVING Clause

Suppose you want to select only a subset of groups for your query output. 
proc sql;
   select jobcode, avg(salary) as AvgSalary format=dollar11.2
      from certadv.payrollmaster
      group by jobcode
      having avg(salary)>56000;
quit;
Tip
Alternatively, because the average salary is already calculated in the SELECT clause, the HAVING clause could specify the column alias AvgSalary: 
having AvgSalary > 56000
The query output is shown below. This output is smaller than the previous output because only the values of JobCode that meet the condition in the HAVING clause are displayed.
Output 1.20 PROC SQL Query Result: Average Salaries over $56,000
PROC SQL Query Result: Average Salaries Over $56,000
Without a GROUP BY clause, the HAVING clause calculates the average salary for the table as a whole for all jobs in the company. The output contains either all the rows in the table if the average salary for the entire table is greater than $56,000, or none of the rows in the table if the average salary for the entire table is less than $56,000.

Understanding Data Remerging

Sometimes, when you use a summary function in a SELECT clause or a HAVING clause, PROC SQL must remerge the data (that is, it makes two passes through the table). You can modify your query to avoid remerging.
Consider a PROC SQL query that requires remerging. This query calculates each navigator's salary as a percentage of all navigators' salaries: 
proc sql;
   select empid, salary,(salary/sum(salary)) as Percent format=percent8.2
      from certadv.payrollmaster
      where jobcode contains 'NA';
quit;
When you submit this query, the SAS log displays the following message.
Log 1.3 SAS Log
NOTE: The query requires remerging summary statistics back with the original  data.
Remerging occurs whenever any of the following conditions exist:
  • The values returned by a summary function are used in a calculation.
  • The SELECT clause specifies a column that contains a summary function and other columns that are not listed in a GROUP BY clause.
  • The HAVING clause specifies one or more columns or column expressions that are not included in a subquery or a GROUP BY clause.
During remerging, PROC SQL makes two passes through the table:
  1. PROC SQL calculates and returns the value of summary functions. PROC SQL also groups data according to the GROUP BY clause.
  2. PROC SQL retrieves any additional columns and rows that it needs to display in the output. It uses the result from the summary function to calculate any arithmetic expressions in which the summary function participates.
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset