Defining a Window for a Calculated Value

How to:

Use the DEFCENT and YRTHRESH parameters on a COMPUTE command in a report request to create a window for a temporary field that is calculated from the result of a PRINT, LIST, SUM, or COUNT command. The window is used to interpret a date value for that field when the century is not supplied.

The DEFCENT and YRTHRESH parameters must immediately follow the field format specification; the values are always taken from the left side of the COMPUTE syntax (that is, from the left side of the equal sign). If the expression in the COMPUTE 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.

You can also use the parameters on a COMPUTE command in a MODIFY or Maintain procedure, or on a DECLARE command in Maintain. For details on the use of the parameters in Maintain, see the Maintaining Databases manual.

Syntax: How to Define a Window for a Calculated Value in a Report

Use standard COMPUTE syntax, as described in the Creating Reports manual. Partial COMPUTE syntax is shown here.

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

TABLE FILE filename
command 
[AND] COMPUTE 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 calculated value.

command

Is a command such as PRINT, LIST, SUM, or COUNT.

fieldname

Is the name of the calculated value.

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 temporary field.

END

Is required to terminate the request.

Syntax: How to Define a Window for a Calculated Value in a MODIFY Request

Use standard MODIFY and COMPUTE syntax, as described in the Maintaining Databases manual; partial syntax is shown here.

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

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

where:

filename

Is the name of the file you are modifying.

fieldname

Is the name of the field being set to the value of expression.

format

Is a date format such as MDY 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 fieldname.

END

Terminates the request. Do not add this command if the request contains PROMPT statements.

Example: Defining a Window for a Calculated Value

In the following request, the parameters DEFCENT and YRTHRESH on the COMPUTE command define a window from 1999 to 2098. Notice that both DEFCENT and YRTHRESH are coded, as required. The window is applied to the field created by the COMPUTE command, LATEST_DAT_INC.

DAT_INC is formatted as I6YMD, which is a legacy date with a 2-digit year. LATEST_DAT_INC is a date format with a 4-digit year (YYMD). The prefix MAX retrieves the highest value of DAT_INC.

The request is:

TABLE FILE EMPLOYEE
SUM SALARY AND COMPUTE
  LATEST_DAT_INC/YYMD DFC 19 YRT 99 = MAX.DAT_INC;
END

The highest value of DAT_INC is 82/08/01. Since the year 82 is less than the threshold 99, it assumes the value 20 for the century (DEFCENT + 1).

The output is:

PAGE     1
 
         SALARY  LATEST_DAT_INC
         ------  --------------
    $332,929.00  2082/08/01

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

The following sample request illustrates a call to the function JULDAT in a COMPUTE command. JULDAT converts dates from Gregorian format (year/month/day) to Julian format (year/day). For century display, dates in Julian format are 7-digit numbers. The first 4 digits are the century. The last three digits represent the number of days, counting from January 1.

For details on JULDAT, see the Using Functions manual.

In the request, the input field is HIRE_DATE. The function converts it to Julian format and returns it as JULIAN_DATE. HIRE_DATE is formatted as I6YMD, which is a legacy date with a 2-digit year. JULIAN_DATE is formatted as I7, which is a legacy date with a 4-digit year. 

TABLE FILE EMPLOYEE
PRINT DEPARTMENT HIRE_DATE
AND COMPUTE
   JULIAN_DATE/I7 = JULDAT(HIRE_DATE, JULIAN_DATE);
BY LAST_NAME BY FIRST_NAME
END

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

FILENAME=EMPLOYEE, SUFFIX=FOC, FDFC=19, FYRT=82
 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,      $
.
.
.

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 follows. By default, the second occurrence of the last name SMITH displays as blanks. 

PAGE     1
 
LAST_NAME      FIRST_NAME   DEPARTMENT   HIRE_DATE   JULIAN_DATE
---------      ----------   ----------   ---------   -----------
BANNING        JOHN         PRODUCTION    82/08/01       1982213
BLACKWOOD      ROSEMARIE    MIS           82/04/01       1982091
CROSS          BARBARA      MIS           81/11/02       2081306
GREENSPAN      MARY         MIS           82/04/01       1982091
IRVING         JOAN         PRODUCTION    82/01/04       1982004
JONES          DIANE        MIS           82/05/01       1982121
MCCOY          JOHN         MIS           81/07/01       2081182
MCKNIGHT       ROGER        PRODUCTION    82/02/02       1982033
ROMANS         ANTHONY      PRODUCTION    82/07/01       1982182
SMITH          MARY         MIS           81/07/01       2081182
               RICHARD      PRODUCTION    82/01/04       1982004
STEVENS        ALFRED       PRODUCTION    80/06/02       2080154
 Information Builders