The Transaction Window
The fourth type of SAS Visual Scenario Designer window is the transaction window. If you haven't yet read the previous articles that explain the behavior of the transformation window, aggregation window, and date-based aggregation window, it will be helpful to do so before proceeding. You'll learn concepts that will help you understand the information that follows.
Before we explain the particulars of how a transaction window behaves in SAS Visual Scenario Designer 6.3 M1, here again is the raw data and the detail data that are used in our examples:
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 the detail data has the filter criteria of transactions at or above $3,500 applied to it, which results in the following output from our raw data:
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
Figure 3 enables you to understand the elements in each animated slide that illustrate the behavior of a transaction window.
Figure 3: Legend
Similar to a date-based aggregation window, a transaction window also applies time-based lookbacks to aggregated data, but the transaction window differs in its output. Remember that 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.
In contrast, a transaction window outputs one row per incoming transaction. Transactions are aggregated using the group-by columns. Similar to the date-based aggregation window described previously, each variable has a lookback. With a transaction window however, the lookbacks are relative to the individual transactions themselves, and not to the anchor end datetime. Each transaction triggers a lookback period and aggregation from the timestamp of the current transaction.
As with a date-based aggregation window, you also set the number of time-based units, and you also define the value, or granularity, of the time unit. The difference is that a transaction window analyzes data to the millisecond level within that time unit. If you set DAYS as the time unit, then the date-based aggregation window does differentiate activity that occurred at different hours, minutes, seconds, and milliseconds within the day. For example, a one-day lookback from a timestamp value of 13 MAY 2014:02:12:47.123 aggregates data between 12 MAY 2014:02:12:47.124 and 13 MAY 2014:02:12:47.123. (123 and 124 represent millisecond values.)
A transaction window outputs an aggregation for each transaction in your raw data. However, transactions that don't meet your filter criteria are not included in the aggregation results. For example, the Member ID 1 transaction of $2,000 on 5/13/2014 4PM is not included in the results of the aggregations because it does not meet the filter criteria.
Note that in the following diagrams and tables, due to space considerations, the records for Member IDs 1, 3 and 4 are excluded, and the time units are at the HOURS level. The records for these Member IDs still meet the filter criteria, so the transaction window still analyzes and aggregates these records, down to the millisecond level, and still outputs those results.
Transactions at the HOURS Level
Figure 4 depicts Member ID 2 transactions from the input table that meet the filter criteria. The transaction window is aggregating Deposit Amount and counting the number of Transaction IDs by Member ID. In this example, you see the window analysis of Member ID 2 transactions that meet the filter criteria, using the HOURS time unit. The window then applies a 72-unit time-frame lookback that applies to each Member ID 2 transaction. A 72-hour lookback, down to the millisecond level within that hour, is applied starting from the Member ID 2 transaction that occurs at 5/12/2014 8AM.
Notice that there are two distinct 72-unit time-frame lookbacks on 5/1/2014 – one for the 12PM transaction and one for the 8AM transaction. Also note that the lookback period that begins at 5/4/2014 11AM includes both the 5/2/2014 1PM transaction and the 5/1/2014 12PM transaction in its lookback period.
Figure 4: Aggregating by Member ID and Applying a 72-Unit Lookback to Each Transaction Where the Time Unit is HOURS
Figure 5 shows the aggregated data from this transaction window. The Transaction Count number displays how many transactions occurred within the lookback period. A transaction count of 3 exists for 5/4/2014 because Member ID 2 performed a single transaction at 5/4/2014 11AM (the end time in the lookback period), at 5/2/2014 1PM (59 hours – or time units – prior to the end time), and then at 5/1/2014 12PM (71 hours – or time units – prior to the end time). The bold font indicates how this transaction window that uses HOURS as its time unit can differ from the next example, which is a transaction window that uses DAYS as its time unit: when using HOURS, the inclusion of the 5/1/2014 12PM transaction in the lookback period for the 5/4/2014 11AM transaction results in one additional transaction and a Deposit Amount Sum that is $4,000 greater. Compare Figure 5 with Figure 9 to see this difference.
Note that records in your raw data that do not meet the filter criteria are aggregated by the transaction window, but are not included in the aggregation results. Member ID 2 transactions of $2,000 on 5/10/2014 10AM and $1,000 on 5/8/2014 1PM are such examples. A Deposit Amount Sum and Transaction Count value of 0 is returned for each.
Figure 5: Aggregating Deposit Amount and Counting Transactions by Member ID with Time-based Lookbacks Using a Time Unit of HOURS for Every Transaction
Member ID
Transaction ID
Transaction Datetime
Deposit Amount Sum
Transaction Count
2
1
4/15/2014 8PM
$5,000
1
2
2
5/1/2014 8AM
$5,000
1
2
3
5/1/2014 12PM
$9,000
2
2
5
5/2/2014 1PM
$14,000
3
2
9
5/4/2014 11AM
$13,000
3
2
14
5/8/2014 1PM
$0
0
2
15
5/10/2014 10AM
$0
0
2
16
5/10/2014 5PM
$4,000
1
2
18
5/12/2014 8AM
$9,000
2
Transactions at the DAYS Level
What if you analyze Member ID transactions over three days, using the same filter, but this time using a 3-unit time-frame lookback where the unit is DAYS, and not HOURS? Because a transaction window analyzes data down to the millisecond and creates lookbacks for any transactions that occur within smaller, more granular time units, the results are identical.
But what if you want a transaction window to differentiate between and create lookbacks for member transactions only down to the level of the time unit (DAYS, HOURS, MINUTES or SECONDS) that you assigned, with no differentiation between lookbacks for transactions that occur at smaller, more granular timestamp variables? Assigning an equal value to those smaller timestamp variables accomplishes this. For example, if you selected a time unit of HOURS, then you would assign a value of 59 to MINUTES and SECONDS, and a value of 999 to milliseconds. The transaction window still analyzes detail data to the millisecond level, but it does not differentiate between transactions that occurred at MINUTES, SECONDS, or MILLISECONDS within the hour because you have assigned identical values to these time stamps.
Figure 6 shows 999 for the MILLISECOND value, 59 for the incrementally larger timestamp values (SECONDS and MINUTES) and 23 for HOURS, within the data set. It is preferable to use 999, 59, and 23 for the other timestamp values, not 000 or 00. This is because a transaction window is looking backward from the end date value, and not forwards from the start date value. XX in the table below represents the current timestamp value that you have selected as your time unit, which could be any valid value.
Making these adjustments means that the transaction window differentiates only between activity that occurs at the time unit that you define.
Figure 6: Time Unit Values
Time Unit
Value
DAYS
23:59:59.999
HOURS
XX:59:59.999
MINUTES
XX:XX:59.999
SECONDS
XX:XX:XX.999
With DAYS as the time unit, and with 23 for HOURS, 59 for MINUTES and SECONDS and 999 for MILLISECONDS, the transaction window effectively only uses the DAYS portion of the datetime value in its lookback period. In this window that outputs one row per transaction per unique value for each group-by column, the lookback periods in Figure 7 do not distinguish between the different hours that each transaction occurred.
Note: SAS LASR Analytic Server processes transactions within the same time unit in arbitrary order, which is not guaranteed to be consistent from run to run. For example, Transaction ID 2 and Transaction ID 3 (both of which occur on 5/1/2014) could be processed by SAS LASR Analytic Server either as 2 followed by 3, or as 3 followed by 2.
In Figure 7, notice that the lookback period for the 5/4/2014 transaction looks back to 5/2/2014, and therefore does not include the 5/1/2014 transaction that was included when HOURS was the time unit (as shown in Figure 4).
Figure 7: Aggregating by Member ID and Applying a 3-Unit Lookback to Each Transaction Where the Time Unit is DAYS
The table in Figure 9 shows the aggregated data from this window. The bold font for 5/4/2014 indicates how – when smaller, more granular time stamps are assigned identical values – this window that uses DAYS as its time unit differs from the previous window example that uses HOURS as its time unit: the omission of the 5/1/2014 12PM transaction results in a Transaction Count of 2 and a Deposit Amount Sum of $9,000. Compare these results with the results shown in the table in Figure 5.
Transactions in your raw data that do not meet the filter criteria do not participate in the aggregation results. In this example, Member ID 2 Transaction IDs 14 and 15 are such examples: $2,000 on 5/10/2014 10AM and $1,000 on 5/8/2014 1PM.
Recall that SAS LASR Analytic Server processes transactions that occur within the same time unit in arbitrary order., Because this transaction window effectively only uses the DAYS portion of the datetime value in its lookback period, the Deposit Amount Sum and Transaction Count for both 5/10/2014 Member ID 2 transactions can equal $4,000 and 1, respectively, depending on the order that SAS LASR Analytic Server processes them.
Here again are the 5/10/2014 transactions for Member ID 2, as they appear in the raw data in Figure 1:
Figure 8: 5/10/2014 Transactions for Member ID 2
Member ID
Transaction ID
Transaction Datetime
Deposit Amount
2
15
5/10/2014 10AM
$2,000
2
16
5/10/2014 5PM
$4,000
Of these two 5/10/2014 transactions, only the $4,000 transaction at 5PM meets the filter criteria, so that is the only 5/10/2014 Member ID 2 transaction that participates in the aggregation results. Hence the Deposit Amount Sum of $4,000 and the Transaction Count of 1. The table below shows the resulting Deposit Amount Sum and Transaction Count if SAS LASR Analytic Server processes the 5/10/2014 $4,000 transaction (Transaction ID 16) first.
Figure 9: Aggregating Deposit Amount and Counting Transactions by
Member ID with Time-based Lookbacks Using a Time Unit of DAYS for Every Transaction
Member ID
Transaction ID
Transaction Datetime
Deposit Amount Sum
Transaction Count
2
1
4/15/2014
$5,000
1
2
2
5/1/2014
$5,000
1
2
3
5/1/2014
$9,000
2
2
5
5/2/2014
$14,000
3
2
9
5/4/2014
$9,000
2
2
14
5/8/2014
$0
0
2
15
5/10/2014
$4,000
1
2
16
5/10/2014
$4,000
1
2
18
5/12/2014
$9,000
2
If SAS LASR Analytic Server processes the 5/10/2014 $2,000 transaction (Transaction ID 15) first, then the rows for Transaction IDs 15 and 16 would appear as follows:
Figure 10: 5/10/2014 Transactions for Member ID 2 If Processed in a Different Order
Member ID
Transaction ID
Transaction Datetime
Deposit Amount Sum
Transaction Count
2
16
5/10/2014
$4,000
1
2
15
5/10/2014
$0
0
Conclusion
You might use a transaction window to see the three-day sum of deposits per member for each transaction that occurs within the filter criteria.
This is article #4 in a five-part series. See the links below for the additional articles: Part 1: Overview and Transformation Window Part 2: Aggregation Window Part 3: Date-based Aggregation Window Part 5: Knowing Which Window Type to Choose
... View more