Sentry Page Protection
Proc SQL [12-14]
Counting Statistics
SELECT count(*) AS n
There are mainly three ways to use the COUNT function in Proc SQL.
Copy and run the SCHOOL table from the yellow box below if you haven't done so.
1. Counting number of rows in a table
The COUNT function can be used to count the number of rows in a table.
Example
1. Counting number of rows in a table
The COUNT function can be used to count the number of rows in a table.
Example
Proc sql;
select count(*) as n
from school;
quit;
select count(*) as n
from school;
quit;
The COUNT function in this example counts the number of rows in the SCHOOL table and displays it under the column n.
There are, in total, 20 rows of data in the SCHOOL table.
The result is computed and displayed in the output:
2. Counting unique values
You can also count unique values in a column using the COUNT function.
Example
Proc sql;
select
count(distinct school)
as n
from school;
quit;
select
count(distinct school)
as n
from school;
quit;
In this example, the DISTINCT keyword is specified in the COUNT function.
The COUNT function counts the number of unique schools in the table.
In total, there are three unique schools in the table (i.e. School A, B and C).
In total, there are three unique schools in the table (i.e. School A, B and C).
3. Counting # of rows separately for different groups of data
Similar to the other summary functions such as the MEAN and STD function, you can use the GROUP BY clause with the COUNT function to group your analysis by different groups of data.
Example
Similar to the other summary functions such as the MEAN and STD function, you can use the GROUP BY clause with the COUNT function to group your analysis by different groups of data.
Example
Proc sql;
select gender,
count(*) as n
from school
group by gender;
quit;
select gender,
count(*) as n
from school
group by gender;
quit;
The Proc SQL step in this example counts the number of male and female students in the table.
There are 6 boys and 14 girls in the table.
Exercise
Locate the CARS table from the SASHelp library.
Write a Proc SQL step to count the number of cars each car maker has in the table.
Locate the CARS table from the SASHelp library.
Write a Proc SQL step to count the number of cars each car maker has in the table.
Need some help?
HINT:
Use the GROUP BY clause to group your analysis for each of the car makers.
SOLUTION:
proc sql;
select make, count(*) as n
from sashelp.cars
group by make;
quit;
Fill out my online form.