SPLICE – Join records with missing fields

SPLICE normally requires reformatting the records of two or more data sets so they can be JOINED. In other words, SPLICE specified fields together from records that have the same specified numeric or character field values (duplicate values) but with different information. Because SPLICE overlays the WITH fields from the overlay record to the base record using matching ON fields, it’s usually necessary to do some initial setup before using SPLICE, to ensure that:

• the ON fields are in the same positions in the base and overlay records
• the WITH fields in the overlay records are in the positions they will occupy in the base records
• the base records and overlay records are the same length. This is always required for fixed-length records and is required for variable-length records unless VLENMAX or VLENOVLY is specified.

                                 .-----------.   
                                   V           |   
 >>-SPLICE--FROM(indd)--TO(outdd)----ON(p,m,f)-+----------------->
    .-----------.                                               
    V           |                                               
 >----WITH(p,m)-+--+----------+--+------------+--+----------+---->
                   +-WITHALL--+  '-KEEPNODUPS-'  '-KEEPBASE-'   
                   +-WITHANY--+                                 
                   '-WITHEACH-'                                 
 >--+-------------+--+-------------+--+-------+--+----------+---><
    '-USING(xxxx)-'  '-VSAMTYPE(x)-'  '-UZERO-'  +-VLENMAX--+   
                                                 '-VLENOVLY-' 

SPLICE Required Operands

FROM –Specifies DDNAME of the input data set.

TO – Specifies the DDNAME of the output data set for the selected records.

ON – Specifies a field to be used for this operation. From 1 to 10 ON fields can be specified.

– (p,m,f) gives the position, length, and format of a numeric or character field. A field must not extend beyond position 32752 or the end of the record.

WITH(p,m) – Specifies a field to be spliced from the second or subsequent duplicate record to the first duplicate record. From 1 to 50 WITH fields can be specified. (p,m) gives the position and length of the field.

SPLICE Optional Operands

WITHALL – Can be used to create multiple spliced records for each set of duplicates. The first duplicate is spliced with the specified fields from the second duplicate. Then the first duplicate is spliced with the specified fields from the third duplicate, and so on.

WITHANY – Can be used to create one spliced record for each set of duplicates. The first duplicate is spliced with the nonblank values of each subsequent duplicate for specified fields.

WITHEACH – Can be used to create one spliced record for each set of duplicates. The first duplicate is spliced with one specified field from each subsequent duplicate.

KEEPNODUPS – Can be used to keep the non-duplicate records as well as the spliced records. The non-duplicate records will be unchanged.

KEEPBASE – Can be used to keep the base records (first duplicate) as well as the spliced records. The base records will be unchanged.

VSAMTYPE – Specifies the record format for a VSAM input data set (F or V).

UZERO – Causes -0 to be treated as unsigned, that is, as +0.

USING – Specifies the first 4 characters of the DDNAME (xxxxCNTL) for the DFSORT control statement data set.

Example: Create one spliced record for each match in two files: Splice data together for each pair of records with the same ON field in two different input data sets.

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD *
 SL1 POSN1 T01
 SL1 POSN4 T01
 SL2 POSN5 T02
 SL1 POSN6 T01
 SL2 POSN3 T02
