Features Added in FOCUS 7.6.9

Starting in FOCUS 7.6.9, you can reduce the number of lines on a report that uses PRINT with ACROSS and create a delimited sequential file using the HOLD command.

Compressing PRINT With ACROSS

How to:

Compress Report Lines

Reference:

Usage Notes for SET ACROSSPRT

The PRINT command generates a report that has a single line for each record retrieved from the data source after screening out those that fail IF or WHERE tests. When PRINT is used in conjunction with an ACROSS phrase, many of the generated columns may be empty. Those columns display the missing data symbol.

To avoid printing such a sparse report, you can use the SET ACROSSPRT command to compress the lines in the report. The number of lines is reduced within each sort group by swapping non-missing values from lower lines with missing values from higher lines, and then eliminating any lines whose columns all have missing values.

Because data may be moved to different report lines, row-based calculations such as ROW-TOTAL and ACROSS-TOTAL in a compressed report are different from those in a non-compressed report. Column calculations are not affected by compressing the report lines.

Top of page

Syntax: How to Compress Report Lines

SET ACROSSPRT = {NORMAL|COMPRESS}

ON TABLE SET ACROSSPRT {NORMAL|COMPRESS}

where:

NORMAL: Does not compress report lines. NORMAL is the default value.
COMPRESS: Compresses report lines by promoting data values up to replace missing values within a sort group.

Top of page

Reference: Usage Notes for SET ACROSSPRT

Compression applies only to ACROSS fields, including ACROSS … COLUMNS. It has no effect on BY fields.
The only data values that are subject to compression are true missing values. If the value of the stored data is either 0 or blank and the metadata indicates that MISSING is ON, that value is not subject to compression.

Top of page

Example: Compressing Report Output With SET ACROSSPRT

The following request against the GGSALES data source prints unit sales by product across region:

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';ON TABLE SET ACROSSPRT NORMALON TABLE SET PAGE NOPAGE                     
END

Each line of the report represents one sale in one region, so at most one column in each row has a non-missing value when ACROSSPRT is set to NORMAL:

                  Region                                         
                  Midwest     Northeast   Southeast   West       
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales 
-----------------------------------------------------------------
Capuccino                  .         936           .           . 
                           .         116           .           . 
                           .         136           .           . 
                           .           .        1616           . 
                           .           .        1118           . 
                           .           .         774           . 
                           .           .           .        1696 
                           .           .           .        1519 
                           .           .           .         836 
Espresso                1333           .           .           . 
                         280           .           .           . 
                         139           .           .           . 
                           .        1363           .           . 
                           .         634           .           . 
                           .         406           .           . 
                           .           .        1028           . 
                           .           .        1014           . 
                           .           .         885           .
                           .           .           .        1782
                           .           .           .        1399
                           .           .           .         551

Setting ACROSSPRT to COMPRESS promotes non-missing values up to replace missing values within the same BY group and then eliminates lines consisting of all missing values:

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';ON TABLE SET ACROSSPRT COMPRESS ON TABLE SET PAGE NOPAGE                     
END

The output is:

                  Region                                        
                  Midwest     Northeast   Southeast   West      
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales
----------------------------------------------------------------
Capuccino                  .         936        1616        1696
                           .         116        1118        1519
                           .         136         774         836
Espresso                1333        1363        1028        1782
                         280         634        1014        1399
                         139         406         885         551

Creating a Delimited Sequential File

How to:

Create a Delimited Sequential File

Reference:

Usage Notes for HOLD FORMAT DFIX

You can use the HOLD FORMAT DFIX command to create an alphanumeric sequential file delimited by any character or combination of characters. You can also specify whether to enclose alphanumeric values in quotation marks or some other enclosure and whether to include a header record that lists the names of the fields.

A Master File and an Access File are created to describe the delimited sequential file that is generated. The SUFFIX value in the Master File is DFIX. The Access File specifies the delimiter, the enclosure character (if any), and whether there is a header record. The Master and Access Files are useful if you will later read the sequential file using FOCUS.

Top of page

Syntax: How to Create a Delimited Sequential File

ON TABLE HOLD [AS filename] FORMAT DFIX
         DELIMITER delimiter [ENCLOSURE enclosure] [HEADER {YES|NO}]

where:

filename

Is the name of the file to be created. If you do not specify a name, the default name is HOLD.

delimiter

Consists of up to 30 printable or non-printable non-null characters. For a non-printable character, enter the hexadecimal value that represents the character. If you use a mixture of printable and non-printable characters, you must enter them all as hexadecimal values. To create a tab-delimited file, you can specify the delimiter value as TAB or as its hexadecimal equivalent (0x09 on ASCII platforms or 0x05 on EBCDIC platforms).

Note that numeric digits and symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-) cannot be used in the delimiter sequence.

enclosure

Consists of up to four printable characters used to enclose each alphanumeric value in the file. Most alphanumeric characters can be used as all or part of the enclosure sequence. However, numeric digits and symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-) cannot be used in the enclosure sequence. Also note that, in order to specify a single quotation mark as the enclosure character, you must enter four consecutive single quotation marks. The most common enclosure is one double quotation mark.

HEADER {YES|NO}

Specifies whether to include a header record that contains the names of the fields in the delimited sequential file generated by the request. NO is the default value.

Top of page

Reference: Usage Notes for HOLD FORMAT DFIX

Missing data is indicated by no data. So, with enclosure, a missing alphanumeric field is indicated by two enclosure characters, while a missing numeric field is indicated by two delimiters.
Text fields are not supported with HOLD FORMAT DFIX.
While HOLD FORMAT DFIX creates a single segment file, you can manually add segments to the resulting Master and Access File. In the Access File, you can specify a separate delimiter and/or enclosure for each segment.
The FILETYPE of the generated sequential file on z/VM is FOCTEMP.

