Sentry Page Protection
Proc SQL [10-14]

Summarizing Data
SELECT gender,
  mean(height)
    AS mean_hgt
FROM school
GROUP BY gender;

You can use Proc SQL to summarize data in a way similar to Proc Means and Proc Freq in SAS.

Copy and run the SCHOOL table from the yellow box below if you haven't done so.
​
Example
Proc sql;
select mean(height)
  as mean_hgt
from school;
quit;

​The MEAN function in the SELECT clause computes the average height of the students in the SCHOOL table.

The average height of students is 59.66 inches.​

GROUP BY Clause

The GROUP BY clause is similar to the BY statement in data step.

It is used when computing the statistics separately for different groups of data.

​Example
Proc sql;
select gender,
  mean(height)
    as mean_hgt
from school
group by gender;
quit;

The Proc SQL step above computes the average height separately for the two genders.

The average height for boys and girls are 61.32 inches and 58.95 inches, respectively.

​There are three things you need to do when grouping your analysis.

1. Identify the column to group the analysis

In our example, the column to group the analysis is the GENDER column:

2. Specify the column in the GROUP BY clause

Once you identify the column(s) to group the analysis, you specify them in the GROUP BY clause.

3. Specify the column in the SELECT clause

Finally, you should also specify the column in the SELECT statement.

This tells SAS to display the column in the output. 

In our example, the GENDER column is displayed along with the average height in the output:
Exercise

Locate the CARS table from the SASHelp library.

Write a Proc SQL step that computes the average MSRP for each car maker.
Need some help? 


HINT:

The grouping column in this example is MAKE.


SOLUTION:

proc sql;
select make, mean(msrp) as mean_msrp
from sashelp.cars
group by make;
quit;

Fill out my online form.