How to Count Observations by Group in SAS: A Step-by-Step Guide

The Role of PROC SQL in SAS

The PROC SQL statement in SAS integrates the powerful querying capabilities of SQL directly into the SAS programming environment. Unlike traditional SAS procedures that follow a strictly procedural workflow, PROC SQL operates declaratively, allowing the user to specify what result they want rather than how to achieve it step-by-step. When counting observations by group, this declarative approach simplifies the task dramatically, requiring only the selection of the grouping variable, the application of the COUNT(*) function, and the specification of the grouping hierarchy.

The syntax for group counting in PROC SQL is highly intuitive. The asterisk in COUNT(*) instructs the procedure to count all rows (observations) that satisfy the criteria defined by the query. When the GROUP BY clause is introduced, the procedure segments the input data set based on the values of the specified variable(s) and applies the COUNT(*) function independently to each segment. This results in an output table where each row represents a unique combination of the grouping variable(s) and includes a calculated column showing the total number of observations found within that particular grouping.

Furthermore, PROC SQL offers extensive capabilities beyond simple counting. Analysts can seamlessly integrate conditional filtering using the WHERE clause, perform joins across multiple data sets, and introduce conditional aggregation using clauses like HAVING. When focusing solely on counting by group, however, the core components—SELECT, FROM, and GROUP BY clause—are sufficient. Mastery of this fundamental SQL structure is crucial for any analyst seeking to efficiently summarize large SAS data libraries.

You can use the following methods to count the total observations by group in SAS:

Syntax Fundamentals: Counting by a Single Group Variable

When the analysis requires segmenting the data based on only one categorical field—such as counting records by ‘Region’ or ‘Product ID’—the single group counting method is employed. This method is the simplest application of the GROUP BY clause and serves as the foundation for more complex aggregations. The resulting output table will contain two columns: the unique value of the grouping variable and the corresponding total count of observations for that value.

The core structure involves selecting the grouping variable and applying the COUNT(*) aggregate function. It is standard practice to use the AS keyword to rename the output of the aggregate function to a meaningful label, such as total_count, ensuring clarity in the resulting report. The efficiency of PROC SQL ensures that this calculation is executed optimally, regardless of the size of the input data set.

The syntax below illustrates how to count observations grouped by a single variable, denoted here as var1, using the sample data set my_data. Note the essential role of the GROUP BY clause in instructing the SQL engine to aggregate results based on the distinct values found in var1.

Method 1: Count Observations by One Group

proc sql;
    select var1, count(*) as total_count
    from my_data
    group by var1;
quit;

Advanced Grouping: Utilizing Multiple Variables

Often, analysts need to drill down deeper into the data structure, segmenting observations based on a combination of factors. This is known as compound grouping or counting by multiple groups. For example, one might need to count players grouped first by ‘Team’ and then by ‘Position’ within each team. This methodology provides a much finer level of detail in the aggregated results, offering insights into cross-classification and intersectional frequencies that single-variable counting cannot capture.

To implement compound grouping in PROC SQL, both grouping variables (var1 and var2 in the example below) must be listed in the SELECT statement and subsequently repeated in the GROUP BY clause, separated by commas. The SQL engine then treats the unique combination of values across all listed grouping variables as a single group. The resulting output will include one row for every unique combination found in the input data set.

It is important to ensure that all non-aggregate columns included in the SELECT statement (i.e., columns not wrapped in functions like COUNT(), SUM(), or AVG()) are explicitly listed in the GROUP BY clause. Failing to include a non-aggregate column in the GROUP BY clause will result in a runtime error, as the SQL engine will not know how to handle the aggregation for that specific column. This strict requirement maintains the integrity and logical structure of the resulting summary table.

Method 2: Count Observations by Multiple Groups

proc sql;
    select var1, var2, count(*) as total_count
    from my_data
    group by var1, var2;
quit;

Detailed Walkthrough: Setting Up the Example Data Set

The following examples show how to use each method with the following dataset in SAS:

To demonstrate these methods practically, we will utilize a small, illustrative data set named my_data. This data simulates records for players, including their assigned team, their position, and their points scored. This simple structure allows us to clearly observe how the GROUP BY clause aggregates the observations based on the categorical variables ‘team’ and ‘position’.

The following SAS code uses the standard DATA step along with DATALINES to create and populate the my_data data set. The variables are defined: team and position are character variables (denoted by the dollar sign $), which will serve as our grouping variables, while points is a numeric variable. Following the data creation, a PROC PRINT statement is used to display the contents, ensuring the data structure is correctly understood before aggregation is performed.

