XTAB

XTAB(col1, col2 [, operator_keyword, numeric_col3])

The first column to produce the xtab from. This can be a constant, a column, a single-column cell range, or any expression evaluating to one of the above.

col2

The second column to produce the xtab from. This can be a constant, a column, a single-column cell range, or any expression evaluating to one of the above.

operator_keyword

One of the valid operator keywords (see below).

numeric_col3

The third column to produce the xtab from. This can be a constant, a column, a single-column cell range, or any expression evaluating to one of the above containing a numeric value.

Description

XTAB calculates distinct values in col1 and col2. Then it computes operator_keyword of numeric_col3 at the intersection of each col1 value with each col2 value.

The operator_keyword defaults to COUNTOF, in which case numeric_col3 is not used.

Possible operator_keywords include:

COUNTOF - returns the number of records at each intersection.

COUNTZERO - returns the number of records at each intersection for which numeric_col3 is 0.

COUNTNONZERO - returns the number of records at each intersection for which numeric_col3 is not 0.

COUNTNULL - returns the number of records at each intersection for which numeric_col3 is NULL.

MINOF - returns the smallest value of numeric_col3 at each intersection; returns missing value if there are no values at the intersection.

MAXOF - returns the largest value of numeric_col3 at each intersection; returns missing value if there are no values at the intersection.

SUMOF - returns the sum of all numeric_col3 values at each intersection.

AVGOF - returns the average of all non- NULL numeric_col3 values at each intersection.

STDEVOF - returns the standard deviation of all non- NULL numeric_col3 values at each intersection.

The XTAB macro function may take a long time to compute when data is large. A "Computing..." progress bar will be displayed until the computation is complete. If you decide to cancel the computation, click on the "X" in the progress bar and delete the function definition containing the XTAB macro function.

Examples

TEMP=XTAB(V1,V2)

Creates a series of rows and columns that compute the count of the distinct values in the intersections of columns V1 and V2.

TEMP=XTAB(V4,V5, SUMOF V6)

Creates a series of rows and columns that represent the intersection of distinct values of columns V4 and V5. The measure at each intersection is the sum of the values in column V6 for the rows corresponding to that intersection.