SORT Card to SUM up the Values, eliminate the Duplicates

SORT CARD, SUM Fields Option is used to do the following functionalities:

• Eliminate the Duplicates based on a specific field/vale position.
• Eliminate the duplicates from the entire record.
• SUM Up the values of specific positions while eliminating the Duplicates.
• SUM up the values of specific positions and place the Sum in a different position while eliminating the Duplicates.

Eliminate the duplicates

For the below sample Transactional file, for each customer number(positions 1 to 4) multiple records exists and the last field is the transaction amount of that customer number on different days.

0001 Arjun1 Reddy 01000
0001 Arjun2 Reddy 02000
0002 Shrey1       10000
0003 Mahender1    00100
0002 Shrey2       09000
0003 Mahender2    00900

Here the requirement is to Sum Up the amounts on customer ID (1 to 4 bytes)& keep the single record if duplicate customer ID exists. So the output should be like below

0001 Arjun1 Reddy 03000
0002 Shrey2       19000
0003 Mahender1    01000

SORT CARD for the above requiement is

First it sorts based on the sort fields given in Ascending order & Sums up the amounts that are there in 18,5 positions for the same Key(in this case 1 to 4 are key fields). Removes the duplicates after summing up by keeping only record for the given sort fields position (in this case it is starting 1 to 4 positions).

EQUALS/NOEQUALS

If you observe the above output, the SUM is placed in the first record, for the 2nd record(0002), the sum is placed in the 2nd record(Shrey2). So here the order of the input records is not preserved.

EQUALS/NOEQUALS decides whether the order of the equal records will be
preserved as it was in the input(EQUALS) or will be arbitrary (NOEQUALS)

The default option is chosen when Sort is installed.The IBM-supplied default is NOEQUALS

Below is the syntax to mention OPTIONS

SUM Fields with OUTREC FIELDS
If the Output file needs to be formatted after eliminating the duplicates or Summing up the values, then OUTREC FIELDS can be used along with SUM Fields as below

With above OUTREC, it copies the 1 to 19 position of input record into Output record and places the summed value in the 25th position of Output.

Eliminate the Duplicates without Sum