Example 4 - Create a matrix of values

This example shows how you can use the WITHANY operand to create a matrix of values. We combine multiple rows for different types of data with a common key into a single row of data for that key, even if some rows are missing.

//S4 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN  DD *
001 3  150 
001 2  120 
001 1  100 
002 2  140 
002 3  250 
003 1  050 
003 3  920 
004 3  005
/*
//OUT DD SYSOUT=*
//TOOLIN DD *
* Splice nonblank type 1 values (in 5-7), type 2 values (9-11) 
* and type 3 values (13-15) for each key.
SPLICE FROM(IN) TO(OUT) ON(1,3,CH) WITHANY KEEPNODUPS -          
  WITH(5,3) WITH(9,3) WITH(13,3) USING(CTL1)                     
/*
//CTL1CNTL DD *
* Before SPLICE:
* Reformat type 1 records to:
* 1   5
* key val
  INREC IFTHEN=(WHEN=(5,1,CH,EQ,C'1'),
          BUILD=(1,3,5:8,3)),
* Reformat type 2 records to:
* 1       9
* key     val
     IFTHEN=(WHEN=(5,1,CH,EQ,C'2'),
          BUILD=(1,3,9:8,3)),
* Reformat type 3 records to:
* 1           13
* key         val
     IFTHEN=(WHEN=(5,1,CH,EQ,C'3'),
          BUILD=(1,3,13:8,3))
/*

The input records look like this:

We have a key (for example, '001') in positions 1-3, a record type (1, 2 or 3) in position 5 and a numeric value in positions 8-10. We want to set up a single row for each key with the values for the three record types and blanks for missing record types.

Before we SPLICE the records, we use the INREC IFTHEN clauses to put the values from the type 1 records in positions 5-7, the values from the type 2 records in positions 9-11 and the values from the type 3 records in positions 13-15. The reformatted INREC records look like this:

001         150   
001     120       
001 100           
002     140       
002         250   
003 050           
003         920   
004         005

We use SPLICE with WITHANY and appropriate WITH fields to create one combined record for each key with the values for the record types. We use KEEPNODUPS to keep records for keys with only one value (for example, '004').

The OUT records are 15 bytes long and look like this:

001 100 120 150 
002     140 250 
003 050     920 
004         005

Note that if we used WITHEACH instead of WITHANY, the output would not be what we wanted since record types are missing for some keys (for example, type 1 is missing for key 002), and record types are out of order for some keys (for example, we have type 3, type 2 and type 1 in that order for key 001). With WITHEACH, the OUT records would look like this:

001         150    
002     140        
003                
004         005