ICETOOL SELECT FIRST / LAST to get largest / smallest values within a set
Suppose we have a file with Branch name, Account number, Transaction amounts. like,
Branch Account-num Transaction-Amount ----+----1----+----2----+----3----+----4----+ Chennai 10011 00523 Chennai 10011 00010 Bangalore 10011 00056 Bangalore 10011 00670 Chennai 10012 00200 Chennai 10012 00235 Chennai 10012 00750 Bangalore 10012 00750 Bangalore 10012 00034
and, we want to get the maximum value transactions for every account. like,
Branch Account-num Transaction-Amount ----+----1----+----2----+----3----+----4----+ Bangalore 10011 00670 Bangalore 10012 00750 Chennai 10011 00523 Chennai 10012 00750
this can be done using ICETOOL as,
//TOOLIN DD * SELECT FROM(INFILE) TO(OUTFILE) ON(BRANCH) ON(ACCOUNT) - FIRST USING(CTL1) /* //CTL1CNTL DD * SORT FIELDS=(BRANCH,A,ACCOUNT,A,TRANS-AMT,D) /* //SYMNAMES DD * BRANCH,1,10,CH ACCOUNT,11,5,ZD TRANS-AMT,21,5,ZD /*
In the above ICETOOL step, we are sorting the file in the order of Branch name (Ascending), Account number (Ascending), Transaction amount (Descending).
(If you want to select the smallest/minimum value transactions only, then sort the Transaction amount in Ascending order).
Then for each Branch and Account, we are selecting the first record only.
what are SYMNAMEs?
Use this code 