Example 8
This example shows how you can use USING(xxxx) to supply an OMIT
statement to remove certain input records and an INREC statement to
reformat certain input records before SELECT processing begins.
SELECT FROM(INB) TO(OUTB) ON(8,10,CH) -
FIRSTDUP USING(BIRD)
Let's say the INB data set looks like
this: TYPE1 CROWS
TYPE1 PIGEONS
TYPE1 HAWKS
TYPE1 ROBINS
TYPE1 SPARROWS
TYPE1 CHICKENS
TYPE1 RAVENS
TYPE2 PIGEONS
TYPE2 ROBINS
TYPE2 HAWKS
TYPE2 TERNS
TYPE3 EAGLES
TYPE3 STARLINGS
We want to remove the TYPE3 records and then
select one record for each type of bird that appears in both a TYPE1
and a TYPE2 record. We could use the following control statements
in BIRDCNTL: * Remove the TYPE3 records.
OMIT COND=(1,5,CH,EQ,C'TYPE3')
* Reformat the TYPE2 records to place the bird name in
* same place as in the TYPE1 records so we can match them.
INREC IFTHEN=(WHEN=(1,5,CH,EQ,C'TYPE2'),
BUILD=(8:18,10))
The OUTB data set would look like
this: TYPE1 HAWKS
TYPE1 PIGEONS
TYPE1 ROBINS