Features Added in FOCUS 7.6.9

Starting in FOCUS 7.6.9, you can use a DEFINE field to specify the number of repetitions for an OCCURS segment and convert dates stored as alphanumeric fields in non-FOCUS data sources to standard FOCUS dates.

Storing the Number of Repetitions of a Repeating Field in a Virtual Field

How to:

Specify an OCCURS Segment Using a Virtual Field

Reference:

Usage Notes for Using a Virtual Field With OCCURS

The OCCURS attribute in a Master File describes repeating fields or groups of fields in a non-FOCUS data source. The repeating group of fields is described as a descendent segment in the Master File, and the OCCURS attribute for that segment specifies how to determine the number of repetitions.

The number of repetitions does not have to be the same for every record instance. Sometimes, the number of repetitions can be derived from a field in the data source. In that case, you can create a virtual field in the Master File that indicates the number of repetitions for each record and use that virtual field as the value of the OCCURS attribute.

Top of page

Syntax: How to Specify an OCCURS Segment Using a Virtual Field

SEGNAME = parent, SEGTYPE = segtype,$
    .
    .
    .
  DEFINE definefield/In = expression;
SEGNAME = osegname, SEGTYPE=S0, PARENT = parent, OCCURS = definefield ,$
  FIELDNAME = rfield, ALIAS = ralias, USAGE = rufmt, ACTUAL = rafmt,$
    .
    .
    .
 [FIELDNAME = orderfield, ALIAS = ORDER, USAGE = In, ACTUAL = I4,$]

where:

parent: Is the name of the parent segment.
segtype: Is the SEGTYPE of the parent segment.
definefield: Is the virtual field that indicates the number of repetitions of the repeating field or group of fields. This field must be defined in a segment that is an ancestor of the segment containing the repeating fields.
In: Is the format of the virtual field that describes the number of repetitions. It must be an integer format.
expression: Is a valid expression that derives the number of repetitions for each record instance.
osegname: Is the name of the descendent OCCURS segment.
rfield: Is the name of a repeating field in the OCCURS segment.
ralias: Is the alias of a repeating field in the OCCURS segment.
rufmt: Is the display format for a repeating field in the OCCURS segment.
rafmt: Is the actual format for a repeating field in the OCCURS segment.
orderfield: Is the name of an internal counter field that you can specify as the last field in the OCCURS segment. The ORDER field associates a sequence number with each occurrence and is useful when the order of the repeating data is significant. For example, the values may represent monthly or quarterly data, but the record itself may not explicitly specify the month or quarter to which the data applies. The USAGE format must be integer and the ACTUAL format is I4.

Top of page

Reference: Usage Notes for Using a Virtual Field With OCCURS

The virtual field used as the OCCURS value cannot be redefined inside or outside of the Master File.

Top of page

Example: Using a Virtual Field With an OCCURS Segment

The following request against the EMPLOYEE data source creates a fixed-format sequential file with a repeating field. The request:

Counts the number of FICA deductions for each employee.
Creates a calculated field that contains the length of all FICA deduction fields for each employee.
Creates a HOLD file in which each record contains the calculated length of the deduction fields for the employee, the identifying information for the employee, and all FICA deductions for the employee.

Note that the number of deductions will vary for each employee. The part of the record that contains the deductions will constitute the OCCURS segment. The number of repetitions will have to be derived from the length field created in the TABLE request.

The procedure to create the file with the repeating deduction field follows:

DEFINE FILE EMPLOYEE                                                    
CTR/I5 WITH DED_AMT = IF EMP_ID NE LAST EMP_ID THEN 1 ELSE LAST CTR + 1;
END                                                                     
                                                                        
TABLE FILE EMPLOYEE                                                     
SUM CNT.DED_AMT NOPRINT EMP_ID LAST_NAME FIRST_NAME CURR_SAL            
COMPUTE DEDLEN/I5 = 12 * CNT.DED_AMT;                                   
BY EMP_ID NOPRINT                                                       
SUM DED_AMT                                                             
BY EMP_ID NOPRINT                                                       
ACROSS CTR NOPRINT                                                      
WHERE DED_CODE EQ 'FICA'                                                
ON TABLE SET HOLDLIST PRINTONLY                                         
ON TABLE HOLD AS OCCURS1 FORMAT ALPHA                                   
END

