Instead of just listing individual rows, you can use a summary function (also called an aggregate function) to produce a statistical summary of data in a table. For example, in the SELECT clause in the following query, the AVG function calculates the average (or mean) miles traveled by frequent-flyer club members. The GROUP BY clause tells PROC SQL to calculate and display the average for each membership group (MemberType).

proc sql;
   select membertype, 
          avg(milestraveled)
          as AvgMilesTraveled
      from sasuser.frequentflyers
      group by membertype;

You should already be familiar with the list of summary functions that can be used in a PROC SQL query.

PROC SQL calculates summary functions and outputs results in different ways depending on a combination of factors. Four key factors are

To ensure that your PROC SQL queries produce the intended output, it is important to understand how the factors listed above affect the processing of summary functions. Consider an overview of all the factors, followed by a detailed example that illustrates each factor.

Summary functions specify one or more arguments in parentheses. In the examples shown in this chapter, the arguments are always columns in the table being queried.

The following chart shows how the number of columns specified as arguments affects how PROC SQL calculates a summary function.

proc sql;
    select avg(salary)as AvgSalary
       from sasuser.payrollmaster;

proc sql outobs=10;
    select sum(boarded,transferred,nonrevenue)
           as Total
       from sasuser.marchflights;

Summary functions perform calculations on groups of data. When PROC SQL processes a summary function, it looks for a GROUP BY clause:

proc sql outobs=10;
    select jobcode, avg(salary)
           as AvgSalary
       from sasuser.payrollmaster;

proc sql outobs=10;
    select jobcode, avg(salary)
           as AvgSalary
       from sasuser.payrollmaster
       group by jobcode;

A SELECT clause that contains a summary function can also list additional columns that are not specified in the summary function. The presence of these additional columns in the SELECT clause list causes PROC SQL to display the output differently.

proc sql;
    select avg(salary)
           as AvgSalary
       from sasuser.payrollmaster;

proc sql;
    select jobcode,
            gender,
           avg(salary)
           as AvgSalary
       from sasuser.payrollmaster
       group by jobcode,gender;

In the next few sections, look more closely at the query examples shown above to see how the first three factors impact summary function processing.

Compare two PROC SQL queries that contain a summary function: one with a single argument and the other with multiple arguments. To keep things simple, these queries do not contain a GROUP BY clause.

Below is a PROC SQL query that displays the average salary of all employees listed in the table Sasuser.Payrollmaster:

 proc sql;
    select avg(salary) as AvgSalary
       from sasuser.payrollmaster;

The SELECT statement contains the summary function AVG with Salary as its argument. Because there is only one column as an argument, the function calculates the statistic down the Salary column to display a single value: the average salary for all employees. The output is shown here.

Consider a PROC SQL query that contains a summary function with multiple columns as arguments. This query calculates the total number of passengers for each flight in March by adding the number of boarded, transferred, and nonrevenue passengers:

 proc sql outobs=10;
    select sum(boarded,transferred,nonrevenue)
           as Total
       from sasuser.marchflights;

The SELECT clause contains the summary function SUM with three columns as arguments. Because the function contains multiple arguments, the statistic is calculated across the three columns for each row to produce the following output.

Consider how a PROC SQL query with a summary function is affected by including a GROUP BY clause and including columns outside of a summary function.

Once again, here is the PROC SQL query that displays the average salary of all employees listed in the table Sasuser.Payrollmaster. This query contains a summary function but, since the goal is to display the average across all employees, there is no GROUP BY clause.

 proc sql outobs=20;
    select avg(salary) as AvgSalary
       from sasuser.payrollmaster;

Note that the SELECT clause lists only one column: a new column that is defined by a summary function calculation. There are no columns listed outside of the summary function.

Here is the query output.

Suppose you calculate an average for each job group and group the results by job code. Your first step is to add an existing column (JobCode) to the SELECT clause list. The modified query is shown here:

proc sql outobs=20;
   select jobcode, avg(salary) as AvgSalary
      from sasuser.payrollmaster;

Consider what the query output looks like now that the SELECT statement contains a column (JobCode) that is not a summary function argument.

As this result shows, adding a column to the SELECT clause that is not within a summary function causes PROC SQL to output all rows instead of a single value. To generate this output, PROC SQL

Therefore, the single value for AvgSalary is repeated for each row.

This result is interesting, but you have not yet reached your goal: grouping the data by JobCode. The next step is to add the GROUP BY clause.

Below is the PROC SQL query from the previous page, to which has been added a GROUP BY clause that specifies the column JobCode. (In the SELECT clause, JobCode is specified but is not used as a summary function argument.) Other changes to the query include removing the OUTOBS= option (it is unnecessary) and specifying a format for the AvgSalary column.

 proc sql;
    select jobcode, 
           avg(salary) as AvgSalary format=dollar11.2
       from sasuser.payrollmaster
       group by jobcode;

Consider how the addition of the GROUP BY clause affects the output.

Success! The summary function has been calculated for each JobCode group, and the results are grouped by JobCode.

