SYNCSORT: SUM FIELDS

SYNCSORT is one of the more powerful tools on the mainframe. One of its features is it can sum up a specific column in your file based on some keys. Say you have a file that contains items sold and number of items sold per transaction as seen below:

Chips	1
Chips	3
Cookies	3
Cookies	2
Chocolate Bar	5
Water	4
Soda	5
Chips	1
Cookies	4
Cookies	1
Chocolate Bar	2
Soda	2

You want to find out how many of each items were sold to produce the output below:

Chips	5
Cookies	10
Chocolate Bar	7
Water	4
Soda	7

You do this using the SUM FIELDS statement in SYNCSORT. Let's say the first column is 20 characters and the second column is 4 columns. You first select the field you will use as the key. In this case, it is the items which is from column 1-20. The number of items sold is in column 21-24.

Your statements will be like this:

  SORT FIELDS=(1,20,CH,A)
 SUM FIELDS=(21,4,ZD)

These statements will sum up columns 21-24 and put that value across the key.

You can also use the SUM FIELDS statement to remove duplicates. The following command will delete any record with duplicate keys:

 SORT FIELDS=(1,20,CH,A)
 SUM FIELDS=NONE

When SUM FIELDS Does not Work as Expected

Sometimes, the SUM FIELDS will not work as expected. Say if your sum fields is as our example, 4 bytes and the total as it accumulates goes beyond 4 bytes, SYNCSORT will print out one line with the total and reset its counter and start accumulating again.

So if you have say 3 records each with the value of 5000 for a four byte field, the total will be 15000. Since adding two records will produce 10000, which is beyond 4 bytes, SUM FIELDS will produce 3 records, each with the value of 5000.

To fix this, you will need to expand your SUM FIELD length to at least 5 instead of 4.