Summary Record Generation

Two Ways to Generate Summary Information

DFSORT gives you two main mechanisms for summary information:

• SUM FIELDS= — Produces one data record per group. You sort by a key (e.g. department); records with the same key form a group. SUM adds (or aggregates) specified numeric fields across the group and outputs one record per group. That output is a normal dataset of summary records—same record layout, but fewer records (one per key value). Non-summed fields usually come from the first record of the group.
• OUTFIL TRAILER1= and TRAILER3= — Produce trailer lines that are not part of the main data record stream in the same way. TRAILER1 is written once at the end of the report and typically holds the grand count and grand total. TRAILER3 is written after each section when you use SECTIONS=, so you get a subtotal line per control-break group. In both you use COUNT= for record count and TOTAL= (or TOT=) for the sum of a numeric field.

Use SUM when you need a summarized dataset (e.g. one row per department with total sales). Use TRAILER1/TRAILER3 when you are building a report and want summary lines at the end or after each section.

Summary Records with SUM FIELDS=

SUM runs after the sort. The sort key defines the group: every record with the same value in the sort key belongs to the same group. SUM FIELDS= lists the numeric fields to sum (position, length, format for each). For each group, DFSORT adds those fields across all records in the group and writes one output record. The summed fields in that record contain the total; other fields (e.g. department code, name) typically come from the first record of the group. So you get a summary record per distinct key value.

Example: input has department (1,5), amount (10,6,PD). Sort by department and sum amount:

1
2
  SORT FIELDS=(1,5,CH,A)
  SUM FIELDS=(10,6,PD)

Output: one record per department. Position 1–5 is the department code (from the first record of the group), position 10–15 is the sum of the amount for that department. So the output dataset is a set of summary records.

If you use SUM FIELDS=NONE, you are not summing any numeric fields—you are just keeping one record per group (deduplication). That is still a form of summary: one representative record per key. So summary record generation via SUM can mean “one record per group with sums” or “one record per group (dedup only).”

TRAILER1: Grand Total at End of Report

TRAILER1 is the report trailer. It is written once, after all data records (and after any TRAILER3 lines for the last section). In TRAILER1 you typically put the grand total: total record count and/or sum of one or more numeric fields. Syntax is part of OUTFIL:

1
2
3
  OUTFIL FNAMES=OUTDD,
    TRAILER1=('TOTAL RECORDS: ',COUNT=(M11,LENGTH=7),
      '  TOTAL: ',TOTAL=(40,6,PD,LENGTH=12))

COUNT= inserts the number of data records. The format (e.g. M11) and LENGTH control how the number is edited (commas, width). TOTAL=(start,len,format,LENGTH=outlen) sums the numeric field at the given start position and length, in the given format (PD packed decimal, ZD zoned decimal, BI binary), and writes the result in the trailer formatted to outlen characters. So you get one summary line at the end with “TOTAL RECORDS: nnnnnnn TOTAL: xxxxxxxxxxxx”.

TRAILER1 can contain literals, multiple COUNT= and TOTAL= items, and fixed positions. It does not change the data records; it appends one or more trailer lines (or a trailer block) to the report.

TRAILER3: Section Subtotal (with SECTIONS=)

When you use SECTIONS= you define control-break groups (e.g. by department). After the last data record of each section, DFSORT can write a TRAILER3 line. So you get a subtotal after each group—count and/or total for that section only. TRAILER3 is specified inside the SECTIONS= parameter:

1
2
3
4
5
6
  OUTFIL FNAMES=OUTDD,
    SECTIONS=(1,5,SKIP=2L,
      TRAILER3=(1:'Dept ',1,5,'  Count: ',COUNT=(M11,LENGTH=6),
        '  Total: ',TOTAL=(10,6,PD,LENGTH=12))),
    TRAILER1=(1:'GRAND TOTAL  ',COUNT=(M11,LENGTH=10),
      '  ',TOTAL=(10,6,PD,LENGTH=14))

Here the section key is bytes 1–5 (e.g. department). After each department, TRAILER3 prints a line with the department code (1,5), the count of records in that section, and the sum of the 6-byte PD field at position 10. At the very end, TRAILER1 prints the grand total count and sum. So summary record generation for sections is done with TRAILER3; the grand summary is TRAILER1.

COUNT= and TOTAL= in Trailers

In both TRAILER1 and TRAILER3 you use the same building blocks:

The format in TOTAL= must match the data: PD for packed decimal, ZD for zoned decimal, BI for binary. Wrong format can cause incorrect totals or abends. The start and length refer to the position in the input (or INREC) record.

IFTRAIL: Updating an Existing Trailer Record

Sometimes the input file already has a trailer record—for example a fixed-format line with “T” in column 1 and reserved positions for “record count” and “total amount.” You do not want to append a new line; you want to update that existing record with the actual count and total. That is what IFTRAIL does.

You identify the trailer record with TRLID= using a logical expression (e.g. position 1, length 1, character, equal to 'T'). You specify what to update with TRLUPD=: at which positions to put COUNT= and/or TOT= (or TOTAL=). If the first record of the file is a header that should not be counted or summed, use HD=YES. DFSORT then updates the identified trailer record in place and writes it to the output; the rest of the file is processed as usual.

1
2
3
4
  OUTFIL FNAMES=OUTDD,
    IFTRAIL=(HD=YES,
      TRLID=(1,1,CH,EQ,C'T'),
      TRLUPD=(21:COUNT=(M11,LENGTH=7),35:TOTAL=(40,6,PD,LENGTH=12)))

Here the trailer is the record with 'T' in position 1. TRLUPD updates position 21 with the record count (7 characters) and position 35 with the sum of the 6-byte PD field at 40, formatted to 12 characters. So the output file has the same trailer record layout, but with real values filled in—another form of summary record generation.

Choosing Between SUM and Trailer Summary

Use SUM when your goal is a summarized dataset: one record per key with totals in specified fields, to be used as input to another step or as a final extract. Use TRAILER1/TRAILER3 when your goal is a report with summary lines at the end or after each section. Use IFTRAIL when the output file must preserve an existing trailer layout and you only need to fill in the count and total values.

Explain It Like I'm Five

Imagine you have a list of lemonade sales: each line says which stand and how much they sold. A summary record could be: one line per stand that says “Stand A total: $50.” That’s like SUM—squish all the lines for the same stand into one line with the total. Or the summary could be an extra line at the bottom of the list: “TOTAL STANDS: 3 TOTAL MONEY: $120.” That’s like TRAILER1—one line at the end with the big totals. And if your list already had a line at the bottom that said “TOTAL: …….” with empty spaces, and you want to fill in those spaces with the real numbers, that’s like IFTRAIL—update the line that’s already there.

Exercises

1. Write SORT and SUM control statements to produce one summary record per department (bytes 1–5) with the sum of a 6-byte packed decimal amount at position 20.
2. Add a TRAILER1 to an OUTFIL that prints “TOTAL RECORDS: ” followed by the record count and “ SUM: ” followed by the sum of the field at 20,6,PD. Use LENGTH= for the numeric output.
3. What is the difference in scope between COUNT= in TRAILER1 and COUNT= in TRAILER3?
4. When would you use IFTRAIL instead of TRAILER1? Give an example input layout.