/*
//IN2 DD *
 POSN1 27M $100 SAN JOSE   CA
 POSN2 08M $200 BOSTON     MA
 POSN3 10M $300 BUFFALO    NY
 POSN4 50M  $50 NEWARK     NJ
/*
//TEMP1 DD DSN=&&TEMP1,
//      DISP=(MOD,PASS),
//      SPACE=(TRK,(5,5)),UNIT=SYSDA
//COMBINE DD SYSOUT=*
//TOOLIN   DD *
 * Reformat the File1 records for splicing
   COPY FROM(IN1) TO(TEMP1) USING(CTL1)
 * Reformat the File2 records for splicing
   COPY FROM(IN2) TO(TEMP1) USING(CTL2)
 * Splice the needed data from File1 and File2 together
   SPLICE FROM(TEMP1) TO(COMBINE) ON(5,5,CH) WITH(15,11)
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,14,      file1 data
                 25:X)      add blanks for spliced file2 data
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(5:1,5, put file2 key in same place as file1 key 
                15:7,8, file2 data
                30:33,2) file2 data
/* 

Base records originate from the IN1 data set. The reformatted TEMP1 records are 31 bytes long and look like this:

 SL1 POSN1 T01
 SL1 POSN4 T01
 SL2 POSN5 T02
 SL1 POSN6 T01
 SL2 POSN3 T02 

The overlay records originate from the IN2 data set. The reformatted TEMP1 records are 31 bytes long and look like this:

 POSN1     27M $100 CA
 POSN2     08M $200 MA
 POSN3     10M $300 NY
 POSN4     50M  $50 NJ 

The records look like this after they are sorted on the 5,5, CH field, but before they are spliced. As a visual aid, the WITH fields in the overlay records are shown in bold.

 SL2 POSN5 T02
 SL2 POSN3 T02
     POSN3     10M $300 NY
 SL1 POSN4 T01
     POSN4     50M  $50 NJ
     POSN2     08M $200 MA
 SL1 POSN1 T01
     POSN1     27M $100 CA
 SL1 POSN6 T01 

The spliced COMBINE records are 31 bytes long and look like this:

 SL2 POSN3 T02 10M $300 NY
 SL1 POSN4 T01 50M  $50 NJ
 SL1 POSN1 T01 27M $100 CA 

Example: The requirement is to have an output record for each SLx value with the Nx value for Pos1, Pos2, and Pos3. If Posx is missing for a particular SLx value, the Nx value should be left blank.

So the output records should come as mentioned below: 

 SL1 N5 N2 N8 Comment1 
 SL2 N3 XX N9 Comment2 
 SL3 N0 N7 XX Comment3 
 SL4 N6 XX XX Comment4 
 SL5 XX N3 N8 Comment5 
 SL6 XX N4 XX Comment6 
*In the output X represents one SPACE 

This requirement can be fulfilled by the use of SPLICE with WITHANY. 

//S1 EXEC PGM=ICETOOL 
//TOOLMSG DD SYSOUT=* 
//DFSMSG DD SYSOUT=* 
//IN DD DSN=XXX.YYYY.ZZZZZ.INPUT         <—- input file 
//OUT DD DSN=XXX.YYYY.ZZZZZ.OUTPUT       <—- output file 
//TOOLIN DD * 
 * Splice records with matching SLx values into one record 
 * with nonblank Nx values for Pos1, Pos2 and Pos3. 
    SPLICE FROM(IN) TO(OUT) ON(1,3,CH) WITHANY KEEPNODUPS - 
    WITH(5,2) WITH(8,2) WITH(11,2) USING(CTL1)
/* 
//CTL1CNTL DD * 
 If record has 'Pos1', reformat it to: SLx Nx Commentx  
   INREC IFTHEN=(WHEN=(8,1,ZD,EQ,1), 
          BUILD=(1,3,5:10,2,14:14,8)), 
 If record has 'Pos2', reformat it to: 
 SLx Nx Commentx  
        IFTHEN=(WHEN=(8,1,ZD,EQ,2),  
         BUILD=(1,3,8:10,2,14:14,8)), 
 If record has 'Pos3', reformat it to: 
 SLx Nx Commentx    
       IFTHEN=(WHEN=(8,1,ZD,EQ,3), 
        BUILD=(1,3,11:10,2,14:14,8)) 
 /*  

INREC IFTHEN processing is used to reformat the Pos1, Pos2, and Pos3 records so the corresponding Nx value is in the correct place for splicing. After INREC processing, the intermediate records look like this: 

 Intermediate Result:
 SL1 N5 XX XX Comment1 
 SL1 XX N2 XX Comment1 
 SL1 XX XX N8 Comment1 
 SL2 N3 XX XX Comment2 
 SL2 XX XX N9 Comment2 
 SL3 N0 XX XX Comment3 
 SL3 XX N7 XX Comment3 
 SL4 XX XX N6 Comment4 
 SL5 XX N3 XX Comment5 
 SL5 XX XX N8 Comment5 
 SL6 XX N4 XX Comment6 
** XX - SPACE 

Note that the Nx value for a Pos1 record is in positions 5-6, the Nx value for a Pos2 record is in positions 8-9, and the Nx value for a Pos3 value is in positions 11-12. SPLICE splices the records with the same SLx value into one record. WITHANY tells SPLICE to keep any nonblank WITH field value from records with the same key. WITH(5,2), WITH(8,2), and WITH(11,2) specify the WITH fields. For example, for the SL2 records, the nonblank value (N3) in positions 5-6 of the first record, and the nonblank value (N9) in positions 11-12 of the second record is kept. Since there’s no nonblank value in positions 8-9, that WITH field is left blank. So nonblank fields are kept and missing fields are ignored. The resulting output is: 

Final Output:
 SL1 N5 N2 N8 Comment1 
 SL2 N3 XX N9 Comment2 
 SL3 N0 N7 XX Comment3 
 SL4 XX XX N6 Comment4 
 SL5 XX N3 N8 Comment5 
 SL6 XX N4 XX Comment6  

Example: Pull records from a master file in their original order as present in the original file.

We use the SEQNUM parameter to add a sequence number to each MASTER record before the records are spliced, and we splice that sequence number along with the data. After SPLICE sorts by the City Name, we SORT again by the sequence number to get the resulting MASTER records back in their original order.

Input - TEMP1
SAN JOSE                                P
NEW YORK                                P
DENVER                                  P
LOS ANGELES                             P


Input - TEMP2
SAN JOSE             8630  SUSAN        M
PHOENIX              7993  PAUL         M
LOS ANGELES          9203  MICHAEL      M
SAN JOSE             0052  VICKY        M
NEW YORK             5218  CARRIE       M
SAN JOSE             3896  FRANK        M
TUCSON               1056  LISA         M
NEW YORK             6385  MICHAEL      M
PHOENIX              5831  HOLLY        M

//S7        EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//PULL DD DSN=VAR.PULL.FILE,DISP=SHR
//MASTER DD DSN=FIXED.MASTER.FILE,DISP=SHR
//TEMP1  DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//TEMP2  DD DSN=&&TEMP2,DISP=(,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//OUT DD DSN=FIXED.OUTPUT.FILE,DISP=(NEW,CATLG,DELETE),
//       SPACE=(TRK,(5,5)),UNIT=SYSDA
//TOOLIN DD *
* Convert PULL records from VB to FB and add ’P’ identifier.
  COPY FROM(PULL) USING(CTL1)
* Add sequence number and ’M’ identifier to MASTER records.
  COPY FROM(MASTER) TO(TEMP1) USING(CTL2)
* Splice PULL and MASTER records (splice sequence number, but
* do NOT splice identifier):
* Spliced MASTER records with matching PULL records have ’P’ id.
* Spliced MASTER records without matching PULL records have ’M’ id.
* Eliminate records with ’M’ id.
  SPLICE FROM(TEMP1) TO(TEMP2) ON(1,20,CH) WITHALL WITH(1,48) -
  USING(CTL3)
* Sort resulting spliced records on original sequence number
* to get them back in their original order. Remove id and sequence number.
  SORT FROM(TEMP2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
* Convert PULL records from VB to FB and add ’P’ identifier
  OUTFIL FNAMES=TEMP1,VTOF,OUTREC=(5,20,49:C’P’)
/*
//CTL2CNTL DD *
* Add sequence number and ’M’ identifier to MASTER records.
  OUTREC FIELDS=(1,40,41:SEQNUM,8,BI,49:C’M’)
/*
//CTL3CNTL DD *
* Eliminate MASTER records without matching PULL records.
  OUTFIL FNAMES=TEMP2,OMIT=(49,1,CH,EQ,C’M’)
/*
//CTL4CNTL DD *
* Sort on sequence number and remove id and sequence number.
  SORT FIELDS=(41,8,BI,A)
  OUTREC FIELDS=(1,40)
/*

The resulting OUT data set (FIXED.OUTPUT.FILE) has the following 40-byte fixed-length records:

SAN JOSE        8630  SUSAN
LOS ANGELES     9203  MICHAEL
SAN JOSE        0052  VICKY
NEW YORK        5218  CARRIE
SAN JOSE        3896  FRANK
NEW YORK        6385  MICHAEL

IFTHEN with SYNCSORT – Click Here    SYNSORT Manual: Click Here