The OCCURS1 file has one record per employee with a variable number of DED_AMT fields. The total length of the number of actual instances of DED_AMT is stored in the field named DEDLEN. The Master File generated by the HOLD command lists 10 DED_AMT fields:

FILENAME=OCCURS1 , SUFFIX=FIX     , $          
  SEGMENT=OCCURS1, SEGTYPE=S0, $                              
    FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $      
    FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $  
    FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $ 
    FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $
    FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $      
    FIELDNAME=DED_AMT, ALIAS=E06, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E07, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E08, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E09, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E10, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E11, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E12, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E13, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E14, USAGE=D12.2M, ACTUAL=A12, $ 
    FIELDNAME=DED_AMT, ALIAS=E15, USAGE=D12.2M, ACTUAL=A12, $

You can edit the Master File to describe these repeating DED_AMT fields with an OCCURS segment. The DEFINE field named NUMOCC derives the number of occurrences from the DEDLEN field. The ORDER field is not actually in the file. It is an internal counter populated by FOCUS:

FILENAME=OCCURS1 , SUFFIX=FIX     , $                             
  SEGMENT=OCCURS1, SEGTYPE=S0, $                                  
    FIELDNAME=EMP_ID, ALIAS=E01, USAGE=A9, ACTUAL=A09, $          
    FIELDNAME=LAST_NAME, ALIAS=E02, USAGE=A15, ACTUAL=A15, $      
    FIELDNAME=FIRST_NAME, ALIAS=E03, USAGE=A10, ACTUAL=A10, $     
    FIELDNAME=CURR_SAL, ALIAS=E04, USAGE=D12.2M, ACTUAL=A12, $    
    FIELDNAME=DEDLEN, ALIAS=E05, USAGE=I5, ACTUAL=A05, $          
    DEFINE NUMOCC/I2 = DEDLEN/12;,$                               
  SEGNAME=DEDUCTION, SEGTYPE=S0, PARENT=OCCURS1, OCCURS=NUMOCC,$
    FIELDNAME=DED_AMT, ALIAS=E06,   USAGE=D12.2M, ACTUAL=A12, $   
    FIELDNAME=ORDER,   ALIAS=ORDER, USAGE=I2    , ACTUAL=I4 , $

The following request uses the ORDER field to select and print the first occurrence of the repeating field for each employee. Since every employee has at least one deduction, every employee is represented on the report output:

TABLE FILE OCCURS1                      
PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT
  WHERE ORDER EQ 1                     
END

The output is:

NUMOCC  LAST_NAME               CURR_SAL          DED_AMT
------  ---------               --------          -------
    10  STEVENS               $11,000.00           $64.17
     8  SMITH                 $13,200.00          $100.10
     4  JONES                 $18,480.00          $247.94
     8  SMITH                  $9,500.00           $60.96
     1  BANNING               $29,700.00          $519.75
     8  IRVING                $26,862.00          $626.78
     4  ROMANS                $21,120.00          $317.62
     1  MCCOY                 $18,480.00          $161.70
     5  BLACKWOOD             $21,780.00          $444.67
     7  MCKNIGHT              $16,100.00          $187.88
     4  GREENSPAN              $9,000.00           $52.50
    10  CROSS                 $27,062.00          $631.40

If you print the tenth occurrence of the repeating field, only two employees are displayed on the report output:

TABLE FILE OCCURS1                      
PRINT NUMOCC LAST_NAME CURR_SAL DED_AMT
  WHERE ORDER EQ 10                     
END

The output is:

NUMOCC  LAST_NAME               CURR_SAL          DED_AMT
------  ---------               --------          -------
    10  STEVENS               $11,000.00           $58.33
    10  CROSS                 $27,062.00          $526.20

DATEPATTERN in the Master File

In this section:

How to:

In some data sources, date values are stored in alphanumeric format without any particular standard, with any combination of components such as year, quarter, and month, and with any delimiter. In a sorted report, if such data is sorted alphabetically, the sequence does not make business sense. To ensure adequate sorting, aggregation, and reporting on date fields, FOCUS can convert the alphanumeric dates into standard FOCUS date format using a conversion pattern that you can specify in the Master File attribute called DATEPATTERN.

Each element in the pattern is either a constant character which must appear in the actual input or a variable that represents a date component. You must edit the USAGE attribute in the Master File so that it accounts for the date elements in the date pattern. The maximum length of the DATEPATTERN string is 64.

Top of page

Specifying Variables in a Date Pattern

The valid date components (variables) are year, quarter, month, day, and day of week. In the date pattern, variables are enclosed in square brackets (these brackets are not part of the input or output). Note that if the data contains brackets, you must use an escape character in the date pattern to distinguish the brackets in the data from the brackets used for enclosing variables.

Top of page

Syntax: How to Specify Years in a Date Pattern

[YYYY]: Specifies a 4-digit year.
[YY]: Specifies a 2-digit year.
[yy]: Specifies a zero-suppressed 2-digit year (for example, 8 for 2008).
[by]: Specifies a blank-padded 2-digit year.

Top of page

Syntax: How to Specify Month Numbers in a Date Pattern

[MM]: Specifies a 2-digit month number.
[mm]: Specifies a zero-suppressed month number.
[bm]: Specifies a blank-padded month number.

Top of page

Syntax: How to Specify Month Names in a Date Pattern

[MON]: Specifies a 3-character month name in uppercase.
[mon]: Specifies a 3-character month name in lowercase.
[Mon]: Specifies a 3-character month name in mixed-case.
[MONTH]: Specifies a full month name in uppercase.
[month]: Specifies a full month name in lowercase.
[Month]: Specifies a full month name in mixed-case.

Top of page

Syntax: How to Specify Days of the Month in a Date Pattern

[DD]: Specifies a 2-digit day of the month.
[dd]: Specifies a zero-suppressed day of the month.
[bd]: Specifies a blank-padded day of the month.

Top of page

Syntax: How to Specify Julian Days in a Date Pattern

[DDD]: Specifies a 3-digit day of the year.
[ddd]: Specifies a zero-suppressed day of the year.
[bdd]: Specifies a blank-padded day of the year.

Top of page

Syntax: How to Specify Day of the Week in a Date Pattern

[WD]: Specifies a 1-digit day of the week.
[DAY]: Specifies a 3-character day name, uppercase.
[day]: Specifies a 3-character day name, lowercase.
[Day]: Specifies a 3-character day name, mixed-case.
[WDAY]: Specifies a full day name, uppercase.
[wday]: Specifies a full day name, lowercase.
[Wday]: Specifies a full day name, mixed-case.

For the day of the week, the WEEKFIRST setting defines which day is day 1.

Top of page

Syntax: How to Specify Quarters in a Date Pattern

[Q]

Specifies a 1-digit quarter number (1, 2, 3, or 4).

For a string like Q2 or Q02, use constants before [Q], for example, Q0[Q].

Top of page

Specifying Constants in a Date Pattern

Between the variables, you can insert any constant values.

If you want to insert a character that would normally be interpreted as part of a variable, use the backslash character as an escape character. For example:

Use \[ to specify a left square bracket constant character.
Use \\ to specify a backslash constant character.

For a single quotation mark, use two consecutive single quotation marks ('').

Top of page

Example: Sample Date Patterns

If the date in the data source is of the form CY 2001 Q1, the DATEPATTERN attribute is:

DATEPATTERN = 'CY [YYYY] Q[Q]'

If the date in the data source is of the form Jan 31, 01, the DATEPATTERN attribute is:

DATEPATTERN = '[Mon] [DD], [YY]'

If the date in the data source is of the form APR-06, the DATEPATTERN attribute is:

DATEPATTERN = '[MON]-[YY]'

If the date in the data source is of the form APR - 06, the DATEPATTERN attribute is:

DATEPATTERN = '[MON] - [YY]'

If the date in the data source is of the form APR '06, the DATEPATTERN attribute is:

DATEPATTERN = '[MON] ''[YY]'

If the date in the data source is of the form APR [06], the DATEPATTERN attribute is:

DATEPATTERN = '[MON] \[[YY]\]' (or '[MON] \[[YY]]'

Note the right square bracket does not have to be escaped.

Top of page

Example: Sorting By an Alphanumeric Date

In the following example, DATE1 is a sequential file containing the following data:

June 1, '02      
June 2, '02      
June 3, '02      
June 10, '02   
June 11, '02     
June 12, '02     
June 20, '02     
June 21, '02     
June 22, '02     
June 1, '03      
June 2, '03      
June 3, '03      
June 10, '03     
June 11, '03     
June 12, '03     
June 20, '03     
June 21, '03     
June 22, '03     
June 1, '04      
June 2, '04      
June 3, '04      
June 4, '04      
June 10, '04     
June 11, '04     
June 12, '04     
June 20, '04     
June 21, '04     
June 22, '04

In the DATE1 Master File, the DATE1 field has alphanumeric USAGE and ACTUAL formats, each A18:

FILENAME=DATE1   , SUFFIX=FIX ,
  DATASET = 'USER1.DATE1.FOCTEMP'    , $
  SEGMENT=FILE1, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=E01, USAGE=A18, ACTUAL=A18, $

The following request sorts by the DATE1 FIELD:

TABLE FILE DATE1
PRINT DATE1 NOPRINT
BY DATE1
ON TABLE SET PAGE NOPAGE
END

The output shows that the alphanumeric dates are sorted alphabetically, not chronologically:

DATE1
-----
June 1, '02         
June 1, '03         
June 1, '04         
June 10, '02        
June 10, '03        
June 10, '04        
June 11, '02        
June 11, '03        
June 11, '04        
June 12, '02        
June 12, '03        
June 12, '04        
June 2, '02         
June 2, '03         
June 2, '04         
June 20, '02        
June 20, '03        
June 20, '04        
June 21, '02        
June 21, '03        
June 21, '04        
June 22, '02        
June 22, '03        
June 22, '04        
June 3, '02         
June 3, '03         
June 3, '04         
June 4, '04

In order to sort the data correctly, you can add a DATEPATTERN attribute to the Master File that enablesFOCUS to convert the date to a FOCUS date field. You must also edit the USAGE format to make it a FOCUS date format. To construct the appropriate pattern, you must account for all of the components in the stored date. The alphanumeric date has the following variables and constants:

Variable: full month name in mixed-case, [Month].
Constant: blank space.
Variable: zero-suppressed day of the month number, [dd].
Constant: comma followed by a blank space followed by an apostrophe (coded as two apostrophes in the pattern).
Variable: two-digit year, [YY].

The edited Master File follows. Note the addition of the DEFCENT attribute to convert the two-digit year to a four-digit year:

FILENAME=DATE1   , SUFFIX=FIX ,
  DATASET = 'USER1.DATE1.FOCTEM'P    , $
  SEGMENT=FILE1, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=E01, USAGE=A18, ACTUAL=A18,
      DEFCENT=20,  
      DATEPATTERN = '[Month] [dd], ''[YY]', $

Now, issuing the same request produces the following output. Note that DATE1 has been converted to a FOCUS date in MtrDYY format (as specified in the USAGE format):

DATE1
-----
June  1, 2002       
June  2, 2002       
June  3, 2002       
June 10, 2002       
June 11, 2002       
June 12, 2002       
June 20, 2002       
June 21, 2002       
June 22, 2002       
June  1, 2003       
June  2, 2003       
June  3, 2003       
June 10, 2003       
June 11, 2003       
June 12, 2003       
June 20, 2003       
June 21, 2003       
June 22, 2003       
June  1, 2004       
June  2, 2004       
June  3, 2004       
June  4, 2004       
June 10, 2004       
June 11, 2004       
June 12, 2004       
June 20, 2004       
June 21, 2004       
June 22, 2004