INFOMAPS Procedure

INSERT DATAITEM Statement

Inserts a data item into the current information map.

Syntax

Form 1:

INSERT DATAITEM COLUMN="data-source-ID"."column-name" <options>;

Form 2:

INSERT DATAITEM EXPRESSION="expression-text" <options>;

Form 3:

INSERT DATAITEM HIERARCHY="dimension"."hierarchy" <options>;

Form 4:

INSERT DATAITEM MEASURE="OLAP-measure" <options>;

Summary of Optional Arguments

tells an application that uses the information map what actions it can present to its users to perform on the result data set returned by the information map.
specifies how a measure data item is aggregated when it is used in a query.
removes one or more functions from the set of aggregate functions available to a data item.
specifies the aggregate functions that are available to a data item.
specifies whether the data item is a category or a measure.
specifies additional properties for the data item.
specifies the description of the data item, which can be viewed by the information map consumer.
specifies the folder in the information map into which to insert the data item.
specifies the SAS format of the data item.
specifies the ID assigned to the data item being inserted.
specifies the name assigned to the data item in the information map.
specifies the data type of the data item's expression.
specifies what method an application that uses the information map is to use in generating a list of values for this data item to present to a user.

Required Arguments

The INSERT DATAITEM statement must include one of the following arguments:

COLUMN="data-source-ID"."column-name"
specifies a column.
data-source-ID
is the identifier of a data source in the current information map. It must match the identifier of the table that contains the column, as shown in the following example:
insert datasource sasserver="SASMain"
   table="Common"."WORLDPOP2002"
   id="PopulationData";

insert dataitem column="PopulationData"."Projected_Population_millions_";
column-name
is the SAS name of a column defined in the relational table associated with data source ID. The INFOMAPS procedure inserts a data item for this column into the information map.
Restriction:This argument applies only to a relational data source.
Interaction:You can specify only one of the COLUMN=, EXPRESSION=, HIERARCHY=, or MEASURE= arguments in an INSERT DATAITEM statement.
EXPRESSION="expression-text"
specifies the combination of data elements, literals, functions, and mathematical operators that are used to derive the value of a data item when the information map is used in a query. The form of the expression-text value depends on the type of data item being defined:
For relational data items
Any reference to physical or business data in a relational table must be enclosed in double angle brackets (<< >>). Everything between double angle brackets is maintained just as it is. That is, case and blank spaces are maintained.
If you are referring to a physical column, then you must qualify the column with the data source ID. For example, <<Transaction.Sales_Tax>>.
If you are referring, in an expression, to a data item in the current information map, then you do not need to qualify the data item ID. You can refer explicitly to the current information map by specifying root as the qualifier. For example, <<root.MODEL_ID>>.
For OLAP data items
Expressions must resolve to a valid, one-dimensional MDX set. Use double angle brackets (<< >>) to enclose references to an OLAP measure, OLAP dimension, OLAP hierarchy, or an OLAP level. Use single sets of square brackets ([ ]) to enclose a reference to an OLAP member. For example:
<<Measures.new_business_value_sum>>,
<<campaigns>>,
<<campaigns.campaigns>>,
[campaigns].[All campaigns].[ADVT]
Note: If you use the Information Maps engine to access an information map containing character type data items created with the EXPRESSION= argument, you should be aware of the EXPCOLUMNLEN= option of the LIBNAME statement and the EXPCOLUMNLEN= data set option. By default, the Information Maps engine sets the data length for columns of these data items to 32 characters. You can use the EXPCOLUMNLEN= statement option or data set option to change the default length. For more information about the EXPCOLUMNLEN= statement option, see LIBNAME Statement.For more information about the EXPCOLUMNLEN= data set option, see EXPCOLUMNLEN= Data Set Option.
Interaction:You can specify only one of the COLUMN=, EXPRESSION=, HIERARCHY=, or MEASURE= arguments in an INSERT DATAITEM statement.
Tip:If you are using the INSERT DATAITEM statement to insert a non-calculated data item from physical data, it is preferable for performance reasons to use the COLUMN=, HIERARCHY=, or MEASURE= argument instead of the EXPRESSION= argument.
HIERARCHY="dimension"."hierarchy"
specifies a physical hierarchy.
dimension
is the name of a dimension in the current OLAP data source.
hierarchy
is the name of a hierarchy that is defined in the specified dimension. For example:
insert datasource sasserver="SASMain" cube="Simba";
insert dataitem hierarchy="MARKET"."GEOGRAPHICAL" id="Geographical";
The INFOMAPS procedure inserts a data item for this hierarchy into the information map.
By default, a data item inserted using the HIERARCHY= argument returns the top-level members of the hierarchy when used in a query. If you want the data item to return members from other levels, you should instead define it with the EXPRESSION= argument. In the following example, the data item Geographical1 returns the top-level members of the GEOGRAPHICAL hierarchy (for example, REGION), while the data item Geographical2 returns all members of the level:
insert dataitem hierarchy="MARKET"."GEOGRAPHICAL" id="Geographical1";
insert dataitem expression="<<MARKET.GEOGRAPHICAL>>"
   id="Geographical2"
   type=character;
