Creating a Numeric Expression

A numeric expression performs a calculation that uses numeric constants, fields, operators, and functions to return a numeric value. When you use a numeric expression to assign a value to a field, that field must have a numeric format. The default format is D12.2.

A numeric expression can consist of the following components, shown below in bold:

Before they are used in calculations, numeric values are generally converted to double-precision floating-point format. The result is then converted to the specified field format. In some cases the conversion may result in a difference in rounding.

If a number is too large (greater than 10⁷⁵) or too small (less than 10^-75), you receive an Overflow or Underflow warning, and zeros display for the field value.

Note: You can change the overflow character by issuing the SET OVERFLOWCHAR command.

For detailed information on rounding behavior for numeric data formats, see the Describing Data manual.

Syntax: How to Express a Number in Scientific Notation

In an IF clause, use the following:

IF field op n[.nn]{E|D|e|d}[+|-]p

In a WHERE clause, use the following:

WHERE field op EXPN(n[.nn{E|D|e|d}[+|-]p);

In a COMPUTE command, use the following:

COMPUTE field[/format] = EXPN(n[.nn]{{E|D|e|d}[+|-]p);

In a DEFINE command, use the following:

DEFINE FILE filename 
field[/format] = EXPN(n[.nn]{E|D|e|d}[+|-]p);
END

In a DEFINE in the Master File, use the following:

DEFINE field[/format] = EXPN(n[.nn]{{E|D|e|d}[+|-]p);

where:

field: Is a field in a request.
/format: Is the optional format of the field. For information on formats, see the Describing Data manual.
op: Is a relational operator in a request.
n.nn: Is a numeric constant that consists of a whole number component, followed by a decimal point, followed by a fractional component.
E, D, e, d: Denotes scientific notation. E, e, d, and D are interchangeable.
+, -: Indicates if p is positive or negative. Positive is the default.
p: Is the power of 10 to which to raise the number. The range of values for p is between -78 and +78.

Note: EXPN is useful for calculations on fields with F and D formats. It is generally not useful for calculations on fields with P or I formats.

Top of page

Example: Evaluating a Number in Scientific Notation

You can use scientific notation in an IF or WHERE clause to express 8000 as 8E+03:

IF RCOST LT 8E+03

WHERE RCOST LT EXPN(8E+03)

Reference: Arithmetic Operators

The following list shows the arithmetic operators you can use in an expression:

Addition	+
Subtraction	-
Multiplication	*
Division	/
Exponentiation	**

Note: If you attempt to divide by 0, the value of the expression is 0. Multiplication and exponentiation are not supported for date expressions of any type. To isolate part of a date, use a simple assignment command.

Order of Evaluation

Numeric expressions are evaluated in the following order:

Exponentiation.
Division and multiplication.
Addition and subtraction.

When operators are at the same level, they are evaluated from left to right. Because expressions in parentheses are evaluated before any other expression, you can use parentheses to change this predefined order. For example, the following expressions yield different results because of parentheses:

COMPUTE PROFIT/D12.2  =  RETAIL_PRICE  -  UNIT_COST  *  UNIT_SOLD ;
COMPUTE PROFIT/D12.2  = (RETAIL_PRICE  -  UNIT_COST) *  UNIT_SOLD ;

In the first expression, UNIT_SOLD is first multiplied by UNIT_COST, and the result is subtracted from RETAIL_PRICE. In the second expression, UNIT_COST is first subtracted from RETAIL_PRICE, and that result is multiplied by UNIT_SOLD.

Note: Two operators cannot appear consecutively. The following expression is invalid:

a * -1

To make it valid, you must add parentheses:

a* (-1)

Top of page

Example: Controlling the Order of Evaluation

The order of evaluation can affect the result of an expression. Suppose you want to determine the dollar loss in retail sales attributed to the return of damaged items. You could issue the following request:

TABLE FILE SALES
PRINT RETAIL_PRICE RETURNS DAMAGED
COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * RETURNS + DAMAGED;
BY PROD_CODE
WHERE PROD_CODE IS 'E1';
END

The calculation

COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * RETURNS + DAMAGED;

gives an incorrect result because RETAIL_PRICE is first multiplied by RETURNS, and then the result is added to DAMAGED. The correct result is achieved by adding RETURNS to DAMAGED, then multiplying the result by RETAIL_PRICE.

You can change the order of evaluation by enclosing expressions in parentheses. An expression in parentheses is evaluated before any other expression. You may also use parentheses to improve readability.

Using parentheses, the correct syntax for the preceding calculation is:

COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * (RETURNS + DAMAGED);

The output is:

PROD_CODE  RETAIL_PRICE  RETURNS  DAMAGED     RETAIL_LOSS
---------  ------------  -------  -------     -----------
E1                 $.89        4        7            9.79