Tracking Changes in Source Datetime Values

Problem

You want to track changes to primary key values using begin and end datetime values.

Solution

You can create a job that uses a Key Effective Date transformation.
The sample job includes the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to create and populate a new job:
  1. Create an empty SAS Data Integration Studio job.
  2. In the Transformations tree, in the Data folder, drag the Key Effective Date transformation into the empty job on the Diagram tab.
  3. Select and drag the source table into the source table location in the Diagram tab. In this sample job, the source table contains customer information.
  4. Drag the cursor from the source table to the input port of the Key Effective Date transformation. This action connects the source to the transformation.
  5. Select and drag the target table into the target table location in the Diagram tab. The target contains the same columns as the source.
  6. Drag the cursor from an output port of the Key Effective Date transformation to the target table. This action connects the transformation to the target. The following example shows the sample process flow.
    Sample Key Effective Date Process Flow Diagram
    Sample Key Effective Date Process Flow Diagram

Identify Source Columns

Perform the following steps to identify the primary key and datetime columns in the transformation:
  1. Open the properties window of the Key Effective Date transformation, and then select the Change Tracking tab.
  2. Under Column Name, triple-click to open the pull-down list to select the source and target columns that contain the begin and end datetime values.
  3. Under Expression, enter the expression or value that is applied when begin and end datetime values are missing from a source row.
  4. Open the Key tab and click New. Under Column, select the name of the first column in the primary key of the source table. Similarly, select in order any other columns in the primary key. The following display depicts the completed Key tab for the sample job.
    Order of Primary Key Columns on the Key Tab
    Order of Primary Key Columns on the Key Tab
  5. Click OK to close the properties window.

Run the Job and View the Output

Perform the following steps to run the job and view the output:
  1. Right-click in the job and select Run. SAS Data Integration Studio generates code for the job and submits the code to the SAS Application Server for execution.
  2. If error messages are displayed on the Status tab, read and respond to the messages as needed.
  3. After the completion of the job, right-click the target and select Open. The following display shows the target table data for the sample job.
    Tracked Datetime Values in the Sample Target Table
    Tracked Datetime Values in the Sample Target Table
Copyright © SAS Institute Inc. All rights reserved.