Creating a Numeric Expression

In this section:

Order of Evaluation

How to:

Express a Number in Scientific Notation

Reference:

Arithmetic Operators

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:

A numeric constant. For example:
```
COMPUTE COUNT/I2 = 1 ;
```
A numeric constant in scientific notation. For example:
```
COMPUTE COST/D12.2 = EXPN(8E+3);
```
For syntax usage, see Express a Number in Scientific Notation.
A numeric field. For example:
```
COMPUTE RECOUNT/I2 = COUNT ;
```
Two numeric constants or fields joined by an arithmetic operator. For example:
```
COMPUTE BONUS/D12.2 = CURR_SAL * 0.05 ;
```
For a list of arithmetic operators, see Arithmetic Operators.

A numeric function. For example:

COMPUTE LONGEST_SIDE/D12.2 = MAX (WIDTH, HEIGHT) ;

Two or more numeric expressions joined by an arithmetic operator. For example:
```
COMPUTE PROFIT/D12.2 = (RETAIL_PRICE - UNIT_COST) * UNIT_SOLD ;
```
Note the use of parentheses to change the order of evaluation of the expression. For information on the order in which numeric operations are performed, see Order of Evaluation.

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.

IF-THEN-ELSE logic is supported in numeric expressions.

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.

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)

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

Example: Using IF-THEN-ELSE Logic in an Arithmetic Expression

The following request uses IF-THEN-ELSE logic in an arithmetic expression to determine how much to add to LISTPR to calculate NEWPRICE.

TABLE FILE MOVIES
SUM COPIES
LISTPR
COMPUTE
NEWPRICE = LISTPR + (IF COPIES GT 10 THEN 0.00 ELSE 25.00);
BY CATEGORY
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. Where there are more than 10 copies, the NEWPRICE equals LISTPR, otherwise NEWPRICE is $25.00 greater than LISTPR.