If the GEOGRAPHICAL hierarchy contains another level named STATE and you want a data item to return members from the STATE level, then you should use the EXPRESSION option to create that data item. For example,
insert dataitem expression="<<MARKET.GEOGRAPHICAL.STATE>>.members"
   id="State"
   type=character;
Restriction:This argument applies only to an OLAP data source.
Interaction:You can specify only one of the COLUMN=, EXPRESSION=, HIERARCHY=, or MEASURE= arguments in an INSERT DATAITEM statement.
MEASURE="OLAP-measure"
specifies a physical measure.
OLAP-measure
is the name of a measure that is defined in the measures dimension in the OLAP data source for the current information map. For example:
insert datasource sasserver="SASMain" cube="SASMain - OLAP schema".Simba;
insert dataitem measure="ACTUALAVE" id="Average Actual";
The INFOMAPS procedure inserts a data item for this OLAP measure into the information map.
Restriction:This argument applies only to an OLAP data source.
Interaction:You can specify only one of the COLUMN=, EXPRESSION=, HIERARCHY=, or MEASURE= arguments in an INSERT DATAITEM statement.

Optional Arguments

ACTIONS=(actions-list)
tells an application (such as SAS Web Report Studio) that uses the information map what actions it can present to its users to perform on the result data set returned by the information map. For example, a user of SAS Web Report Studio can right-click a column heading of a report and select Sort from the pop-up menu to sort the values in that column. Specifying actions=(nosort) tells SAS Web Report Studio not to offer the Sort menu selection for this data item.
The following actions can be specified:
RANK | NORANK
specifies whether the following items can be ranked:
  • relational data item values
  • members of OLAP data items that represent hierarchies
The setting for this option does not affect the ability of the information map consumer to rank row and column values in a generated result set.
Default:RANK
SORT | NOSORT
specifies whether the following items can be sorted:
  • relational data item values
  • members of OLAP data items that represent hierarchies
The setting for this option does not affect the ability of the information map consumer to sort OLAP data values.
Default:SORT
FILTER | NOFILTER
specifies whether members of OLAP data items that represent hierarchies can have filters applied to them. The setting for this option does not affect the ability of the information map consumer to filter on row and column values in a generated result set, and it does not affect test queries that are run from the Test the Information Map dialog box in Information Map Studio.
Default:FILTER
Restriction:This option value applies only to non-measure OLAP data items.
NAVIGATE | NONAVIGATE
specifies whether the member of OLAP data items that represent hierarchies can be drilled up or down, or expanded and collapsed.
Default:NAVIGATE
Restriction:This option value applies only to non-measure OLAP data items.
Default:All actions are enabled (RANK, SORT, FILTER, or NAVIGATE) unless specifically disabled (NORANK, NOSORT, NOFILTER, or NONAVIGATE).
Example:
actions=(RANK SORT NOFILTER NONAVIGATE)
AGGREGATION=aggregate-function
specifies how a measure data item is aggregated when it is used in a query. Values for the aggregate-function value are shown in Aggregate Functions. For more information about the aggregate functions (except for InternalAggregation and InternalAggregationAdditive), see "Summarizing Data: Using Aggregate Functions" in the "Retrieving Data from a Single Table" chapter in the SAS SQL Procedure User's Guide.
The special value InternalAggregation specifies that the values of the measure data item are aggregated by a nonadditive expression. A nonadditive expression is one for which the arithmetic summation of the aggregated values of the measure data item is not equal to the arithmetic summation of all of the detail values of that data item. For example, SUM(COL1)/COUNT(COL1) is a nonadditive expression. If you specify that a data item has a nonadditive expression, then the total for that data item is calculated by applying the specified expression to the detail values of the data item.
The special value InternalAggregationAdditive specifies that values of the measure data item are aggregated by an additive expression. An additive expression is one for which the arithmetic summation of the aggregated values of the measure data item is equal to the arithmetic summation of all of the detail values of that data item. For example, SUM(COL1*COL2) is an additive expression.
If you do not specify an AGGREGATION= option, then the default aggregate function is defined as follows:
  • If the expression type for the data item is numeric, then the default aggregate function is SUM.
  • If the expression type for the data item is character, date, time, or timestamp, then the default aggregate function is COUNT.
  • If the data item is based on a measure, then the default aggregate function is InternalAggregation.
