The OLAP Procedure

MEASURE Statement

Defines the cube's measures and indicates how they map to the input data. 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. All cube aggregations have identical measures.

Syntax

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

Details

Required Arguments

measure-name: specifies a valid SAS name for the measure. The name must be unique. For naming guidelines, seeNaming 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.

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.

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.

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. The FORMAT= option also overrides a FORMAT statement. 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.

COLUMN | ANALYSIS=variable-name

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 variable-name 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 variable-name 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 variable-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.

As a further illustration, assume that you were building a cube with an NWAY aggregation that was specified using a summarized SAS data set. The data set 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 variable-name 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. The default value is the measure name.

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. The COLUMN argument is not required for the NUNIQUE statistic and will be ignored for the NUNIQUE statistic if specified.

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.

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. The default value is 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. The default value is caption.

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 formats supplied by SAS 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.

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.

INCLUDE_CALCULATED_MEMBER | INCLUDE_CALC: specifies that calculated members are included in the NUNIQUE count for the measure statement. This option applies to the 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.

MLSID=n: is a positive integer identifier between 0 and MACINT (2147483647) that identifies the observation in the data set that contains the translated caption and description for the measure. This identifier is expected in the MLSID column of the data set specified by DIMTABLECAPPREF=, DIMTABLELIBREF=, and the USER_DEFINED_TRANSLATIONS statement.

Note: For further information on MLS caption tables and MLSID, see USER_DEFINED_TRANSLATIONS Statement.

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.