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

SAS Visual Scenario Designer 6.3 M1 Windows: The Aggregation Window

by SAS Employee BenDavidson on ‎11-17-2015 09:18 AM - edited on ‎02-17-2016 04:00 PM by SAS Employee kimy_sas (387 Views)

The Aggregation Window

In the previous article, you were introduced to each type of window in SAS Visual Scenario Designer 6.3 M1, and you learned how the transformation window analyzes raw data to create detail data. This article introduces you to the aggregation window, and explains how it analyzes your raw data or detail data.

 

Here's a recap of the raw data that we're using for the examples in these articles:

 

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

 

Here again is the detail data that the transformation window outputs:

 

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

 

How then does an aggregation window in SAS Visual Scenario Designer 6.3 M1 work? It summarizes raw or detail data across distinct category values, such as member ID, age or transaction type. The resulting data set includes one row for each unique combination of the category values.

 

An aggregation window uses group-by columns to aggregate, or segment, data into groups based upon the unique value in each group-by column. For example, selecting age and gender creates a grouping of age, and within age, a grouping of gender. Group-by columns are synonymous with partition keys, and are the level to which the data is aggregated. When you select one or more group-by columns, the resulting data set includes one row for each unique group-by column value. For example, if you applied a group-by column of day of the week, then you would have a data set of seven rows – one for each day of the week. The values that are associated with the group-by column or columns are automatically included in the resulting data set.

 

The legend below enables you to understand the elements in the animated image that follows.

 

Figure 3: Legend

Legend v3.png

 

 

In the following image, a group-by column of Member ID has been applied to the detail data (as shown in Figure 2). As a result, Member ID is the group-by column that aggregates the data by Deposit Amount Sum and by Transaction Count.

 

Figure 4: Aggregating by Member ID

Aggregation_Window.gif

Figure 5 shows the aggregated Deposit Amount Sum and Transaction Count results from an aggregation window.

 

Figure 5: Aggregating Deposit Amount and Counting Transactions by Member ID

Member ID

Deposit Amount Sum

Transaction Count

1

$4,000

1

2

$32,000

7

3

$8,000

1

4

$12,000

1

 

You might use an aggregation window to find total cash deposits per member that occur within the filter criteria.

 

This is article #2 in a five-part series. See the links below for the additional articles:

Part 1: Overview and Transformation Window

Part 3: Date-based Aggregation Window

Part 4: Transaction Window

Part 5: Knowing Which Window Type to Choose

Contributors
Your turn
Sign In!

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