Chapter Contents

Previous

Next

Summarizing Groups of Data

Summary functions produce a statistical summary of a table or of group(s) of data. The following example displays the minimum, average, and maximum level of education within each division. Use the GROUPBY clause and a summary function to summarize information about a group of data. If you omit a GROUPBY, one summary value is produced for the entire table.

Summary Functions

The list of Selected Tables in the SQL QUERY TABLES window contains SAMPLE.EMPINFO from the previous example. Select OK.

In the SQL QUERY COLUMNS window, remove COUNT(*) from the list of Selected Columns. Select DIVISION and Education level from the Available Columns list and add them to the list of Selected Columns.

Select Education level a second time from the Available Columns List and add it to the list of Selected Columns.

Select Education level a third time from the Available Columns list and add it to the list of Selected Columns.

Select the first Education level from the Selected Columns list. Select Summary Functions.

Select MIN from the list of Summary Functions. A summary function is applied to the selected column and a default unique column alias is automatically generated. The summary function and the selected column name are automatically set as the label. You can use this default label in the report, or you can set a new alias or label.

Select the second Education level from the Selected Columns List. Select Summary Functions. Select AVG from the list of Summary Functions.

Select the third Education level from the Selected Columns list. Select Summary Functions. Select MAX from the list of Summary Functions.

Select the first Education level from the Selected Columns List. Select Column Alias/Label. Type Minimum Years of Education in the LABEL field of the Column Alias and Label window.

Select OK.

Select the second Education level from the Selected Columns List. Select Column Alias/Label. Type Average Years of Education in the LABEL field of the Column Alias and Label window.

Select OK.

Select the third Education level from the Selected Columns List. Select Column Alias/Label. Type Maximum Years of Education in the LABEL field of the Column Alias and Label window.

Select OK.

Select the second Education level from the Selected Columns List. Select Column Formats.

Type comma4.0 in the Format= field. Select OK.

Select

Tools

Run Immediate

A dialog is displayed.

Group By Columns

Select Group(s) for Summary Functions to display the GROUP BY COLUMNS window.

Select DIVISION from the Available Columns list and add it to the list of Selected Columns. Select OK.

Select

Tools

Run Immediate

The maximum, minimum, and average education levels for each division are displayed in the OUTPUT window.

Select

Tools

Reset

to reset your query and return to the SQL QUERY TABLES window.

Removing Duplicate Rows

You can remove duplicate rows from your query output. To display each distinct division and location, select SAMPLE.EMPINFO and add it to the list of Selected Tables. Select OK.

Select DIVISION and LOCATION in the SQL QUERY COLUMNS window and add them to the list of Selected Columns.

Select

View

Distinct

Select

Tools

Run Query

Run Immediate

Lines in the OUTPUT window which contain the same division and location are not repeated.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.