The OLAP Procedure

MEASURE Statement

The MEASURE statement defines the cube's measures and indicates how they map to the input data.

MEASURE measure-name STAT=statname <option(s)>;

Include one MEASURE statement for each measure in the cube. Each cube must have at least one measure. Measure names must be unique. You can have a maximum of 1,024 measures per cube.

Note: All cube aggregations have identical measures. [cautionend]

Required Arguments

measure-name

specifies a valid SAS name for the measure. The name must be unique. For naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server.

STAT= statname

specifies the statistic for the measure. The following base statistics are available: N, NMISS, NUNIQUE, SUM, MAX, MIN, or USS. In addition, these derived statistics are also available: AVG, RANGE, CSS, VAR, STD, STDERR, CV, T, PRT, LCLM, or UCLM.

Note: At least one non-NUNIQUE measure must be defined. [cautionend]

New cubes that are based on a data source that contains existing summarized data (where such data has been indicated in at least one AGGREGATION statement via the TABLE= option), must include MEASURE statements for the stored statistics required for each derived statistic that you want to create for the new cube. For example, if you want to calculate AVG, you must create measures for N and SUM, as well as AVG.

Note: MOLAP aggregations do not require the N and SUM. [cautionend]

The following table indicates which stored statistics are required for each derived statistic:

Stored Statistics Required for Each Derived Statistic
Derived Statistics	Required Stored Statistics
AVG	N, SUM
CSS	N, SUM, USS
RANGE	MIN, MAX
VAR, STD, STDERR, CV, T, PRT, LCLM, UCLM	N, SUM, USS

Note: For information about statistic formulas, see "Keywords and Formulas" in Base SAS Procedures Guide. [cautionend]

For cubes that are not loaded from a fully summarized data source (that is, you specified a data source by using the DATA | FACT= option), some statistics use formats taken from the input data source. Specifically, if the statistic is SUM, MIN, MAX, RANGE, AVG, STD, STDERR, LCLM, or UCLM, then PROC OLAP uses the format that is assigned to the column specified by the COLUMN | ANALYSIS= option. The following table lists the formats used for the other supported statistics:

Default Formats Used for Statistics
Statistic	Format Used
CSS	BEST.
CV	8.2
N	12.0
NMISS	10.0
PRT	6.4
T	7.3
USS	BEST.
VAR	BEST.

For cubes that are loaded from a fully summarized data source (that is, you specified the data source by using the AGGREGATION statement), the default format is BEST12.

To override the default formats, you can either set the FORMAT= option or use a SAS FORMAT statement.

Note: The FORMAT= option also overrides a FORMAT statement. [cautionend]

Note: When you rebuild a cube that has been physically deleted, the rebuilt cube still uses the formats originally saved in the cube's metadata. This means that the rebuilt cube does not automatically include any formatting changes that you might have made in the input data source. To manually specify the new formats, edit and rebuild the cube by using SAS OLAP Cube Studio. [cautionend]

COLUMN | ANALYSIS=anlvar

specifies the name of a numeric column that is contained in the cube's input data source. (You can use a column as a measure even if it is also being used as a level.)

If the cube is based on an unsummarized data source, then anlvar is the name of the column in that data source from which the measure will be calculated. Use COLUMN= to specify the column.

If the cube is based on a summarized data source, then anlvar can be the name of the numeric column in the data source that was used as the analysis variable for the pre-calculated measure. Use ANALYSIS= to specify the column. It can also be a name that identifies a logical association between measures with the same anlvar name.

For example, if your cube has three measures, N, SUM, and AVERAGE, and if those measures were derived from the same analysis variable, then you could specify ANALYSIS=Sales to logically link the three measures through their shared analysis variable. You would also identify the analysis variable in the AGGR_COLUMN= option.

As a further illustration, assume that you were building a cube with an NWAY aggregation that was specified using a summarized SAS dataset. The dataset contains the columns Country, Region, Division, Year, Quarter, Month, SumOfSales, and NumOfSales. You would use two MEASURES statements, one for SumOfSales and another for NumOfSales, as follows.

measure Sales_Sum
   stat=sum
   aggr_column="SumOfSales"
   analysis="Sales"
   desc='Sum of Sales'
   units='Dollars'
   format=dollar10.2
   ;
measure Sales_N
   stat=n
   aggr_column="NumOfSales"
   analysis="Sales"
   desc='Number of Sales'
   units='Dollars'
   format=dollar10.2
   ;

The Sales column becomes logically linked with the physical columns SumOfSales and NumOfSales.