Sometimes you want to count the number of rows in an entire table or in groups of rows. In PROC SQL, you can use the COUNT summary function to count the number of rows that have nonmissing values. There are three main ways to use the COUNT function.

select count(*) as Count

select count(jobcode) as Count

select count(distinct jobcode)
       as Count

Consider the three ways of using the COUNT function.

Suppose you want to know how many employees are listed in the table Sasuser. Payrollmaster. This table contains a separate row for each employee, so counting the number of rows in the table gives you the number of employees. The following PROC SQL query accomplishes this task:

 proc sql;
    select count(*) as Count
       from sasuser.payrollmaster;

You can also use COUNT(*) to count rows within groups of data. To do this, you specify the groups in the GROUP BY clause. Consider a more complex PROC SQL query that uses COUNT(*) with grouping. This time, the goal is to find the total number of employees within each job category, using the same table that is used above.

proc sql;
   select substr(jobcode,1,2)
          label='Job Category',
          count(*) as Count
      from sasuser.payrollmaster
      group by 1;

This query defines two new columns in the SELECT clause. The first column that is labeled JobCategory, is created by using the SAS function SUBSTR to extract the two-character job category from the existing JobCode field. The second column, Count, is created by using the COUNT function. The GROUP BY clause specifies that the results are to be grouped by the first defined column (referenced by 1 because the column was not assigned a name).

Suppose you want to count all of the nonmissing values in a specific column instead of in the entire table. To do this, you specify the name of the column as an argument of the COUNT function. For example, the following PROC SQL query counts all nonmissing values in the column JobCode:

 proc sql;
    select count(JobCode) as Count
       from sasuser.payrollmaster;

Because the table has no missing data, you get the same output with this query as you would by using COUNT(*). JobCode has a nonmissing value for each row in the table. However, if the JobCode column contained missing values, this query would produce a lower value of Count than the previous query. For example, if JobCode contained three missing values, the value of Count would be 145.

To count all unique values in a column, add the keyword DISTINCT before the name of the column that is used as an argument. For example, here is the previous query modified to count only the unique values:

proc sql;
   select count(distinct jobcode) as Count
      from sasuser.payrollmaster;

This query counts 16 unique values for JobCode.

To display the unique JobCode values, you can apply the method of eliminating duplicates, which was discussed earlier. The following query lists only the unique values for JobCode.

proc sql;
   select distinct jobcode
      from sasuser.payrollmaster;

There are 16 job codes, so the output contains 16 rows.

You have seen how to use the GROUP BY clause to group data. For example, the following query calculates the average salary within each job-code group, and displays the average for each job code:

 proc sql;
    select jobcode, 
           avg(salary) as AvgSalary
           format=dollar11.2
       from sasuser.payrollmaster
       group by jobcode;

There are 16 job codes in the table, so the output displays 16 rows.

Now, suppose you want to select only a subset of groups for your query output. You can use a HAVING clause, following a GROUP BY clause, to select (or filter) the groups to be displayed. The way a HAVING clause affects groups is similar to how a WHERE clause affects individual rows. As in a WHERE clause, the HAVING clause contains an expression that is used to subset the data. Any valid SAS expression can be used. When you use a HAVING clause, PROC SQL displays only the groups that satisfy the HAVING expression.

Modify the query shown above so that it selects only the JobCode groups with an average salary of more than $56,000. The HAVING clause has been added at the end of the query.

 proc sql;
    select jobcode, 
           avg(salary) as AvgSalary
           format=dollar11.2
       from sasuser.payrollmaster
       group by jobcode
       having avg(salary) > 56000;

having AvgSalary > 56000

The query output is shown below. This output is smaller than the previous output, because only a subset of the job-code groups is displayed.

If you omit the GROUP BY clause in a query that contains a HAVING clause, then the HAVING clause and summary functions (if any are specified) treat the entire table as one group. Without a GROUP BY clause, the HAVING clause in the example shown above calculates the average salary for the table as a whole (all jobs in the company), not for each group (each job code). 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).

Sometimes, when you use a summary function in a SELECT clause or a HAVING clause, PROC SQL must remerge data (make two passes through the table). Remerging requires additional processing time and is often unavoidable. However, there are some situations in which you might be able to modify your query to avoid remerging. Understanding how and when remerging occurs increases your ability to write efficient queries.

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 sasuser.payrollmaster
      where jobcode contains 'NA';

When you submit this query, the SAS log displays the following message.

Remerging occurs whenever any of the following conditions exist:

During remerging, PROC SQL makes two passes through the table:

Consider how PROC SQL remerges data when it processes the following query:

proc sql;
   select empid, salary,
          (salary/sum(salary)) as Percent
          format=percent8.2
      from sasuser.payrollmaster
      where jobcode contains 'NA';

In the first pass, for each row in which the jobcode contains 'NA', PROC SQL calculates and returns the value of the SUM function (specified in the SELECT clause).

In the second pass, PROC SQL retrieves the additional columns and rows that it needs to display in output (EmpID, Salary) and the rows in which JobCode contains 'NA'. PROC SQL also uses the result from the SUM function to calculate the arithmetic expression (salary/sum(salary)).