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.