Creating a Single-Root Cluster Master File

Reading a Field Containing Delimited Values as individual Rows

How to:

Read a Field Containing Delimited Values as Individual Rows

A field that contains a list of delimited values (such as email addresses separated by spaces) can be pivoted to be read as individual rows, when an additional segment is added with SEGSUF=DFIX (Delimited Flat File).

Syntax: How to Read a Field Containing Delimited Values as Individual Rows

In the Master File, add a segment definition with SEGTYPE=S0, SEGSUF=DFIX, and a POSITION attribute that points to the field with delimited values.

SEGNAME=parentseg, SUFFIX=suffix, SEGTYPE=S1,$
 FIELD=FIELD1,  ...,$
 FIELD=delimitedfield, ALIAS=alias1, USAGE=fmt, ACTUAL=afmt,$
   ...
SEGNAME=dfixsegname, PARENT=parentseg, SEGSUF=DFIX,
  POSITION=delimitedfield,$
  FIELD=name, ALIAS=alias2, USAGE=fmt, ACTUAL=afmt,$
   ...

Create an Access File that specifies the row delimiter and any other DFIX attributes for the DFIX segment.

 SEGNAME=dfixsegname, RDELIMITER='delimiter', $

where:

delimitedfield: Is the name of the delimited field.
alias1: Is the alias of the delimited field.
fmt: Is the USAGE format for the delimited field.
afmt: Is the ACTUAL format for the delimited field.
dfixsegname: Is the segment name of the added segment definition for the DFIX field.
parentseg: Is the name of the segment that actually contains the delimited field.
name: Is the name for the pieces of the delimited field.
alias2: Is the alias for the pieces of the delimited field.
delimiter: Is the delimiter in the DFIX field.

When you issue a request, the field will be treated as separate rows based on the delimiter.

Example: Reading a Field Containing Delimited Values as Individual Rows

The following file named COUNTRYL.FTM contains country names and the longitude and latitude values of their capitals, The longitude and latitude values are stored as a single field named LNGLAT, separated by a comma:

Argentina      -64.0000000,-34.0000000
Australia      133.0000000,-27.0000000
Austria        13.3333000,47.3333000
Belgium        4.0000000,50.8333000
Brazil         -55.0000000,-10.0000000
Canada         -95.0000000,60.0000000
Chile          -71.0000000,-30.0000000
China          105.0000000,35.0000000
Colombia       -72.0000000,4.0000000
Denmark        10.0000000,56.0000000
Egypt          30.0000000,27.0000000
Finland        26.0000000,64.0000000
France         2.0000000,46.0000000
Germany        9.0000000,51.0000000
Greece         22.0000000,39.0000000
Hungary        20.0000000,47.0000000
India          77.0000000,20.0000000
Ireland        -8.0000000,53.0000000
Israel         34.7500000,31.5000000
Italy          12.8333000,42.8333000
Japan          138.0000000,36.0000000
Luxembourg     6.1667000,49.7500000
Malaysia       112.5000000,2.5000000
Mexico         -102.0000000,23.0000000
Netherlands    5.7500000,52.5000000
Norway         10.0000000,62.0000000
Philippines    122.0000000,13.0000000
Poland         20.0000000,52.0000000
Portugal       -8.0000000,39.5000000
Singapore      103.8000000,1.3667000
South Africa   24.0000000,-29.0000000
South Korea    127.5000000,37.0000000
Spain          -4.0000000,40.0000000
Sweden         15.0000000,62.0000000
Switzerland    8.0000000,47.0000000
Taiwan         121.0000000,23.5000000
Thailand       100.0000000,15.0000000
Tunisia        9.0000000,34.0000000
Turkey         35.0000000,39.0000000
United Kingdom -.1300000,51.5000000
United States  -97.0000000,38.0000000

Following is the original Master File, COMMA1.

FILENAME=COMMA1  , SUFFIX=FIX, IOTYPE=STREAM 
 DATASET=appname/countryl.ftm, $   
   SEGNAME=COU, SEGTYPE=S1, $  
     FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A15, ACTUAL=A15, $   
     FIELDNAME=LNGLAT, ALIAS=LNGLAT,USAGE=A25, ACTUAL=A25, $

Following is the COMMA2 Master File, with the DFIX segment added.

FILENAME=COMMA2  , SUFFIX=FIX, IOTYPE=STREAM,     
 DATASET=appname/countryl.ftm, $    
   SEGNAME=COU, SEGTYPE=S1, $  
     FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A15, ACTUAL=A15, $  
     FIELDNAME=LNGLAT, ALIAS=LNGLAT,USAGE=A25, ACTUAL=A25, $  
   SEGNAME=COMMA2, SEGTYPE=S0, SEGSUF=DFIX,PARENT=COU,POSITION=LNGLAT,$ 
     FIELD=COORD, ALIAS = XY, USAGE=A25, ACTUAL=A25,$

Following is the COMMA2 Access File.

SEGNAME=COMMA2, RDELIMITER=',', HEADER=NO, PRESERVESPACE=NO, $

The following request uses the COMMA2 Master File to print the values.

TABLE FILE COMMA2
PRINT COORD      
BY COUNTRY 
END

On the output, the LNGLAT field has been treated as two separate records. The partial output follows:

COUNTRY          COORD      
-------          -----      
Argentina        -64.0000000 
                 -34.0000000 
Australia        133.0000000
                 -27.0000000
Austria          13.3333000 
                 47.3333000 
Belgium          4.0000000  
                 50.8333000 
Brazil           -55.0000000
                 -10.0000000
Canada           -95.0000000
                 60.0000000 
Chile            -71.0000000
                 -30.0000000
China            105.0000000
                 35.0000000 
Colombia         -72.0000000