Example 23
OPTION COPY
OUTFIL IFTHEN=(WHEN=INIT,
OVERLAY=(5:5,18,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C''''),
34:34,19,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C''''))),
IFTHEN=(WHEN=INIT,
BUILD=(1,4,5,60,SQZ=(SHIFT=LEFT,PAIR=APOST,MID=C','))),
VLTRIM=C' '
This example illustrates how you can create VB output records with comma separated values from VB input records containing fields in fixed positions.
Length | Data
45 John Lewis -12.83 Research
50 Ted Blank +128.37 Manufacturing
54 Marilyn Carlson -282.83 Technical Support
51 Rex Otis +2.83 Marketing
Note that the data has three fields in fixed positions. The first field is a character string. The second field is a numeric value. The third field is another character string. (In the previous example, the character strings were surrounded by apostrophes; in this example the apostrophes must be added around the character strings.)
Length | Data
34 'John Lewis',-12.83,'Research'
39 'Ted Blank',+128.37,'Manufacturing'
49 'Marilyn Carlson',-282.83,'Technical Support'
32 'Rex Otis',+2.83,'Marketing'
We use OUTFIL IFTHEN to ensure that short records are padded on the right with blanks. (OUTFIL BUILD would terminate due to the short records.) WHEN=INIT reformats every record. OVERLAY surrounds the character strings with apostrophes. BUILD builds the output records with comma separated values.
Length | Data
52 'John Lewis' -12.83 'Research'
52 'Ted Blank' +128.37 'Manufacturing'
54 'Marilyn Carlson' -282.83 'Technical Support'
52 'Rex Otis' +2.83 'Marketing'
We then use SQZ to squeeze out the blanks between the fields, shift the remaining characters to the left and insert commas between the fields. SHIFT=LEFT shifts the characters to the left. PAIR=APOST ensures that blanks within paired apostrophes are not squeezed out (for example, we want to keep the blank in the 'John Lewis' field.) MID=C',' inserts a comma for each group of blanks removed between the fields (for example, between 'John Lewis' and -12.83).
Finally, we use VLRTIM=C' ' to remove trailing blanks at the end of each VB record by adjusting its length appropriately.