If the cube consists of a combination of summarized and unsummarized data sources, then anlvar refers to both a physical and a logical entity. For example, you might have a cube that requires a physical analysis variable to create a crossing, but that same cube already contains other, higher-level aggregations. In this case, the analysis variable is also used to logically link the measures in the pre-existing aggregations that were derived from the same input column. You would also identify the analysis variable in the AGGR_COLUMN= option.

Default:

measure-name

Interaction:

An unsummarized data source is specified with the DATA | FACT= option in the PROC OLAP statement. A summarized data source is specified with the TABLE= option in an AGGREGATION statement.

Note: The COLUMN argument is not required for the NUNIQUE statistic and will be ignored for the NUNIQUE statistic if specified. [cautionend]

Options

AGGR_COLUMN=input-column

specifies the name of the numeric column in the summarized input data that contains the values for the measure. The source of the summarized input data is specified in the AGGREGATION statement. This option is valid only for stored statistics.

CAPTION='string'

specifies a maximum of 200 characters that can be used to create a meaningful description of the measure. Third-party applications that report on cube data might display this description. If the text includes blank spaces or any characters that are not permitted in a valid SAS name, then enclose the text within quotation marks.

Note: Cubes that are built with captions that are longer than 200 characters cannot be fully registered in the SAS Metadata Repository. Captions will be truncated to 200 characters. [cautionend]

Default:

The default is based on the statistic and the COLUMN= value, as shown in the following table. For example, if the statistic is SUM and the COLUMN= value is Sales, then the default caption is Sum of Sales.

Defaults for the CAPTION= Option If No Caption Is Specified
Statistic Used for Measure	Default Caption
AVG	Average measure-column-name
CSS	Corrected Sum of Squares of measure-column-name
CV	Measure-column-name Coefficient of Variation
LCLM	Measure-column-name Lower Confidence Limit
MAX	Maximum measure-column-name
MIN	Minimum measure-column-name
N	Number of Values for measure-column-name
NMISS	Number of Missing Values for measure-column-name
NUNIQUE	Number of Unique Values for level-name in hierarchy-name
PRT	Probability of Greater Absolute Value for measure-column-name
RANGE	Measure-column-name Range
STD	Measure-column-name Standard Deviation
STDERR	Measure-column-name Standard Error of Mean
SUM	Sum of measure-column-name
T	Measure-column-name T Value
UCLM	Measure-column-name Upper Confidence Limit
USS	Measure-column-name Uncorrected Sum of Squares
VAR	Measure-column-name Variance

DEFAULT

identifies a measure as the default measure for the cube.

Default:

The measure defined in the first MEASURE statement

DESC | DESCRIPTION='string'

specifies any number of characters that can be used to create a meaningful description of the measure. Third-party applications that report on cube data might display this description. If the text includes blank spaces or any characters that are not permitted in a valid SAS name, then enclose the text within quotation marks.

Default:

measure-name

FORMAT=sas-format-name

specifies the SAS format to be used to display the value of the measure. This format overrides the default format (see STAT= for more information) and any format that is specified in a SAS FORMAT statement. Both SAS-supplied formats and user-defined formats are supported.

Note: When you rebuild a cube that has been physically deleted, the rebuilt cube still uses the formats that were originally saved in the cube's metadata. This means that the rebuilt cube does not automatically include any formatting changes that you might have made in the input data source. To manually specify the new formats, edit and rebuild the cube by using SAS OLAP Cube Studio. [cautionend]

HIERARCHY='string'

specifies the hierarchy in which the level resides. This option is used only with the NUNIQUE statistic. If there is only one hierarchy, then the option can be omitted.

Note: The HIERARCHY= option will be ignored for non-NUNIQUE statistics if specified. [cautionend]

INCLUDE_CALCULATED_MEMBER | INCLUDE_CALC

specifies that calculated members are included in the NUNIQUE count for the measure statement. This option applies to the STAT= NUNIQUE statistic option only.

LEVEL= 'string'

specifies the level for which a unique count is determined. This option is used only with the NUNIQUE statistic. The default is the Measure name.

Note: The LEVEL= option is ignored for non-NUNIQUE statistics if specified. [cautionend]

NOINCLUDE_CALCULATED_MEMBER |NOINCLUDE_CALC

specifies that calculated members are not included in the MEASURE statement.

UNITS='string'

specifies a maximum of 256 characters that can be used to create a meaningful description of the measure's units (for example, "pounds sterling"). Third-party applications that report on cube data might display this description. If the text includes blank spaces, mixed-case letters, special characters, then enclose the text within quotation marks.

Top of Page