Aggregate Functions
Function
Definition
Available to nonnumeric item that is a measure
AVG
average (mean) of values
AvgDistinct
average (mean) of distinct values
COUNT
number of nonmissing values
icon: check mark (Yes)
CountDistinct
number of distinct nonmissing values
icon: check mark (Yes)
CountPlusNMISS
number of values (including the number of missing values)
icon: check mark (Yes)
CountPlusNMISSDistinct
number of distinct values (including the number of distinct missing values)
icon: check mark (Yes)
CSS
corrected sum of squares
CSSDistinct
corrected sum of squares of distinct values
CV
coefficient of variation (percent)
CVDistinct
coefficient of variation (percent) of distinct values
FREQ
number of nonmissing values
icon: check mark (Yes)
FreqDistinct
number of distinct nonmissing values
icon: check mark (Yes)
InternalAggregation
defined in an expression (nonadditive)
icon: check mark (Yes)
InternalAggregationAdditive
defined in an expression (additive)
icon: check mark (Yes)
MAX
largest value
icon: check mark (Yes)
MEAN
mean (average) of values
MeanDistinct
mean (average) of distinct values
MIN
smallest value
icon: check mark (Yes)
N
number of nonmissing values
icon: check mark (Yes)
NDistinct
number of distinct nonmissing values
icon: check mark (Yes)
NMISS
number of missing values
icon: check mark (Yes)
NMISSDistinct
number of distinct missing values
icon: check mark (Yes)
PRT
probability of a greater absolute value of Student's t
PRTDistinct
probability of a greater absolute value of Student's t of distinct values
RANGE
range of values
RANGEDistinct
range of distinct values
STD
standard deviation
STDDistinct
standard deviation of distinct values
STDERR
standard error of the mean
STDERRDistinct
standard error of the mean of distinct values
SUM
sum of values
SumDistinct
sum of distinct values
T
Student's t value for testing the hypothesis that the population mean is zero
TDistinct
Student's t value for testing the hypothesis that the population mean of distinct values is zero
USS
uncorrected sum of squares
USSDistinct
uncorrected sum of squares for distinct values
VAR
variance
VarDistinct
variance of distinct values
Restrictions:The AGGREGATION= option applies only to relational data items that are measures.

If the data item is defined by an expression that references a measure data item or that contains an aggregate function, then the only valid values for the AGGREGATION= option are InternalAggregation or InternalAggregationAdditive.

Interaction:If you use the AGGREGATION= option in the same INSERT DATAITEM statement as either the AGGREGATIONS_DROP_LIST= or the AGGREGATIONS_KEEP_LIST= option, then the INFOMAPS procedure sets the AGGREGATIONS_DROP_LIST= or the AGGREGATIONS_KEEP_LIST= option first.
AGGREGATIONS_DROP_LIST=(aggregate-function-list)
removes one or more functions from the set of aggregate functions available to a data item. See Aggregate Functions for information about aggregate functions.
Separate multiple aggregate functions in the list with a blank space. For example:
aggregations_drop_list=(Freq FreqDistinct CSSDistinct)
Note: Use AGGREGATIONS_DROP_LIST= if there are only a few aggregate functions that you want excluded from the total set. Use AGGREGATIONS_KEEP_LIST= if there are only a few aggregate functions that you want included.
Default:If you specify neither AGGREGATIONS_DROP_LIST= nor AGGREGATIONS_KEEP_LIST=, then all of the valid aggregate functions for the data item are available.
Restriction:This option applies only to relational data items that are measures.
Interaction:If you use the AGGREGATIONS_DROP_LIST= option in the same INSERT DATAITEM statement as the AGGREGATION= option, then the INFOMAPS procedure sets the AGGREGATIONS_DROP_LIST= option first.
AGGREGATIONS_KEEP_LIST=(aggregate-function-list)
specifies the aggregate functions that are available to a data item. Functions not listed in aggregate-function-list are excluded. See Aggregate Functions for information about aggregate functions.
Separate multiple aggregate functions with a blank space. For example:
aggregations_keep_lisT=(Freq FreqDistinct CSSDistinct)
Note: Use AGGREGATIONS_KEEP_LIST= if there are only a few aggregate functions that you want included. Use AGGREGATIONS_DROP_LIST= if there are only a few aggregate functions that you want excluded from the total set.
Default:If you specify neither AGGREGATIONS_KEEP_LIST= nor AGGREGATIONS_DROP_LIST=, then all of the valid aggregate functions for the data item are available.
Restriction:This option applies only to relational data items that are measures.
Interaction:If you use the AGGREGATIONS_KEEP_LIST= option in the same INSERT DATAITEM statement as the AGGREGATION= option, then the INFOMAPS procedure sets the AGGREGATIONS_KEEP_LIST= option first.
CLASSIFICATION=CATEGORY | MEASURE
specifies whether the data item is a category or a measure. The classification of the data item determines how it is processed in a query. A data item that is a measure can be used in computations or analytical expressions. A data item that is a category is used to group measures using an applied aggregate function.
If you do not specify the CLASSIFICATION= option, the INFOMAPS procedure assigns a default classification based on the following:
  • the contents of the expression if the EXPRESSION= argument is used
  • the data type of the physical data if the COLUMN=, HIERARCHY=, or MEASURE= argument is used
