SAS Visual Scenario Designer 6.3 M1 Windows: The Date-based Aggregation Window
The Date-based Aggregation Window
The aggregation window type described in the previous article can also apply time-based lookbacks to your data. Make sure to check out the previous articles in this SAS Visual Scenario Designer 6.3 M1 Windows article series to better understand the information that follows.
Before we explain the particulars of how a date-based aggregation window behaves in SAS Visual Scenario Designer 6.3 M1, here again is the raw data and the detail data that are used in the examples in this article:
Figure 1: Raw Data
|
Transaction ID |
Transaction Datetime |
Member ID |
Deposit Amount |
|
1 |
4/15/2014 8PM |
2 |
$5,000 |
|
2 |
5/1/2014 8AM |
2 |
$5,000 |
|
3 |
5/1/2014 12PM |
2 |
$4,000 |
|
4 |
5/1/2014 11PM |
3 |
$8,000 |
|
5 |
5/2/2014 1PM |
2 |
$5,000 |
|
6 |
5/3/2014 9AM |
1 |
$4,000 |
|
7 |
5/3/2014 5PM |
1 |
$2,000 |
|
8 |
5/3/2014 10PM |
3 |
$3,000 |
|
9 |
5/4/2014 11AM |
2 |
$4,000 |
|
10 |
5/4/2014 9PM |
3 |
$2,000 |
|
11 |
5/5/2014 11AM |
1 |
$3,000 |
|
12 |
5/6/2014 2PM |
1 |
$3,000 |
|
13 |
5/7/2014 10PM |
1 |
$2,500 |
|
14 |
5/8/2014 1PM |
2 |
$1,000 |
|
15 |
5/10/2014 10AM |
2 |
$2,000 |
|
16 |
5/10/2014 5PM |
2 |
$4,000 |
|
17 |
5/10/2014 8PM |
4 |
$12,000 |
|
18 |
5/12/2014 8AM |
2 |
$5,000 |
|
19 |
5/13/2014 4PM |
1 |
$2,000 |
Remember that our detail data applies a filter criteria of transactions at or above $3,500:
Figure 2: Detail Data from a Transformation Window
|
Transaction ID |
Transaction Datetime |
Member ID |
Deposit Amount |
|
1 |
4/15/2014 8PM |
2 |
$5,000 |
|
2 |
5/1/2014 8AM |
2 |
$5,000 |
|
3 |
5/1/2014 12PM |
2 |
$4,000 |
|
4 |
5/1/2014 11PM |
3 |
$8,000 |
|
5 |
5/2/2014 1PM |
2 |
$5,000 |
|
6 |
5/3/2014 9AM |
1 |
$4,000 |
|
9 |
5/4/2014 11AM |
2 |
$4,000 |
|
16 |
5/10/2014 5PM |
2 |
$4,000 |
|
17 |
5/10/2014 8PM |
4 |
$12,000 |
|
18 |
5/12/2014 8AM |
2 |
$5,000 |
And here again is the legend, which helps you to understand the animated images that display different types of date-based aggregation window behavior:
Figure 3: Legend
A date-based aggregation window analyzes your data from the anchor end datetime back through an adjustable number of time-based units, such as DAYS or HOURS. Total activity (in this example, Deposit Amount) for each unique value of a group-by column or columns that occur within the lookback period (that is, a time-based filter used for analyzing data) is the focus here.
The end time is assigned a default value of the latest date or datetime in the input data set. The default lookback period is 1 and each additional time unit that you assign has a value of 1. For example, a 3-unit time-frame lookback period that uses DAYS as its time unit looks back over a total of 3 days: the end time, or final day (1), and two days preceding that (2). Similarly, a 72-unit time-frame lookback period that uses HOURS as its time unit looks back over a total of 72 hours: the end time, or final hour (1); and seventy one hours preceding that (71). Each of these methods covers the same span of time, but each may return different results, as you will see in the following diagrams.
You set the number of time-based units (1, 2, 3, and so on), and you define the value, or granularity, of the time unit (DAYS, HOURS, MINUTES, and SECONDS). The date-based aggregation window only returns results at that level of granularity within that time unit. If you set HOURS as the time unit, then SAS Visual Scenario Designer does not differentiate activity that occurred at smaller time units (MINUTES, SECONDS) within the hour.
A date-based aggregation window outputs one row per unique value for each group-by column from the anchor end datetime through the lookback period. Note that if there are no rows for a given group-by column that meet the lookback criteria, then these group-by values are not included in the output.
Date-based Aggregation at the HOURS Level
Figure 4 depicts the behavior of a date-based aggregation window. It depicts Member ID transactions that meet the filter criteria of being at or above $3,500. The window is aggregating the Deposit Amount by Member ID. The window applies a 72-unit time-frame lookback period, where the time unit is HOURS, from the end date of 5/13/2014 4PM. Therefore, the window analyzes Member ID ATM transactions at the hour level from 5/13/2014 4PM through 5/10/2014 4PM.
Figure 4: Aggregating by Member ID and Applying a 72-Unit Lookback where the Time Unit is HOURS
Figure 5 shows the aggregated data from this window. Between 5/13/2014 4PM and 5/10/2014 4PM, the following transactions occurred (refer back to the table in Figure 1 if you need a reminder):
- Member ID 1 performed one transaction of $2,000 on 5/13/2014 4PM, which is below the filter criteria and therefore is not included in the aggregation results.
- Member ID 2 performed one transaction of $5,000 on 5/12/2014 8AM and one transaction of $4,000 on 5/10/2014 5PM.
- Member ID 4 performed one transaction of $12,000 on 5/10/2014 8PM.
The Deposit Amount Sum column returns the sum of the deposit amount for transactions that meet the filter criteria of deposit amount being at or above $3,500 for each Member ID. For Member ID 2, that means that the 5/13/2014 Deposit Amount Sum is the sum of the 2 transactions that occur within the lookback period: $5,000 on 5/12/2014 8AM, and $4,000 on 5/10/2014 5PM, for a deposit amount sum of $9,000. Because the Member ID 1 transaction of $2,000 does not meet the filter criteria, Member ID 1 has a Deposit Amount Sum of 0.
In a date-based aggregation window, every transaction within a lookback period gets the same anchor end datetime. In this example, that is 5/13/2014 4PM.
Figure 5: Aggregating Deposit Amount by Member ID Using a Lookback Time Unit of HOURS
|
Anchor Datetime |
Member ID |
Deposit Amount Sum |
|
5/13/2014 4PM |
1 |
$0 |
|
5/13/2014 4PM |
2 |
$9,000 |
|
5/13/2014 4PM |
4 |
$12,000 |
Date-based Aggregation at the DAYS Level
What happens if you analyze Member ID transactions over three days, using the same filter, but using a 3 unit time-frame lookback where the time unit is DAYS?
With DAYS as its time unit, the date-based aggregation window now only uses the day portion of the datetime value in its lookback period. Therefore, this window analyzes Member ID ATM transactions from 5/13/2014 (the end date, or 1) back through 5/11/2014 (the two days – or time units – preceding that). The 5/10/2014 transactions from Member ID 4 and Member ID 2 are now excluded. The diagram in Figure 6 depicts this difference in lookback behavior.
Figure 6: Aggregating by Member ID and Applying a 3-Unit Lookback where the Time Unit is DAYS
Figure 7 shows the aggregated data from this window. Between 5/13/2014 and 5/11/2014, the following transactions occurred:
- Member ID 1 performed one transaction of $2,000 on 5/13/2014, which is below the filter criteria of $3,500, and therefore is not included in the aggregation results.
- Member ID 2 performed one transaction of $5,000 on 5/12/2014.
Notice that both of the Member ID 2 and Member ID 4 transactions on 5/10/2014 are excluded.
Figure 7: Aggregating Deposit Amount by Member ID Using a Lookback Time Unit of DAYS
|
Anchor Datetime |
Member ID |
Deposit Amount Sum |
|
5/13/2014 |
1 |
$0 |
|
5/13/2014 |
2 |
$5,000 |
Conclusion
No matter what time unit you select, you might use a date-based aggregation window to see a summary of the total cash deposits for each bank member from the anchor date through a lookback period of three days.
This is article #3 in a five-part series. See the links below for the additional articles in this series:
Part 1: Overview and Transformation Window
Part 2: Aggregation Window
Part 4: Transaction Window
Special offer for SAS Communities members
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
