Audit Command Language – Summarize Statement




Welcome to another post in the ‘Audit Command Language Tutorial for Beginners’ series. We hope things are moving along nicely for all our readers. It has indeed been a long enough journey up to this point and it is certainly our hope that it has been fruitful so far.

We are going to continue with the posts exploring the Audit Command Language statements. In this post, we will be discussion the ‘Summarize’ statement.

The summarize statement performs a rather simple task but one which has quite an impact in any and all data analytics projects. Lets put it this way, if you haven’t found any major insights into your analytics projects, it simply means that the summarize statement has probably not come into play yet.

Before we discuss some of the most common uses of the statement, let us quickly go through the application portion i.e. syntax and GUI implementation.

Syntax:

OPEN PAYROLL
SUMMARIZE ON WorkDept SUBTOTAL Gross_Pay Net_Pay Tax_Amount OTHER Cheque_No TO “summ_test.FIL” OPEN PRESORT

It is important to understand what this statement is doing to get a grasp of the syntax. Firstly, it opens the table ‘Payroll’. Then the field on which the data set is to be summarized, will be provided. Here we are summarizing the data set on field ‘WorkDept’ i.e. the resulting table or view would only have the unique values present in the field ‘WorkDept’. The next part of the statement is listing fields (specifically numeric fields) to be totaled for each of the ‘WorkDept’ values in the data set. the PRESORT at the end of the statment indicates that the ‘Payroll’ data set is sort prior to the use of the statement. Please note, it is critical that a any data set be presorted before summarizing the same, else the results would be incorrect.

To summarize the resulting data set created from the above example would be displaying the total ‘Gross_Pay’, ‘Net_Pay’ & ‘Tax_Amount’ across each of the unique ‘WorkDept’ in the ‘Payroll’ data set.

To accomplish the same using the ACL GUI, please refer to screenshots below:

Go to Menu -> Analyze -> Summarize. Thereafter you should get a pop  up window. Update it as shown in the screenshots:

Please feel free to play around with the interface for this statement. It has a variety of options, all of which cannot be explored in just one post. We can create another post more specific to any specific queries that we may receive.

Additionally, you may prefer to the videos provided below for a demonstration of the summarize statement:
Part 1

Part 2

The above videos should provide even more clarity about the Summarize statement. This statement is a powerful statement because it provides the unique values in a specified column in a data set. Since this statement provides this specific information, it allows for numerous possibilities when combined joins and merges in the Audit Command Language tool overall. The scope of the analysis that can be undertaken simply by including this statement in your arsenal has more impact than all the functions combined. Not to get too dramatic about it, but once you grasp this concept well, you can pick it up very quickly in any scripting language very quickly and start being super productive from the start.

Try out these examples and keep experimenting. The more you fiddle around the more your learn.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.