Business Context
A national retail chain wants to analyze daily sales performance using a 3-day sliding window to smooth out daily fluctuations. The analysis must handle days where sales data might be missing for some stores. Additionally, they want to identify which product categories contributed to the sales within each 3-day window to understand sales drivers.
About the Set : aggregation
Data aggregation and summary statistical calculations.
Discover all actions of aggregation Data Preparation
Creation of a daily sales table with some missing sales values (represented by '.') and a contributing product category variable.
Copied!
| 1 | DATA casuser.daily_store_sales; |
| 2 | LENGTH StoreID $ 8 ProductCategory $ 20; |
| 3 | FORMAT SaleDate date9.; |
| 4 | INFILE DATALINES delimiter=','; |
| 5 | INPUT StoreID $ SaleDate :date9. DailySales ProductCategory $; |
| 6 | DATALINES; |
| 7 | STORE_01,01JUN2023,12500,Electronics |
| 8 | STORE_01,02JUN2023,14000,Apparel |
| 9 | STORE_02,02JUN2023,9500,Groceries |
| 10 | STORE_01,03JUN2023,11000,Electronics |
| 11 | STORE_02,03JUN2023,., |
| 12 | STORE_01,04JUN2023,15500,Home Goods |
| 13 | STORE_02,04JUN2023,11200,Groceries |
| 14 | STORE_01,05JUN2023,., |
| 15 | STORE_02,05JUN2023,10500,Apparel |
| 16 | STORE_01,06JUN2023,13000,Apparel |
| 17 | STORE_02,06JUN2023,11800,Electronics |
| 18 | ; |
| 19 | RUN; |
Étapes de réalisation
1
Load the sales data, which includes missing values, into CAS.
Copied!
| 1 | |
| 2 | PROC CASUTIL; |
| 3 | load |
| 4 | DATA=casuser.daily_store_sales outcaslib='casuser' casout='daily_store_sales' replace; |
| 5 | QUIT; |
| 6 |
2
Execute aggregation with a 3-day sliding window, including missing values in calculations, and capturing contributing product categories.
Copied!
| 1 | PROC CAS; |
| 2 | aggregation.aggregate / |
| 3 | TABLE={name='daily_store_sales', groupBy={'StoreID'}}, |
| 4 | id='SaleDate', |
| 5 | interval='DAY', |
| 6 | windowInt='3 DAY', |
| 7 | includeMissing=false, |
| 8 | doESP=true, |
| 9 | contribute='ProductCategory', |
| 10 | contributeUnroll=true, |
| 11 | contributeDelimiter=', ', |
| 12 | varSpecs={{name='DailySales', agg='mean n'}}, |
| 13 | casOut={name='sales_sliding_window', caslib='casuser', replace=true}; |
| 14 | RUN; |
| 15 | QUIT; |
3
Print the results to verify the sliding window calculation and the concatenated list of contributing product categories.
Copied!
| 1 | |
| 2 | PROC PRINT |
| 3 | DATA=casuser.sales_sliding_window; |
| 4 | title '3-Day Sliding Window Sales Analysis'; |
| 5 | RUN; |
| 6 |
Expected Result
The output table 'sales_sliding_window' will show the rolling 3-day average sales and transaction count for each store. The 'includeMissing=false' ensures that missing daily sales do not affect the mean calculation. Crucially, a new column (named 'ProductCategory' by default) will be created, containing a comma-separated list of the product categories from the records that fall within each 3-day window, demonstrating the 'contribute' functionality.