Data Step

How to Aggregate and Sum Data by Group

Simon 35 views

Difficulty Level

Débutant

Published on : 20/04/2022

Expert Advice

Simon

While PROC REPORT and PROC SQL offer great flexibility, the most efficient native SAS method for aggregating massive datasets remains PROC SUMMARY (or PROC MEANS). It is worth clarifying that the NWAY option actually belongs to these procedures—not SQL—and is a crucial best practice when using a CLASS statement to optimize processing by suppressing unnecessary global subtotals in your output table.

Dans : Data Step

Deduplication of CAS tables: Why your Hash Tables fail and how to correct it

24/06/2023 • 31

How to retrieve the value of a variable whose name is stored in another column?

24/04/2019 • 27

Understanding Implicit vs. Explicit Output

20/04/2019 • 28

How to efficiently extract the last observations from a table?

02/09/2017 • 27

Data Cleaning and De-duplication of Consecutive Dates

22/04/2017 • 30

View full category

In daily data processing, it is very common to need to switch from a "transactional" level of detail (multiple rows per customer) to a "summary" level (one row per customer with a total).

1. The Problem: Summing balances by customer

The user has a dataset containing customer numbers, dates, and amounts (balances). The same customer (Cust_1) appears multiple times on different dates.

Customer	Amount	Date
Cust_1	100.00	01/01/2008
Cust_1	150.00	10/01/2008
Cust_1	125.00	11/01/2008

Objective: Create a new table containing the sum of the amounts for each customer.

Expected result for Cust_1: 375.00

Note :
In the original discussion, an effective solution using PROC REPORT was provided. Although often used for display (printing), this procedure is very powerful for generating output tables.

1	PROC REPORT DATA=cust nowd out=temp;
2	column cust_no bal;
3	define cust_no / group noprint;
4	define bal / analysis sum noprint;
5	RUN;

Code analysis:
out=temp: This is the key option. It tells SAS^© not to just display the report, but to save the result in a table named temp.

column cust_no bal: Selects the columns to use.

define cust_no / group: Indicates that the cust_no variable is the grouping key (like a "GROUP BY" in SQL).

define bal / analysis sum: Indicates that the bal variable should be analyzed and summed.

Note :
PROC SQL (For those accustomed to SQL)
This is often the most intuitive method if you come from the world of relational databases.

1	PROC SQL;
2	create TABLE temp_sql as
3	select cust_no, sum(bal) as Total_Balance
4	from cust
5	group BY cust_no;
6	QUIT;

Note: The nway statement is important to keep only the highest level of summary (by customer) and avoid overall totals.

Use PROC REPORT if you need flexibility and are already building a report.
Use PROC SQL if you prefer standard SQL syntax.
Use PROC MEANS if performance on very large volumes is your priority.

Expert's advice: As Cynthia points out, don't hesitate to consult the SAS^© documentation for the OUTPUT statement (for PROC MEANS) or the OUT= option (for TABULATE and REPORT). This is often where the key to transforming a visual report into a data table lies.

Important Disclaimer

The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.

Back to article list

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.