Describing a Calculated Value: COMPUTE

How to:

Reference:

COMPUTE commands can be included in Master Files and referenced in subsequent TABLE requests, enabling you to build expressions once and use them in multiple requests.

Syntax: How to Include a COMPUTE Command in a Master File 

COMPUTE fieldname/fmt [(GEOGRAPHIC_ROLE = georole)]
 =expression;
  [,TITLE='title',] 
  [TITLE_ln='titleln', ... ,]
  [,DESC[CRIPTION]='desc',] 
  [DESC_ln='descln', ... ,]$

where:

fieldname

Is name of the calculated field.

fmt

Is the format and length of the calculated field.

georole

Is a valid geographic role. Geographic roles can be names, postal codes, ISO (International Organization for Standardization) codes, FIPS (Federal Information Processing Standards) codes, or NUTS (Nomenclature of Territorial Units for Statistics ) codes. The following is a list of supported geographic roles.

  • ADDRESS_FULL. Full address.
  • ADDRESS_LINE. Number and street name.
  • CITY. City name.
  • CONTINENT. Continent name.
  • CONTINENT_ISO2. Continent ISO-3166 code.
  • COUNTRY. Country name.
  • COUNTRY_FIPS. Country FIPS code.
  • COUNTRY_ISO2. Country ISO-3166-2 code.
  • COUNTRY_ISO3. Country ISO-3166-3 code.
  • GEOMETRY_AREA. Geometry area.
  • GEOMETRY_LINE. Geometry line.
  • GEOMETRY_POINT. Geometry point.
  • LATITUDE. Latitude.
  • LONGITUDE. Longitude.
  • NUTS0. Country name (NUTS level 0).
  • NUTS0_CC. Country code (NUTS level 0).
  • NUTS1. Region name (NUTS level 1).
  • NUTS1_CC. Region code (NUTS level1).
  • NUTS2. Province name (NUTS level 2).
  • NUTS2_CC. Province code (NUTS level 2).
  • NUTS3. District name (NUTS level 3).
  • NUTS3_CC. District code (NUTS level 3).
  • POSTAL_CODE. Postal code.
  • STATE. State name.
  • STATE_FIPS. State FIPS code.
  • STATE_ISO_SUB. US State ISO subdivision code.
  • USSCITY. US city name.
  • USCITY_FIPS. US city FIPS code.
  • USCOUNTY. US county name.
  • USCOUNTY_FIPS. US county FIPS code.
  • USSTATE. US state name.
  • USSTATE_ABBR. US state abbreviation.
  • USSTATE_FIPS. US state FIPS code.
  • ZIP3. US 3-digit postal code.
  • ZIP5. US 5-digit postal code.
expression

Is the formula for calculating the value of the field.

TITLE='title'

Is a column title for the calculated field in the default language.

TITLE_ln='titleln'

Is a column title for the calculated field in the language specified by the language code ln.

DESC[CRIPTION]='desc'

Is a description for the calculated field in the default language.

DESC_ln='descln'

Is a description for the calculated field in the language specified by the language code ln.

Reference: Usage Notes for COMPUTE in a Master File

In all instances, COMPUTEs in the Master File have the same functionality and limitations as temporary COMPUTEs. Specifically, fields computed in the Master File must follow these rules:

  • They cannot be used in JOIN, DEFINE, or ACROSS phrases, or with prefix operators.
  • When used as selection criteria, syntax is either IF TOTAL field or WHERE TOTAL field.
  • When used as sort fields, syntax is BY TOTAL COMPUTE field.
  • To insert a calculated value into a heading or footing, you must reference it prior to the HEADING or FOOTING command.

Note: Maintain Data does not currently support using COMPUTEs in Master Files.

Example: Coding a COMPUTE in the Master File and Accessing the Computed Value

Use standard COMPUTE syntax to add a calculated value to your Master File. You can then access the calculated value by referencing the computed fieldname in subsequent TABLE requests. When used as a verb object, as in the following example, the syntax is SUM (or PRINT) COMPUTE field.

The following is the SALESTES Master File (the SALES FILE modified with an embedded COMPUTE): 

FILENAME=SALESTES, SUFFIX=FOC,
SEGNAME=STOR_SEG, SEGTYPE=S1,
   FIELDNAME=STORE_CODE,  ALIAS=SNO,  FORMAT=A3,   $
   FIELDNAME=CITY,        ALIAS=CTY,  FORMAT=A15,  $
   FIELDNAME=AREA,        ALIAS=LOC,  FORMAT=A1,   $
 
SEGNAME=DATE_SEG, PARENT=STOR_SEG, SEGTYPE=SH1,
   FIELDNAME=DATE,        ALIAS=DTE,  FORMAT=A4MD, $
 
SEGNAME=PRODUCT, PARENT=DATE_SEG, SEGTYPE=S1,
   FIELDNAME=PROD_CODE,     ALIAS=PCODE,   FORMAT=A3,    FIELDTYPE=I, $
   FIELDNAME=UNIT_SOLD,     ALIAS=SOLD,    FORMAT=I5,    $
   FIELDNAME=RETAIL_PRICE,  ALIAS=RP,      FORMAT=D5.2M, $
   FIELDNAME=DELIVER_AMT,   ALIAS=SHIP,    FORMAT=I5,    $
   FIELDNAME=OPENING_AMT,   ALIAS=INV,     FORMAT=I5,    $
   FIELDNAME=RETURNS,       ALIAS=RTN,     FORMAT=I3,    MISSING=ON, $
   FIELDNAME=DAMAGED,       ALIAS=BAD,     FORMAT=I3,    MISSING=ON, $
 
   COMPUTE REVENUE/D12.2M=UNIT_SOLD*RETAIL_PRICE;

The following TABLE request uses the REVENUE field: 

TABLE FILE SALESTES
HEADING CENTER
"NEW YORK PROFIT REPORT"
" "
SUM UNIT_SOLD AS 'UNITS,SOLD' RETAIL_PRICE AS 'RETAIL_PRICE'
COMPUTE REVENUE;
BY PROD_CODE AS 'PROD,CODE'
WHERE CITY EQ 'NEW YORK'
END

The output is:

           NEW YORK PROFIT REPORT
 
  PROD  UNITS
  CODE  SOLD   RETAIL_PRICE          REVENUE
  ----  ----   ------------          -------
  B10      30          $.85           $25.50
  B17      20         $1.89           $37.80
  B20      15         $1.99           $29.85
  C17      12         $2.09           $25.08
  D12      20         $2.09           $41.80
  E1       30          $.89           $26.70
  E3       35         $1.09           $38.15