Previous Page | Next Page

The INFOMAPS Procedure

INSERT DATAITEM Statement

Inserts a data item into the current information map.
INSERT DATAITEM
COLUMN="data-source-ID"."column-name" | EXPRESSION="expression-text" | HIERARCHY="dimension"."hierarchy" | MEASURE="OLAP-measure"
<ACTIONS=(actions-list)>
<AGGREGATION=aggregate-function>
<AGGREGATIONS_DROP_LIST=(aggregate-function-list)>
<AGGREGATIONS_KEEP_LIST=(aggregate-function-list)>
<CLASSIFICATION=CATEGORY | MEASURE>
<CUSTOM_PROPERTIES=(custom-properties-list)>
<DESCRIPTION="descriptive-text">
<FOLDER=folder-name | "folder-location" </CREATE>>
<FORMAT="format-name">
<ID="data-item-ID">
<NAME="data-item-name">
<TYPE=NUMERIC | CHARACTER | DATE | TIME | TIMESTAMP>
<VALUE_GENERATION=NONE | DYNAMIC | custom-values-list>;
Required Argument

COLUMN="data-source-ID"."column-name"

specifies a column. The 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_";

The 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.

Note:   If you are using 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. By default, the Information Maps engine sets the data length for columns of these data items to 32 characters. You can use the EXPCOLUMNLEN= option to change the default length. For more information about the EXPCOLUMNLEN= option, see Other LIBNAME Statement Options for the Information Maps Engine and EXPCOLUMNLEN= Data Set Option.  [cautionend]

Requirement: Relational data: 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>> .
Requirement: OLAP data: Expressions for OLAP data items 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]
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. The dimension is the name of a dimension in the current OLAP data source. The 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 option 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="measure"

specifies a physical measure. The 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 option 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.
Options

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 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: The action is enabled (RANK, SORT, FILTER, or NAVIGATE) unless it is 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 AGGREGATION= option 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 9.2 SQL Procedure User's Guide.

The special value InternalAggregation specifies that the values of the measure data item are aggregated by a non-additive expression. A non-additive 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 non-additive expression. If you specify that a data item has a non-additive 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.

Restriction: The AGGREGATION= option applies only to relational data items that are measures.
Restriction: 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.
Default: If you do not specify an AGGREGATION= option, then the default aggregate function is defined as follows:
SUM

if the expression type for the data item is numeric

COUNT

if the expression type for the data item is character, date, time, or timestamp

InternalAggregation

if the data item is based on a measure

If the default function is not available, then the first aggregate function in the list of functions that SAS maintains becomes the default.
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.

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 [Aggregate Functions]
CountDistinct, number of distinct nonmissing values [Aggregate Functions]
CountPlusNMISS number of values (including the number of missing values) [Aggregate Functions]
CountPlusNMISSDistinct number of distinct values (including the number of distinct missing values) [Aggregate Functions]
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 [Aggregate Functions]
FreqDistinct number of distinct nonmissing values [Aggregate Functions]
InternalAggregation defined in an expression (non-additive) [Aggregate Functions]
InternalAggregationAdditive defined in an expression (additive) [Aggregate Functions]
MAX largest value [Aggregate Functions]
MEAN mean (average) of values
MeanDistinct mean (average) of distinct values
MIN smallest value [Aggregate Functions]
N number of nonmissing values [Aggregate Functions]
NDistinct number of distinct nonmissing values [Aggregate Functions]
NMISS number of missing values [Aggregate Functions]
NMISSDistinct number of distinct missing values [Aggregate Functions]
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

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.  [cautionend]

Restriction: This option applies only to relational data items that are measures.
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.
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.  [cautionend]

Restriction: This option applies only to relational data items that are measures.
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.
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 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.

Requirement: Property names must be unique. If a 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.
Restriction: Property names cannot begin with an underscore (_) character.
property-value

specifies the value of the property.

description

specifies the description of the property. The description is optional.

For 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" | "folder-location" </CREATE>

specifies the folder in the information map into which to insert the data item.

  • 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=
Restriction: The following characters are not valid in a folder name:

  • / \

  • null characters

  • non-blank nonprintable characters
Restriction: 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.

    • If the data item is inserted from a physical column in one of the following ways, then the ID is generated from either the SAS name or label of the physical column:

      INSERT DATAITEM with the COLUMN= argument specified

      INSERT DATASOURCE with either the _ALL_ or the COLUMNS= option specified

      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.

Restriction: 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.
Restriction: 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.

Restriction: 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.
Restriction: 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 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.  [cautionend]

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") 
                 )
Examples

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

Previous Page | Next Page | Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.