Top of page

Example: Creating a Pipe-Delimited File

The following request against the CENTORD data source creates a sequential file named PIPE1 with fields separated by the pipe character (|). Alphanumeric values are not enclosed in quotation marks, and there is no header record:

TABLE FILE CENTORD                           
SUM QUANTITY LINEPRICE BY REGION BY YEAR  
ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER |
END

The PIPE1 Master File specifies the SUFFIX value as DFIX:

FILENAME=PIPE1   , SUFFIX=DFIX    , $                           
  SEGMENT=PIPE1, SEGTYPE=S2, $                                  
    FIELDNAME=REGION, ALIAS=E01, USAGE=A5, ACTUAL=A05, $        
    FIELDNAME=YEAR, ALIAS=E02, USAGE=YY, ACTUAL=A04, $
    FIELDNAME=QUANTITY, ALIAS=E03, USAGE=I8C, ACTUAL=A08, $     
    FIELDNAME=LINEPRICE, ALIAS=E04, USAGE=D12.2MC, ACTUAL=A12, $

The PIPE1 Access File specifies the delimiter:

SEGNAME=PIPE1, DELIMITER=|, HEADER=NO, $

The PIPE1 sequential file contains the following data. Each data value is separated from the next value by a pipe character:

EAST|2000|3907|1145655.77    
EAST|2001|495922|127004359.88
EAST|2002|543678|137470917.05
NORTH|2001|337168|85750735.54
NORTH|2002|370031|92609802.80
SOUTH|2000|3141|852550.45    
SOUTH|2001|393155|99822662.88
SOUTH|2002|431575|107858412.0
WEST|2001|155252|39167974.18 
WEST|2002|170421|42339953.45

The following version of the HOLD command specifies both the delimiter and an enclosure character (“):

ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER | ENCLOSURE "

The Master File remains the same, but the Access File now specifies the enclosure character:

SEGNAME=PIPE1, DELIMITER=|, ENCLOSURE=", HEADER=NO, $

In the delimited file that is created, each data value is separated from the next by a pipe character, and alphanumeric values are enclosed within double quotation marks:

"EAST"|2000|3907|1145655.77     
"EAST"|2001|495922|127004359.88 
"EAST"|2002|543678|137470917.05 
"NORTH"|2001|337168|85750735.54 
"NORTH"|2002|370031|92609802.80 
"SOUTH"|2000|3141|852550.45     
"SOUTH"|2001|393155|99822662.88 
"SOUTH"|2002|431575|107858412.01
"WEST"|2001|155252|39167974.18  
"WEST"|2002|170421|42339953.45

This version of the HOLD command adds a header record to the generated file:

ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER |  ENCLOSURE "  HEADER YES

The Master File remains the same, but the Access File now specifies that the generated sequential file should contain a header record with column names as its first record:

SEGNAME=PIPE1, DELIMITER=|, ENCLOSURE=", HEADER=YES, $

In the delimited file that is created, each data value is separated from the next by a pipe character, and alphanumeric values are enclosed within double quotation marks. The first record contains the column names:

"REGION"|"YEAR"|"QUANTITY"|"LINEPRICE"
"EAST"|2000|3907|1145655.77           
"EAST"|2001|495922|127004359.88       
"EAST"|2002|543678|137470917.05       
"NORTH"|2001|337168|85750735.54       
"NORTH"|2002|370031|92609802.80       
"SOUTH"|2000|3141|852550.45           
"SOUTH"|2001|393155|99822662.88       
"SOUTH"|2002|431575|107858412.01      
"WEST"|2001|155252|39167974.18        
"WEST"|2002|170421|42339953.45

Top of page

Example: Creating a Tab-Delimited File

The following request against the CENTORD data source creates a sequential file named TAB1 with fields separated by a tab character:

TABLE FILE CENTORD                              
SUM QUANTITY LINEPRICE BY REGION BY YEAR     
ON TABLE HOLD AS TAB1 FORMAT DFIX DELIMITER TAB
END

As the tab character is not printable, the TAB1 Access File specifies the delimiter using its hexadecimal value.

The following is the Access File in an EBCDIC environment:

SEGNAME=TAB1, DELIMITER=0x05, HEADER=NO, $

The following is the Access File in an ASCII environment:

SEGNAME=TAB1, DELIMITER=0x09, HEADER=NO, $

Top of page

Example: Missing Data in the HOLD File

The following request against the CENTORD data source creates missing alphanumeric and numeric values in the resulting comma-delimited HOLD file:

DEFINE FILE CENTORD                                                 
AREA/A5 MISSING ON = IF REGION EQ 'EAST' THEN MISSING ELSE REGION;  
MQUANTITY/I9 MISSING ON = IF REGION EQ 'WEST' THEN MISSING ELSE 200;
END                                                                 
                                                                    
TABLE FILE CENTORD                                                  
SUM QUANTITY MQUANTITY LINEPRICE BY AREA BY YEAR                 
WHERE AREA NE 'NORTH' OR 'SOUTH'                                 
  ON TABLE HOLD AS MISS1 FORMAT DFIX DELIMITER , ENCLOSURE "        
END

In the MISS1 HOLD file, the missing alphanumeric values are indicated by two enclosure characters in a row (““) and the missing numeric values are indicated by two delimiters in a row (,,):

"",2000,3907,600,1145655.77       
"",2001,495922,343000,127004359.88
"",2002,543678,343000,137470917.05
"WEST",2001,155252,,39167974.18   
"WEST",2002,170421,,42339953.45