The TRANASSIGN Procedure

Overview
Syntax:
     TRANASSIGN Procedure
     PROC TRANASSIGN Statement
     TRANSACTION Statement
     DIMENSION Statement
Example
ChildOf Function

Overview

The TRANASSIGN procedure provides a batch method for calculating a Profitability Management model. The procedure takes the transaction tables in your input directory, applies rules in the model to make calculations, and stores the calculated results in columns that are appended to your transaction tables in the output directory. Errors and warnings are written to the SAS log file.

Syntax:

PROC TRANASSIGN Statement

The PROC TRANASSIGN statement specifies the input data sources for transaction table schema, transaction behaviors, and assignment rules.

Required Arguments:

Note: The following arguments are required only if DIAGNOSTIC= is not specified. If DIAGNOSTIC= is specified, then the following arguments are ignored.

AR | ASSIGNMENTRULE=assignment-rule-table

Specifies the data source for the assignment rule table. The table must conform to the following schema:

Name	Data Type	Maximum Length	Description
Name	Char	64	The name of the assignment rule
Condition	Char	4096	The expression that is used to select transactions as assignment destinations; the expression should be SAS-compliant and only use variables from the transaction table; the only exception is that it can use the customized dimension-member filtering function ChildOf
Formula	Char	4096	The formula expression that is used to calculate the driver quantity for a particular assignment; the expression must be SAS-compliant and only use variables from the transaction table
Type	Char	64	The transaction table group that the assignment rule applies to; the value of this field is matched by the value of the TYPE parameter in a TRANSACTION statement

 

BH | BEHAVIOR=behavior-table

Specifies the data source for the behavior table. The behavior table must conform to the following schema:

Name	Data Type	Maximum Length	Description
Name	Char	32	The name of the behavior
Period	Char	64	The period name for the behavior row
AssignmentRule	Char	64	The name of the assignment rule used by the behavior
TotalValue	Double		The total cost/revenue value for the behavior row
UnitValue	Double		The unit cost/revenue value for the behavior row

Note: This behavior table is not identical to the behavior table that is used by a SAS Profitability Management model. Whereas the TRANASSIGN behavior table contains an AssignmentRule field, the SAS Profitability Management model behavior table does not contain such a field. Internally, SAS Profitability Management combines its behavior table with a rule association table to append an AssignmentRule field to produce the kind of behavior table that is used by the TRANASSIGN procedure.  

TS | TRANSCHEMA=transaction-schema-table

Specifies the data source for the transaction-schema table. The table must conform to the following schema:

Name	Data Type	Maximum Length	Description
Type	Char	64	The transaction table group to which a transaction table belongs; the value of this field is matched by the value of the TYPE parameter in a TRANSACTION statement
Column	Char	64	The name of the column
Output	Char	1	Is it part of the output (dimension or measure): 'Y' or 'N'

Note that the transaction schema table can contain multiple schemas. There is one schema per transaction table group. The Type field in the transaction schema table contains a string that identifies the transaction table group. The following is a sample transaction schema table:

Optional Arguments:

DIAG | DIAGNOSTIC=VERSION

Returns the version number of the TRANASSIGN procedure to the SAS log. If you specify DIAGNOSTIC=, then any other arguments of the TRANASSIGN statement are ignored.

Currently, VERSION is the only value supported for the DIAGNOSTIC= argument.

For example:

1    proc tranassign
2         diagnostic=version;
3    run;

NOTE: VERSION: 1.4.1
NOTE: PROCEDURE TRANASSIGN used (Total process time):
      real time           0.15 seconds
      cpu time            0.00 seconds

	

TRANSACTION Statement

The TRANSACTION statement specifies the input data source for a transaction table. It is also used to specify the transaction type, the period for the transaction table, and the data source for the output table. The statement specifies the action (overwrite or append) when there is a pre-existing output table with the same name. At least one TRANSACTION statement is required for a TRANASSIGN procedure.

Required Arguments:

DATA=transaction-table

Specifies the data source for the transaction table. Its schema must match the value of the TYPE parameter.

 

OUTPUT=output-table

Specifies the output table for the resulting transaction table. The schema of the output table is based on the transaction table schema that is specified in the TRANSCHEMA parameter in the PROC TRANASSIGN statement. All columns that are specified as output columns for the transaction table are in the output table. In addition, the following columns are part of the schema for the output table:

Name	Data Type	Maximum Length	Description
Behavior	Char	32	The name of the behavior that corresponds to the assignment
Quantity	Double		The driver quantity of the assignment
Value	Double		The cost/revenue that flows through the assignment

PERIOD='period-name'

Specifies the period for the transaction table. The period information determines the corresponding behavior rows for the transaction assignment.

 

TYPE='transaction-table-group'

Specifies the name of the transaction table group to which this transaction table belongs. The transaction table group determines the schema of the transaction table. Every transaction table in a group must share the same schema. The schema for a transaction table group is in the table specified by the TRANSCHEMA parameter in the PROC TRANASSIGN statement.

DIMENSION Statement

The DIMENSION statement defines the dimensional mappings in the transaction assignment. An assignment rule can use a condition that has an external dimension that does not live in the transaction table. This statement defines the data source for the external dimension table, and the key mapping between the transaction table and the dimension table. The DIMENSION statement is optional for a TRANASSIGN procedure.

Required Arguments:

NAME=dimension-name

Specifies the name of the dimension. The dimension name is referenced in an assignment rule's condition.

 

TABLE=dimension-table

Specifies the data source for the dimension table. Each dimension table must follow the following column ordering and length, although the name of each column is arbitrary:

Name	Data Type	Maximum Length	Description
Key	Char	32	The primary key for the dimension table
L1	Char	32	The level 1 member
L2	Char	32	The level 2 members
...
Ln	Char	32	The level n mm

Example

In the following example, the caller invokes the TRANASSIGN procedure to process two transaction tables and write the output to the same data source. The REGION dimension is used for dimension-member filtering:

libname model .;
libname modelOut .;

PROC TRANASSIGN 
        TranSchema=TranSchema 
        Behavior=Behavior
        AssignmentRule=Rule; 
        
Transaction Data=model.Atm04Q3 Period='2004_Q3' Type='General' Output=modelOut.ATMOut;
Transaction Data=model.Atm04Q4 Period='2004_Q4' Type='General' Output= modelOut.ATMOut;
Dimension Name='Region' Table=model.DimRegion;

RUN;

ChildOf Function

double IsChildOf(dim_member_column_in_transaction_table, parent_dim_member_full_name);

The ChildOf function returns 1 when the corresponding dimension member for the current transaction row is a descendant of the member parent_dim_member_full_name. Otherwise, it returns 0.

The first parameter specifies the column name of the dimension member in the transaction table. This parameter should not be in quotation marks.

The second parameter specifies the full name of a parent dimension member. The syntax of dim_member_full_name is similar to MDX (multidimensional expressions). Square brackets enclose the name of a single dimension member. The full name must start with the dimension name, and it must contain the name of each level for the parent dimension member. This parameter must be in quotation marks.

Examples for calling this function include:

• IsChildOf(Region, "[Region].[USA].[North Carolina]")
• IsChildOf(Product, "[Product].[Paper Goods].[Tissue Paper[429x340]]]")
• IsChildOf(Product, '[Product].[Beverages].[Fred''s choice]')

The examples demonstrate the following rules:

• Both single quotation marks and double quotation marks can enclose strings.
• "]" is used as an escape character for embedded closing square bracket.
• A single quotation mark is used as an escape character for an embedded single quotation mark.