Wednesday, May 1, 2019

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.

No comments:

Post a Comment

Follow by Email

Total Pageviews