Table of Contents
To count distinct values in SAS, you can use the SQL DISTINCT function or the PROC FREQ procedure. The SQL DISTINCT function is used to count the number of unique values in a column, while the PROC FREQ procedure is used to count the number of each unique value in a column. Each method has its own advantages and disadvantages, and both can be used to quickly and easily count distinct values in SAS. Examples of each method are provided to help you better understand how to count distinct values in SAS.
You can use the following methods to count distinct values in a dataset in SAS:
Method 1: Count Distinct Values in One Column
proc sql;
select count(distinct var1) as distinct_var1
from my_data;
quit;
Method 2: Count Distinct Values by Group
proc sql;
select var1, count(distinct var2) as distinct_var2
from my_data
group by var1;
quit;
The following examples show how to use each method in practice with the following dataset:
/*create dataset*/
data my_data;
input team $ points;
datalines;
Mavs 10
Mavs 13
Mavs 13
Mavs 15
Mavs 15
Rockets 9
Rockets 10
Rockets 10
Spurs 18
Spurs 19
;
run;
/*view dataset*/
proc print data=my_data;
Example 1: Count Distinct Values in One Column
The following code shows how to count the total distinct values in the team column:
/*count distinct values in team column*/
proc sql;
select count(distinct team) as distinct_teams
from my_data;
quit;
From the output we can see that there are 3 distinct values in the team column.
We can confirm this manually by observing that there are three different teams: Mavs, Rockets, and Spurs.
Example 2: Count Distinct Values by Group
The following code shows how to count the distinct values in the points column, grouped by the team column:
/*count distinct values in points column, grouped by team*/
proc sql;
select team, count(distinct points) as distinct_points
from my_data
group by team;
quit;
The resulting table shows the number of distinct values in the points column, grouped by each of the teams.
The following tutorials explain how to perform other common tasks in SAS: