Defining a Window for a Virtual Field

How to:

The DEFCENT and YRTHRESH parameters on a DEFINE command create a window for a virtual field. The window is used to interpret date values for the virtual field when the century is not supplied. You can issue a DEFINE command in either a request or a Master File.

The DEFCENT and YRTHRESH parameters must immediately follow the field format specification; the values are always taken from the left side of the DEFINE syntax (that is, from the left side of the equal sign). If the expression in the DEFINE contains a function call, the function uses the DEFCENT and YRTHRESH values for the input field. The standard order of precedence (field level/file level/global level) applies to the DEFCENT and YRTHRESH values for the input field.

Syntax: How to Define a Window for a Virtual Field in a Request

Use standard DEFINE syntax for a request, as described in the Creating Reports manual. Partial DEFINE syntax is shown here.

On the line that specifies the name of the virtual field, include the DEFCENT and YRTHRESH parameters and values. The parameters must immediately follow the field format information. 

DEFINE FILE filename 
  fieldname[/format] [{DEFCENT|DFC} {cc|19} {YRTHRESH|YRT} {[-]yy|0}] =
    expression; 
.
.
.
END

where:

filename

Is the name of the file for which you are creating the virtual field.

fieldname

Is the name of the virtual field.

format

Is a date format such as DMY or YYMD.

DEFCENT

Is the parameter for the default century.

cc

Is the century for the start date of the window. If you do not supply a value, cc defaults to 19, for the twentieth century.

YRTHRESH

Is the parameter for the year threshold. You must code values for both DEFCENT and YRTHRESH unless YRTHRESH is negative. In that case, only code a value for YRTHRESH.

yy

Is the year threshold for the window. If you do not supply a value, yy defaults to zero (0).

If yy is a positive number, two-digit years greater than or equal to the threshold default to the value of DEFCENT for the century. Two-digit years less than the threshold assume the value of DEFCENT + 1.

If yy is a negative number (-yy), the start date of the window is derived by subtracting that number from the current year, and DEFCENT is automatically calculated. The start date is automatically incremented by one at the beginning of each successive year.

expression

Is a valid arithmetic or logical expression, function, or function that determines the value of the virtual field.

END

Is required to terminate the DEFINE command.

Example: Defining a Window for a Virtual Field in a Request

In the following request, the DEFINE command creates two virtual fields, GLOBAL_HIRE_DATE and WINDOWED_HIRE_DATE. Both virtual fields are derived from the existing field HIRE_DATE. The format of HIRE_DATE is I6YMD, which is a legacy date with a 2-digit year. The virtual fields are date formats with a 4-digit year (YYMD).

The second virtual field, WINDOWED_HIRE_DATE, has the additional parameters DEFCENT and YRTHRESH, which define a window from 1982 to 2081. Notice that both DEFCENT and YRTHRESH are coded, as required.

The request is:

DEFINE FILE EMPLOYEE
GLOBAL_HIRE_DATE/YYMD = HIRE_DATE;
WINDOWED_HIRE_DATE/YYMD DFC 19 YRT 82 = HIRE_DATE;
END
 
TABLE FILE EMPLOYEE
PRINT HIRE_DATE GLOBAL_HIRE_DATE WINDOWED_HIRE_DATE
END

Assuming that there are no FDEFCENT and FYRTHRESH file-level settings in the Master File for EMPLOYEE, the global default settings (DEFCENT = 19, YRTHRESH = 0) are used to interpret 2-digit years for HIRE_DATE when deriving the value of GLOBAL_HIRE_DATE. For example, the value of all years for HIRE_DATE (80, 81, and 82) is greater than 0; consequently they default to 19 for the century and are returned as 1980, 1981, and 1982 in the GLOBAL_HIRE_DATE column.

For WINDOWED_HIRE_DATE, the window created specifically for that field (1982 to 2081) is used. The 2-digit years 80 and 81 for HIRE_DATE are less than the threshold for the window (82); consequently, they are returned as 2080 and 2081 in the WINDOWED_HIRE_DATE column.

The output is:

PAGE     1
 
HIRE_DATE   GLOBAL_HIRE_DATE  WINDOWED_HIRE_DATE
---------   ----------------  ------------------
 80/06/02   1980/06/02        2080/06/02
 81/07/01   1981/07/01        2081/07/01
 82/05/01   1982/05/01        1982/05/01
 82/01/04   1982/01/04        1982/01/04
 82/08/01   1982/08/01        1982/08/01
 82/01/04   1982/01/04        1982/01/04
 82/07/01   1982/07/01        1982/07/01
 81/07/01   1981/07/01        2081/07/01
 82/04/01   1982/04/01        1982/04/01
 82/02/02   1982/02/02        1982/02/02
 82/04/01   1982/04/01        1982/04/01
 81/11/02   1981/11/02        2081/11/02

Example: Defining a Window for Function Input in a DEFINE Command

The following sample request illustrates a call to the function AYMD in a DEFINE command. AYMD adds 60 days to the input field, HIRE_DATE; the output field, SIXTY_DAYS, contains the result. HIRE_DATE is formatted as I6YMD, which is a legacy date with a 2-digit year. SIXTY_DAYS is formatted as I8YYMD, which is a legacy date with a 4-digit year.

For details on AYMD, see the Using Functions manual. 

DEFINE FILE EMPLOYEE
SIXTY_DAYS/I8YYMD = AYMD(HIRE_DATE, 60, 'I8YYMD');
END
TABLE FILE EMPLOYEE
PRINT HIRE_DATE SIXTY_DAYS
END

