DECODE: Decoding Values

The DECODE function assigns values based on the coded value of an input field. DECODE is useful for giving a more meaningful value to a coded value in a field. For example, the field GENDER may have the code F for female employees and M for male employees for efficient storage (for example, one character instead of six for female). DECODE expands (decodes) these values to ensure correct interpretation on a report.

You can use DECODE by supplying values directly in the function or by reading values from a separate file.

The use of DECODE with Maintain is limited. For information on decoding values with subscripted stack values, see SELECTS: Decoding a Value From a Stack.

Syntax: How to Supply Values in the Function

DECODE fieldname(code1 result1 code2 result2...[ELSE default ]);

where:

fieldname

Alphanumeric or Numeric

Is the name of the input field.

code

Alphanumeric or Numeric

Is the coded value for which DECODE searches. If the value has embedded blanks, commas, or other special characters, enclose it in single quotation marks. When DECODE finds the specified value, it assigns the corresponding result.

result

Alphanumeric or Numeric

Is the value assigned to a code. If the value has embedded blanks or commas or contains a negative number, enclose it in single quotation marks.

default

Alphanumeric or Numeric

Is the value assigned if the code is not found. If you omit a default value, DECODE assigns a blank or zero to non-matching codes.

You can use up to 40 lines to define the code and result pairs for any given DECODE function, or 39 lines if you also use an ELSE phrase. Use either a comma or blank to separate the code from the result, or one pair from another.

Top of page

Example: Supplying Values in the Function

EDIT extracts the first character of the CURR_JOBCODE field, then DECODE returns either ADMINISTRATIVE or DATA PROCESSING depending on the value extracted.

TABLE FILE EMPLOYEE
PRINT CURR_JOBCODE AND COMPUTE
DEPX_CODE/A1 = EDIT(CURR_JOBCODE, '9$$'); NOPRINT AND COMPUTE
JOB_CATEGORY/A15 = DECODE DEPX_CODE(A 'ADMINISTRATIVE' 
B 'DATA PROCESSING') ;
BY LAST_NAME
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

LAST_NAME    CURR_JOBCODE    JOB_CATEGORY
---------    ------------    ------------
BLACKWOOD    B04             DATA PROCESSING
CROSS        A17             ADMINISTRATIVE
GREENSPAN    A07             ADMINISTRATIVE
JONES        B03             DATA PROCESSING
MCCOY        B02             DATA PROCESSING
SMITH        B14             DATA PROCESSING

Syntax: How to Read Values From a File

DECODE fieldname(ddname [ELSE default]);

where:

fieldname

Alphanumeric or Numeric

Is the name of the input field.

ddname

Alphanumeric

Is a logical name or a shorthand name that points to the physical file containing the decoded values.

default

Alphanumeric or Numeric

Is the value assigned if the code is not found. If you omit a default, DECODE assigns a blank or zero to non-matching codes.

Reference: Guidelines for Reading Values From a File

Each record in the file is expected to contain pairs of elements separated by a comma or blank.
If each record in the file consists of only one element, this element is interpreted as the code, and the result becomes either a blank or zero, as needed.
This makes it possible to use the file to hold screening literals referenced in the screening condition:
```
IF field IS (filename)
```
and as a file of literals for an IF criteria specified in a computational expression. For example:
```
TAKE = DECODE SELECT (filename ELSE 1);
VALUE = IF TAKE IS 0 THEN... ELSE...;
```
TAKE is 0 for SELECT values found in the literal file and 1 in all other cases. The VALUE computation is carried out as if the expression had been:
```
IF SELECT (filename) THEN... ELSE...;
```
The file can contain up to 32,767 characters in the file.
All data is interpreted in ASCII format on UNIX and Windows, or in EBCDIC format on z/OS, and converted to the USAGE format of the DECODE pairs.
Leading and trailing blanks are ignored.
The remainder of each record is ignored and can be used for comments or other data. This convention applies in all cases, except when the file name is HOLD. In that case, the file is presumed to have been created by the HOLD command, which writes fields in the internal format, and the DECODE pairs are interpreted accordingly. In this case, extraneous data in the record is ignored.

Top of page

Example: Reading Values From a File

The following example has two parts. The first part creates a file with a list of IDs and reads the EDUCFILE data source. The second part reads the EMPLOYEE data source and assigns 0 to those employees who have taken classes and 1 to those employees who have not. The HOLD file contains only one column of values; therefore, DECODE assigns the value 0 to an employee whose EMP_ID appears in the file and 1 when EMP_ID does not appear in the file.

TABLE FILE EDUCFILE
PRINT EMP_ID
ON TABLE HOLD
END

TABLE FILE EMPLOYEE
PRINT EMP_ID AND LAST_NAME AND FIRST_NAME AND COMPUTE 
NOT_IN_LIST/I1 = DECODE EMP_ID(HOLD ELSE 1); 
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

EMP_ID     LAST_NAME        FIRST_NAME  NOT_IN_LIST
------     ---------        ----------  -----------
112847612  SMITH            MARY                  0
117593129  JONES            DIANE                 0
219984371  MCCOY            JOHN                  1
326179357  BLACKWOOD        ROSEMARIE             0
543729165  GREENSPAN        MARY                  1
818692173  CROSS            BARBARA               0