For a relational data source, if a data item is created from a physical column, then CATEGORY is the default classification unless the physical data is of type NUMERIC and is not a key. Data items inserted with the EXPRESSION= argument also default to CATEGORY. However, if the expression contains an aggregation, the default classification is MEASURE instead. For an OLAP data source, if the HIERARCHY= argument is used, then the default classification is CATEGORY. If the MEASURE= argument is used, then the default classification is MEASURE. If the EXPRESSION= argument is used, then the default classification is MEASURE if the specified TYPE= value is NUMERIC. Otherwise, the default classification is CATEGORY.
CUSTOM_PROPERTIES=(custom-properties-list)
specifies additional properties for the data item. Custom properties are supported by specific SAS applications such as SAS Marketing Automation. The form of the custom-properties-list value is
("property-name-1" "property-value-1" <"description-1">)
...
("property-name-n" "property-value-n" <"description-n">)
where
property-name
specifies the name of the property.
Restriction:Property names cannot begin with an underscore (_) character.
Requirement:Property names must be unique. If a specified property name already exists in the data item, then the INSERT DATAITEM statement will fail. Therefore, it is recommended that you add a prefix or suffix to the property name to ensure uniqueness.
property-value
specifies the value of the property.
description
specifies the description of the property. The description is optional.
Example:
custom_properties=(("MA_Level" "Nominal" "Descriptive text goes here.")
                   ("MA_UseInSubjectIdTop" "Subject_ID_" "Subject ID"))
DESCRIPTION="descriptive-text"
specifies the description of the data item, which can be viewed by the information map consumer.
Alias:DESC=
Restriction:Although you can specify more than 256 characters for the data item description, SAS programs can use only the first 256 characters of the description.
FOLDER="folder-name" </CREATE>
FOLDER="folder-location" </CREATE>
specifies the folder in the information map into which to insert the data item.
The following rules apply:
  • If the folder is in the root directory of the information map, then you can specify the folder by name without an initial slash. For example, folder="CUSTOMERS".
  • If the folder is not in the root directory, then you must specify the location of the folder beginning with a slash. For example, folder="/CUSTOMERS/Europe".
/CREATE
specifies that the named folder or location is created automatically if it does not already exist.
Alias:LOCATION=
Restrictions:The following characters are not valid in a folder name:
  • / \
  • null characters
  • non-blank nonprintable characters

A folder name can contain blank spaces, but it cannot consist only of blank spaces.

FORMAT="format-name"
specifies the SAS format of the data item.
If you do not specify a SAS format, or if you specify an empty string as the format value, the INFOMAPS procedure sets a default format for the data item based on the following factors:
  • the classification of the data item
  • whether there is a format defined in the physical or business resource referenced in the data item expression
  • the expression type of the data item
