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))
/*
001 3 150
001 2 120
001 1 100
002 2 140
002 3 250
003 1 050
003 3 920
004 3 005
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.
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').
001 100 120 150
002 140 250
003 050 920
004 005
001 150
002 140
003
004 005