Inserting sequence numbers

Suppose you want to show the total price for all of the books associated with each course department, but you need the list in its original course department order rather than sorted by course department. In order to get the totals, you need to SORT by the department field, and SUM the price field, using the following statements:

  INREC FIELDS=(110,5,170,4)
  SORT FIELDS=(1,5,CH,A)
  SUM FIELDS=(6,4,BI)

Table 1 shows the result of these statements.

Table 1. Total book prices by course
Department	Total Price
1 5	6 9
BIOL BUSIN COMP ENGL HIST PSYCH	10195 2350 4275 10253 4640 3785 4800

Because you had to SORT in order to SUM (remember that you cannot SUM with COPY), the list is in sorted order by course department instead of the original order of COMP, blank, BUSIN, ENGL, HIST, PSYCH, and BIOL you need. To get back the original order, you can add a sequence number to each record before the records are sorted, and use a second step to sort the records back into their original order by that sequence number. You can remove the sequence numbers at the end of the second step.

You can use the previous statements with a slightly modified INREC statement to add the sequence number for the first step:

  INREC FIELDS=(110,5,170,4,SEQNUM,8,ZD)
  SORT FIELDS=(1,5,CH,A)
  SUM FIELDS=(6,4,BI)

The sequence numbers start at 00000001 for the first record and are incremented by 00000001 for each subsequent record. This allows you to sort the summed records back into their original course department order by the sequence number.

Table 2 shows the result of this first step. The output records are stored in a temporary data set.

Table 2. Total Book Prices by Course with Sequence Numbers
Department	Total Price	Sequence Number
1 5	6 9	10 17
BIOL BUSIN COMP ENGL HIST PSYCH	10195 2350 4275 10253 4640 3785 4800	00000002 00000017 00000003 00000001 00000005 00000011 00000013

You can use the following statements for the second step to sort by the sequence number, display the data in a meaningful way, and remove the sequence numbers. The input for this second step is the temporary data set you created in the first step.

  SORT FIELDS=(10,8,CH,A)
  OUTREC FIELDS=(1,5,CHANGE=(16,
        C'HIST',C'History',
        C'BUSIN',C'Business',
        C'COMP',C'Computer Science',
        C'ENGL',C'English',
        C'BIOL',C'Biology',
        C'PSYCH',C'Psychology'),
       NOMATCH=(C'Unaffiliated'),
      C' | ',
      6,4,BI,EDIT=($III,IIT.TT))

The output records from this second step are:

Computer Science |     $102.53
Unaffiliated     |     $101.95
Business         |      $42.75
English          |      $46.40
History          |      $37.85
Psychology       |      $48.00
Biology          |      $23.50

You can use SEQNUM,m,f to create sequence numbers of various lengths in various formats. You can use START=j to start the sequence numbers at j instead of 1. You can use INCR=i to increment each sequence number by i instead of 1. You can use RESTART=(p,m) to start the sequence number at the starting value again each time the value in a particular field changes.

Suppose you have the following input records:

New York       Albany
California     Morgan Hill
New York       Buffalo
Arizona        Tuscon
California     San Jose
New York       Poughkeepsie
Arizona        Phoenix
California     Davis
New York       Armonk

If you wanted to sort the records by the State field in positions 1-15 and by the City field in positions 16-30, and add a third field with a sequence number starting from 1000 and incrementing by 10, you could use START=1000 and INCR=10 as shown in the following statements:

  SORT FIELDS=(1,30,CH,A)
  OUTREC OVERLAY=(32:SEQNUM,5,ZD,START=1000,INCR=10)

The output records would look like this:

Arizona        Phoenix         01000
Arizona        Tuscon          01010
California     Davis           01020
California     Morgan Hill     01030
California     San Jose        01040
New York       Albany          01050
New York       Armonk          01060
New York       Buffalo         01070
New York       Poughkeepsie    01080

Note that each record has a different sequence number.

If you wanted to start the sequence number over at 1000 again each time the State changed, you could add RESTART=(1,15) as shown in the following statements:

  SORT FIELDS=(1,30,CH,A)
  OUTREC OVERLAY=(32:SEQNUM,5,ZD,START=1000,INCR=10,
    RESTART=(1,15))

The output records would look like this:

Arizona        Phoenix         01000
Arizona        Tuscon          01010
California     Davis           01000
California     Morgan Hill     01010
California     San Jose        01020
New York       Albany          01000
New York       Armonk          01010
New York       Buffalo         01020
New York       Poughkeepsie    01030

Note that the sequence number starts over at 1000 again each time the State changes.

If you specify an IFTHEN clause with a sequence number, the sequence number is only incremented for the subset of records selected by that IFTHEN clause. For example, if you specified the following statements for the input records shown previously:

  OPTION COPY
  OUTREC IFTHEN=(WHEN=(1,15,CH,EQ,C'Arizona'),
           OVERLAY=(32:SEQNUM,2,ZD)),
         IFTHEN=(WHEN=(1,15,CH,EQ,C'California'),
           OVERLAY=(32:SEQNUM,4,ZD)),
         IFTHEN=(WHEN=(1,15,CH,EQ,C'New York'),
           OVERLAY=(32:SEQNUM,3,ZD))

The output records would look like this:

New York       Albany          001
California     Morgan Hill     0001
New York       Buffalo         002
Arizona        Tuscon          01
California     San Jose        0002
New York       Poughkeepsie    003
Arizona        Phoenix         02
California     Davis           0003
New York       Armonk          004

For complete details about creating sequence numbers with INREC, OUTREC and OUTFIL OUTREC, see z/OS DFSORT Application Programming Guide.