INFOMAPS Procedure

INSERT RELATIONSHIP Statement

Inserts a join into the current information map.

Syntax

INSERT RELATIONSHIP CONDITION="conditional-expression"
LEFT_TABLE="data-source-ID-1" RIGHT_TABLE="data-source-ID-2" <options>;

Summary of Optional Arguments

CARDINALITY=ONE_TO_ONE | ONE_TO_MANY | MANY_TO_ONE | MANY_TO_MANY | UNKNOWN

describes the relationship between rows in the first data source and rows in the second data source.

CUSTOM_PROPERTIES=(custom-properties-list)

specifies additional properties for the relationship.

DESCRIPTION="descriptive-text"

specifies the description of the relationship that is created.

ID="relationship-ID"

specifies the ID of the relationship to be inserted.

JOIN=INNER | LEFT | RIGHT | FULL

specifies the type of join.

Required Arguments

CONDITION="conditional-expression": specifies the columns to be joined to create a single relationship between two tables.

Requirement:The columns referenced in the conditional expression must be qualified with the associated data source ID and must be enclosed in double angle brackets (<< >>).

LEFT_TABLE="data-source-ID-1": specifies the data source ID of the first table in the relationship.

RIGHT_TABLE="data-source-ID-2": specifies the data source ID of the second table in the relationship.

Optional Arguments

CARDINALITY=ONE_TO_ONE | ONE_TO_MANY | MANY_TO_ONE | MANY_TO_MANY | UNKNOWN: describes the relationship between rows in the first data source and rows in the second data source.

Default:If the CARDINALITY= option is not specified, then the cardinality defaults to UNKNOWN.

CUSTOM_PROPERTIES=(custom-properties-list)

specifies additional properties for the relationship. 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 relationship, then the INSERT RELATIONSHIP 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.

DESCRIPTION="descriptive-text": specifies the description of the relationship, which can be viewed by the information map consumer.

Alias:DESC=

ID="relationship-ID"

specifies the ID of the relationship to be inserted. If you do not specify an ID, the INFOMAPS procedure generates a unique ID.

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

JOIN=INNER | LEFT | RIGHT | FULL

specifies the type of join. The type can be one of the following:

INNER: returns all the rows in one table that have one or more matching rows in the other table

LEFT: returns all the rows in the specified left table, plus the rows in the specified right table that match rows in the left table

RIGHT: returns all the rows in the specified right table, plus the rows in the specified left table that match rows in the right table

FULL: returns all the rows in both tables

Default:INNER

Details

The INSERT RELATIONSHIP statement applies only to relational tables. If a join already exists between the specified tables, then the new join replaces the old one, unless a new and unique ID is specified.

When specifying a table, you must specify the data source ID associated with the table in an information map. IDs are case sensitive. You can define data source ID values when you insert or update the data sources. You can use the LIST DATASOURCES statement to see the IDs of data sources in your information map.

Example

insert relationship 
   left_table="CUSTOMER" 
   right_table="TRANSACTION"
   condition="(<<CUSTOMER.Cust_ID>>=<<TRANSACTION.Cust_ID>>)"
   join=inner
   id="join_customer_to_transaction";