We’re smarter together. Learn from this collection of community knowledge and add your expertise.

SAS Visual Scenario Designer 6.3 M1 Windows: The Transaction Window

by SAS Employee BenDavidson on ‎11-19-2015 11:19 AM - edited on ‎02-17-2016 04:15 PM by SAS Employee kimy_sas (429 Views)

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

Legend v3.png

 

 

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

Transaction_Hours_Window_small.gif

 

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

Transaction_Days_Window_small.gif

 

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

 

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.