The function uses the DEFCENT and YRTHRESH values for the input field HIRE_DATE. In this example, they are set on the field level in the Master File: 

FILENAME=EMPLOYEE, SUFFIX=FOC
 SEGNAME=EMPINFO,  SEGTYPE=S1
  FIELDNAME=EMP_ID,       ALIAS=EID,    FORMAT=A9,                     $
  FIELDNAME=LAST_NAME,    ALIAS=LN,     FORMAT=A15,                    $
  FIELDNAME=FIRST_NAME,   ALIAS=FN,     FORMAT=A10,                    $
  FIELDNAME=HIRE_DATE,    ALIAS=HDT,    FORMAT=I6YMD, DFC=19, YRT=82,  $
.
.
.

The function inputs a 2-digit year, which is windowed. It then outputs a 4-digit year that includes the century digits.

The input values 80 and 81 are less than the threshold 82, so they assume the value 20 for the century. The input value 82 is equal to the threshold, so it defaults to 19 for the century.

The output is: 

PAGE     1
 
HIRE_DATE   SIXTY_DAYS
---------   ----------
 80/06/02   2080/08/01
 81/07/01   2081/08/30
 82/05/01   1982/06/30
 82/01/04   1982/03/05
 82/08/01   1982/09/30
 82/01/04   1982/03/05
 82/07/01   1982/08/30
 81/07/01   2081/08/30
 82/04/01   1982/05/31
 82/02/02   1982/04/03
 82/04/01   1982/05/31
 81/11/02   2082/01/01

Syntax: How to Define a Window for a Virtual Field in a Master File

Use standard DEFINE syntax for a Master File, as discussed in your documentation on describing data the Describing Data manual. Partial DEFINE syntax is shown here.

The parameters DEFCENT and YRTHRESH must immediately follow the field format information. 

DEFINE fieldname/[format] [{DEFCENT|DFC} {cc|19} 
       {YRTHRESH|YRT} {[-]yy|0}] =    expression;$

where:

fieldname

Is the name of the virtual field.

format

Is a date format such as DMY or YYMD.

DEFCENT

Is the parameter for the default century.

cc

Is the century for the start date of the window. If you do not supply a value, cc defaults to 19, for the twentieth century.

YRTHRESH

Is the parameter for the year threshold. You must code values for both DEFCENT and YRTHRESH unless YRTHRESH is negative. In that case, only code a value for YRTHRESH.

yy

Is the year threshold for the window. If you do not supply a value, yy defaults to zero (0).

If yy is a positive number, two-digit years greater than or equal to the threshold default to the value of DEFCENT for the century. Two-digit years less than the threshold assume the value of DEFCENT + 1.

If yy is a negative number (-yy), the start date of the window is derived by subtracting that number from the current year, and DEFCENT is automatically calculated. The start date is automatically incremented by one at the beginning of each successive year.

expression

Is a valid arithmetic or logical expression, function, or function that determines the value of the virtual field.

Example: Defining a Window for a Virtual Field in a Master File

In the following example, the DEFINE command in a Master File creates a virtual field named NEW_HIRE_DATE. It is derived from the existing field HIRE_DATE. The format of HIRE_DATE is I6YMD, which is a legacy date with a 2-digit year. NEW_HIRE_DATE is a date format with a 4-digit year (YYMD).

The parameters DEFCENT and YRTHRESH on the DEFINE command create a window from 1982 to 2081, which is used to interpret all 2-digit years for the virtual field. Notice that both DEFCENT and YRTHRESH are coded, as required.

The field-level window takes precedence over any global settings in effect. There is no file-level setting in the Master File.

The Master File is:

FILENAME=EMPLOYEE, SUFFIX=FOC
 SEGNAME=EMPINFO,  SEGTYPE=S1
  FIELDNAME=EMP_ID,       ALIAS=EID,    FORMAT=A9,           $
  FIELDNAME=LAST_NAME,    ALIAS=LN,     FORMAT=A15,          $
  FIELDNAME=FIRST_NAME,   ALIAS=FN,     FORMAT=A10,          $
  FIELDNAME=HIRE_DATE,    ALIAS=HDT,    FORMAT=I6YMD,        $
.
.
.
DEFINE NEW_HIRE_DATE/YYMD DFC 19 YRT 82 = HIRE_DATE;$

The following request generates the values in the sample report: 

TABLE FILE EMPLOYEE
PRINT HIRE_DATE NEW_HIRE_DATE
END

Since the 2-digit years 80 and 81 are less than the threshold 82, the century assumes the value of DEFCENT + 1 (20), and they are returned as 2080 and 2081 in the NEW_HIRE_DATE column. The 2-digit year 82 is equal to the threshold and therefore defaults to the value of DEFCENT (19). It is returned as 1982.

The output is: 

PAGE     1
 
HIRE_DATE     NEW_HIRE_DATE
---------     -------------
 80/06/02     2080/06/02
 81/07/01     2081/07/01
 82/05/01     1982/05/01
 82/01/04     1982/01/04
 82/08/01     1982/08/01
 82/01/04     1982/01/04
 82/07/01     1982/07/01
 81/07/01     2081/07/01
 82/04/01     1982/04/01
 82/02/02     1982/02/02
 82/04/01     1982/04/01
 81/11/02     2081/11/02
 Information Builders