Data Step

How to Aggregate and Sum Data by Group

Simon 35 vistas

Nivel de dificultad

Débutant

Publicado el : 20/04/2022

Consejo del experto

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

Ver toda la categoría

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.

Aviso importante

Los códigos y ejemplos proporcionados en WeAreCAS.eu son con fines educativos. Es imperativo no copiarlos y pegarlos ciegamente en sus entornos de producción. El mejor enfoque es comprender la lógica antes de aplicarla. Recomendamos encarecidamente probar estos scripts en un entorno de prueba (Sandbox/Dev). WeAreCAS no acepta ninguna responsabilidad por cualquier impacto o pérdida de datos en sus sistemas.

Volver a la lista de artículos

SAS y todos los demás nombres de productos o servicios de SAS Institute Inc. son marcas registradas o marcas comerciales de SAS Institute Inc. en los EE. UU. y otros países. ® indica registro en los EE. UU. WeAreCAS es un sitio comunitario independiente y no está afiliado a SAS Institute Inc.