Restriction:The FORMAT= option applies only to relational data items and OLAP measures.
ID="data-item-ID"
specifies the ID assigned to the data item being inserted. The ID is a value that uniquely identifies the associated data item in the current information map.
If you do not specify the ID= option, the INFOMAPS procedure generates an ID. The value that is generated for a data item depends on how the data item is inserted:
  • If the NAME= option is specified, the data item name is used as the seed for generating the ID.
  • If the NAME= option is not specified, how the ID is generated depends on whether the data item is inserted from a physical column or from the EXPRESSION=, HIERARCHY=, or MEASURE= argument, as follows:
    • If the data item is inserted from a physical column using INSERT DATAITEM with the COLUMN= argument specified or using INSERT DATASOURCE with either the _ALL_ or the COLUMNS= option specified, then the ID is generated from either the SAS name or label of the physical column.
      The settings of the USE_LABELS=, REPLACE_UNDERSCORES=, and INIT_CAP= options determine the exact value and casing of the ID.
    • If the data item is inserted with the EXPRESSION= option, then the INFOMAPS procedure assigns a unique ID of the form DataItemnumber, where number is an internally maintained counter for ID generation. This counter is also used for generating IDs for other business data, including data sources, filters, and relationships.
    • If the data item is inserted with the HIERARCHY= or MEASURE= option, then the ID is generated from the caption of the hierarchy or measure.
The INSERT DATAITEM statement prints a note displaying the ID of the data item if the ID has a different value from the data item name. You can use the LIST statement to view the IDs of all the data items in the current information map.
Restrictions:Nulls and non-blank nonprintable characters are not valid in an ID. The following characters are invalid:
. < > [ ] { } \ / ^ @ ~
If a name contains any of these characters, they are replaced with an underscore (_) when the ID is generated from the name.

The first 32 characters of an ID must be unique across an information map. An error occurs if you specify an ID that is the same as an existing ID (data item, data source, filter, or other). An ID that differs only by case from another ID in the current information map is not considered unique.

NAME="data-item-name"
specifies the name assigned to the data item in the information map. A name is optional, descriptive text that makes it easier for business users to understand what the data is about. A data item's name is for display purposes only. You refer to a data item in code using its ID rather than its name.
If you do not specify a name, the name defaults to one of the following, depending on how the data item is defined:
  • If the COLUMN= argument is used, then the name defaults to the column name or column label (based on the setting of the USE_LABELS= option from the NEW INFOMAP or UPDATE INFOMAP statement).
  • If the EXPRESSION= argument is used, then the INFOMAPS procedure provides a default name.
  • If the HIERARCHY= or MEASURE= argument is used, then the name defaults to the caption of the hierarchy or measure.
Restrictions:There is no limit on the length of the name of a relational data item. OLAP data item names cannot contain more than 245 characters.

A data item name can contain blank spaces, but it cannot consist only of blank spaces. Nulls and non-blank nonprintable characters are not valid characters in a data item name. A data item name can contain the following special characters, but they are replaced with an underscore (_) in the ID that is generated from the name:

. < > [ ] { } \ / ^ @ ~
Square brackets ([ ]) are not valid in an OLAP data item name.

TYPE=NUMERIC | CHARACTER | DATE | TIME | TIMESTAMP
specifies the data type of the data item's expression.
Restriction:For OLAP data, the only valid types are NUMERIC and CHARACTER.
Interaction:If you specify the EXPRESSION= option, then you must specify the TYPE= option. If you specify the COLUMN=, HIERARCHY=, or MEASURE= option, then you can omit the TYPE= option. In this case, the INFOMAPS procedure derives the type from the type of the corresponding data.
VALUE_GENERATION=NONE | DYNAMIC | (custom-values-list)
specifies what method an application (for example, SAS Web Report Studio) that uses the information map is to use in generating a list of data item values for this data item to present to a user when the user is constructing a filter or responding to a prompt. The following value generation methods can be specified:
NONE
specifies that the list of values should not be generated. The application will require its user to manually type data item values.
DYNAMIC
specifies that the list that contains all of the data item's values be dynamically generated. The list is generated by querying the data source to retrieve the data item's values.
custom-values-list
defines a custom list of values for the data item. The form of the custom-values-list value is
("unformatted-value-1" <"formatted-value-1">)
...
("unformatted-value-n" "<"formatted-value-n">)
where
unformatted-value
specifies the unformatted value for a report.
formatted-value
specifies the formatted value for a report.
Note: The formatted value is optional. It is used for display purposes only. For example, SAS Web Report Studio displays these values to the user of a filter and prompt definition dialog boxes so that the user can see what the values will look like after they are formatted for a report.
Note:To refer to a custom value later during an update, you must specify the unformatted value rather than the formatted value.
Example:
value_generation=( 
                   ("CA" "California")
                   ("NC")
                   ("NY" "New York") 
                 )

Example

/* Use the COLUMN= option to insert a data item for a physical column. */
insert dataitem column="TRANSACTION"."Sales_Amount" 
   id="Total_Sales";