Business Context
A retail bank needs to generate a monthly summary of customer transactions. The goal is to calculate the total transaction amount, the average transaction value, and the number of transactions for each customer and account type. This summary will be used by financial advisors to review customer activity.
About the Set : aggregation
Data aggregation and summary statistical calculations.
Discover all actions of aggregation Data Preparation
Creation of a sample customer transaction table. It includes customer IDs, account types, transaction dates, and amounts over several months.
Copied!
| 1 | DATA casuser.customer_transactions; |
| 2 | LENGTH CustomerID $10 AccountType $15; |
| 3 | FORMAT TransactionDate yymmdd10.; |
| 4 | INFILE DATALINES delimiter=','; |
| 5 | INPUT CustomerID $ AccountType $ TransactionDate :date9. Amount; |
| 6 | DATALINES; |
| 7 | CUST001,SAVINGS,01JAN2023,150.00 |
| 8 | CUST001,SAVINGS,15JAN2023,200.50 |
| 9 | CUST002,CHECKING,05JAN2023,1200.00 |
| 10 | CUST001,CHECKING,20JAN2023,350.75 |
| 11 | CUST002,CHECKING,28JAN2023,85.00 |
| 12 | CUST001,SAVINGS,10FEB2023,55.00 |
| 13 | CUST001,CHECKING,12FEB2023,1300.00 |
| 14 | CUST002,SAVINGS,15FEB2023,500.00 |
| 15 | CUST002,CHECKING,25FEB2023,250.25 |
| 16 | CUST001,SAVINGS,05MAR2023,95.20 |
| 17 | ; |
| 18 | RUN; |
Étapes de réalisation
1
Load the source table into CAS memory.
Copied!
| 1 | |
| 2 | PROC CASUTIL; |
| 3 | load |
| 4 | DATA=casuser.customer_transactions outcaslib='casuser' casout='customer_transactions' replace; |
| 5 | QUIT; |
| 6 |
2
Execute the aggregate action to summarize transactions by CustomerID, AccountType, and Month.
Copied!
| 1 | PROC CAS; |
| 2 | aggregation.aggregate / |
| 3 | TABLE={name='customer_transactions', groupBy={'CustomerID', 'AccountType'}}, |
| 4 | id='TransactionDate', |
| 5 | interval='MONTH', |
| 6 | align='BEGINNING', |
| 7 | varSpecs={{name='Amount', agg='sum mean n'}}, |
| 8 | casOut={name='monthly_summary', caslib='casuser', replace=true}; |
| 9 | RUN; |
| 10 | QUIT; |
3
Verify the aggregated output.
Copied!
| 1 | |
| 2 | PROC PRINT |
| 3 | DATA=casuser.monthly_summary; |
| 4 | title 'Monthly Transaction Summary by Customer and Account Type'; |
| 5 | RUN; |
| 6 |
Expected Result
The output table 'monthly_summary' should contain one row for each unique combination of CustomerID, AccountType, and month. Each row will display the total sum, mean, and count of transaction amounts for that group, with the timestamp aligned to the beginning of each month.