DFSORT: Taming Wild and Unruly Data

5 Pages

SFF is a new format you can use for any type of signed free form data. SFF extracts decimal digits (0-9) from right to left anywhere in the field to form a positive or negative number. If a minus sign or right-parenthesis is found anywhere in the field, the number is treated as negative; otherwise, the number is treated as positive. Any combination of characters is valid, but characters other than 0-9, the minus sign, and the rightparenthesis are ignored.

SFF can be used in the SORT, MERGE, INCLUDE, OMIT, INREC, OUTREC and OUTFIL statements, and in the ON and BREAK parameters for all ICETOOL operators. Here are some of the ways you can use SFF for signed free form fields:

  • Sort or merge on the values. Example: 21,36,SFF,A
  • Compare the values. Example: 21,36,SFF,EQ,+150
  • Edit the values using pre-defined and user-defined patterns. Example: 21,36,SFF,M4
  • Convert the values to other formats. Example: 21,36,SFF,TO=PD
  • Use the values in arithmetic expressions. Example: 21,36,SFF,ADD,61,28, SFF,M18
  • Calculate statistics for the values. Example: TOT=(21,36,SFF,TO=FI)
  • Handle duplicates for the values. Example: SELECT with ON(21,36,SFF).

SFF is used in the SORT statement as the format for the amount1 values so they can be sorted correctly.

SFF is used in the OUTREC statement as the format for the amount1 and change values so they can be added together to create the new amount2 value. M4.LENGTH=12 is used to edit the resulting amount2 value with the M4 pattern and give it an output length of 12 bytes. The new edited amount2 value overlays the original change value.

SFF is used in the OUTFIL statement as the format for the amount1 values so their average can be calculated. M4,LENGTH=10 is used to edit the resulting amount1 average with the M4 pattern and give it an output length of 10 bytes. SFF also is used in the OUTFIL statement as the format for the amount2 values so their average can be calculated. M4,LENGTH=12 is used to edit the resulting amount2 average with the M4 pattern and give it an output length of 12 bytes.

Note that SFF treats values with a minus sign (found in amount1 and amount2) or a right-parenthesis (found in change) as negative numbers.

Unsigned Free Form Format (UFF)

UFF is a new format you can use for any type of unsigned free form data. UFF extracts decimal digits (0-9) from right to left anywhere in the field to form a positive number. Any combination of characters is valid, but characters other than 0-9 are ignored.

UFF can be used in the SORT, MERGE, INCLUDE, OMIT, INREC, OUTREC and OUTFIL statements, and in the ON and BREAK parameters for all ICETOOL operators. Here are some of the ways you can use UFF for unsigned free form fields:

  • Sort or merge the values. Example: 101,20,UFF,D
  • Compare the values. Example: 101,20,UFF,GT,1,20,UFF
  • Edit the values using pre-defined and user-defined patterns. Example: 101,20,UFF,M11
  • Convert the values to other formats. Example: 101,20,UFF,TO=BI
  • Use the values in arithmetic expressions. Example: 101,20,UFF,MUL, +20,M10
  • Calculate statistics for the values. Example: MAX=(101,20,UFF,TO=FS)
  • Handle duplicates for the values. Example: OCCUR with ON(101,20,UFF,G1).

UFF is used in the OUTREC statement as the format for the id values so just their digits can be extracted. Note that the dashes in the id values are ignored with UFF. EDIT=(TTT-TTTTT) is used to edit the resulting digits with the TTT-TT-TTT pattern to add leading zeros and dashes. LENGTH=12 is used to expand the output values to 12 bytes and right-align them.

5 Pages