SAS Certification Prep Guide, 4th Edition

Additional Features

To further refine a PROC SQL query that contains a GROUP BY clause, you can use a HAVING clause. A HAVING clause works with the GROUP BY clause to restrict the groups that are displayed in the output, based on one or more specified conditions.

For example, the following PROC SQL query groups the output rows by JobCode. The HAVING clause uses the summary function AVG to specify that only the groups that have an average salary that is greater than 40,000 is displayed in the output.

proc sql;
   select jobcode,avg(salary) as Avg
      from sasuser.payrollmaster
      group by jobcode
      having Avg>40000
      order by jobcode;

Note: You can learn more about the use of the HAVING clause in Performing Advanced Queries Using PROC SQL.