Understanding the raw data is crucial for interpreting the SQL results. In our sample, we can manually count that Team A has 6 observations, Team B has 2, and Team C has 4, totaling 12 observations. We can also see the distribution of positions within those teams, which will be verified by the compound grouping example later. This preparatory step confirms data integrity and provides a baseline for validating the outputs of our PROC SQL statements.

/*create dataset*/
data my_data;
    input team $ position $ points;
    datalines;
A Guard 15
A Guard 12
A Guard 29
A Forward 13
A Forward 9
A Forward 16
B Guard 25
B Guard 20
C Guard 34
C Forward 19
C Forward 3
C Forward 8
;
run;

/*view dataset*/
proc print data=my_data;

Example 1: Count Observations by One Group

Our first practical example demonstrates the application of Method 1, focusing on counting the total number of observations based solely on the ‘team’ variable. This calculation provides a high-level summary of the dataset’s distribution across the three distinct teams (A, B, and C). The goal is to obtain a count representing the number of players associated with each team, irrespective of their position or points scored.

The PROC SQL statement below is concise and performs the required aggregation efficiently. We select the team variable and the COUNT(*) function, assigning the alias total_count to the result. Crucially, the GROUP BY clause specifies team, instructing the SAS engine to collapse rows with the same team identifier into a single summary record, while counting the individual observations contributing to that record.

Upon execution, the resulting table immediately reveals the size of each team category. Interpreting this output allows the analyst to confirm distributional assumptions and identify any imbalances. For instance, if Team B had only one observation, it might signal an issue with data collection or necessitate different statistical treatment compared to the larger teams. The simplicity and speed of this command make it invaluable for initial data exploration and validation within SAS.

/*count observations by team*/
proc sql;
    select team, count(*) as total_count
    from my_data
    group by team;
quit;

From the output we can see that team A contains 6 observations, team B contains 2 observations, and team C contains 4 observations. The total count confirms our manual inspection of the data set.

Example 2: Count Observations by Multiple Groups

Expanding on the previous example, we now apply Method 2 to count observations based on the combination of both ‘team’ and ‘position’. This compound grouping provides a detailed breakdown of the team roster structure, showing exactly how many Guards and Forwards are present on each of the three teams. This level of detail is often necessary when analyzing performance metrics that differ significantly based on roles or sub-categories.

The corresponding PROC SQL statement includes both team and position in the SELECT list. Crucially, the GROUP BY clause must also include both variables (team, position). The SQL engine processes these variables together, meaning it creates a unique group for “Team A, Guard,” a separate group for “Team A, Forward,” and so on. The aggregate function COUNT(*) then calculates the row frequency for each of these unique combinations.

The resulting table is a powerful summary, allowing the analyst to immediately see the hierarchical distribution of the data. For instance, we can quickly determine which teams rely more heavily on Guards versus Forwards, or identify any combination that lacks sufficient data points for reliable statistical inference. This ability to cross-classify and aggregate simultaneously is a core strength of using PROC SQL for complex data summary tasks in SAS.

/*count observations by team and position*/
proc sql;
    select team, position, count(*) as total_count
    from my_data
    group by team, position;
quit;

Interpreting the Results and Further Analysis Potential

The output from the compound grouping exercise provides clear, categorized counts, which are essential for subsequent data modeling and reporting. A quick examination of the results shows the precise breakdown of the total observations. We can summarize the findings directly from the table, understanding not only the overall team size but also the distribution of roles within those teams. This summary confirms that the data is well-structured and ready for further statistical exploration, such as calculating average points per position within each team.

For example, knowing that Team C has 3 Forwards and 1 Guard provides far greater analytical depth than simply knowing Team C has 4 total players. This stratified count is the basis for determining variance, calculating weighted means, or identifying categories that might need balancing or imputation if they are too small. These count statistics are frequently used as input parameters for advanced procedures like PROC GLM or PROC REG, where subgroup sizes influence the reliability of model estimates.

Ultimately, utilizing PROC SQL for observation counting by group offers an efficient, scalable method for initial data summarization in SAS. Whether counting by a single criterion or complex combinations, the SQL approach maintains data integrity and provides clear, actionable summaries crucial for informed decision-making and rigorous statistical analysis. The specific counts derived from Example 2 are explicitly detailed in the following list, summarizing the distribution of players across team and position variables:

• A total of 3 players belong to Team A and hold the position of Forward.
• A total of 3 players belong to Team A and hold the position of Guard.
• A total of 2 players belong to Team B and hold the position of Guard.
• A total of 3 players belong to Team C and hold the position of Forward.
• A total of 1 player belongs to Team C and holds the position of Guard.

Conclusion and Related SAS Tutorials

The methods detailed above illustrate the robust capabilities of PROC SQL for counting and summarizing data in SAS. These techniques are foundational for any data preparation or exploratory data analysis (EDA) task.

The following tutorials explain how to perform other common tasks in SAS: