At any stage of the mapping process, variables included in the mapping can be edited. Changes can be made to the following:

• Variable Inclusion
• Variable Order
• Variable Name
• Data Type
• Field Length
• Field Label
• Controlled Terminology

When study blinding is enabled, Mapper displays a blinded icon for blinded domains and fields in the Domain Editor window and in the Edit Variables window.

Edit Variables

1. In the Domain Editor, select the level where variables are edited.

2. Click the Variable drop-down and select Variables. A Variables box is added to the selected domain. 

   Note: This step may not be necessary, as variable editing is available from other transformations. However, this action provides a visual indicator that variables are being edited.

3. Press ENTER key or click the ellipses (...) next to the Variables field in the Domain Properties subpanel. The Edit Variables window opens, displaying a list of current variables and their settings.

Important: If a domain or field is blinded, Mapper displays a blinded icon in the Edit Variables window. The icon identifies variables sourced from blinded data. Output behavior depends on the privileges of the user executing the mapping and any blinding rules configured for the output domain.

Details of the variable editing options are described below. After making any changes, click Save to return to the Domain Editor.

Variables Status

At the bottom of the Edit Variables window, a summary of the variable status is displayed. The badges indicate the following:

• All Variables: The total number of variables at this layer, including any derived variables created at this layer.
• Selected: The number of variables included at this layer. Variables not selected at this layer are excluded from that layer.
• Derived: The number of derived variables created at this layer.

Standards: Field Names, Order and Labels, and Domain Class

If the name of the created domain matches one of the domains included in the selected standard or specification for the mapping (for example, AE, DM, LAB, etc.), Mapper recognizes variables that are part of that domain.

When a source table is added to a domain, labels are imported automatically. Labels from the selected standard or specification can be applied through variable editing.

The Class property of a mapped domain can also be edited to use fields from domains that are part of the standard, specification, or domain class (for example, Events, Findings, Findings About, Interventions). This feature enables auto-complete to suggest field names and labels, even when creating a custom domain, such as for an intermediate mapping.

Using Standards Order and Standards Labels

If the name of a field or variable is recognized (based on the name of the domain or  selected domain class), clicking Standards and then selecting Standards Order reorders the list according to the selected standard or specification.

Clicking Standards and then selecting Standards Labels updates the field labels for any matching fields.

By default, field names, data types, and field lengths are mapped based on metadata from the original data source.

Edit Controlled Terminology

Some fields require a data dictionary to store coded values. For these fields, use the CT field to define the data dictionary. When a CT dictionary is created, decoded values display in the preview.

Note: If a standard or specification was selected when creating the custom mapping, the options for any codelists in that standard or specification automatically appear.

To edit the controlled terminology for coded fields:

1. From the Edit Variables window, click the list icon in the CT field. The Controlled Terminology window opens.
   
2. In the Default Values section, specify what to do when the field contains a value for which no specific entry exists: 
   • Use Actual Value: Retains the original value for the field.

   • Use This Value: Select this option and enter a value in the box to define a default value for items that do not have coded or decoded values.

     Note: If null values already exist in the results, delete the row decoding the null value for the Use This Value option to decode null results correctly.

     
     

3. The Code column displays the values that currently exist in the records.

4. In the Decode column, click to edit the value stored for a given code. The value entered in the Decode field displays in the mapping output.

   Note: A drop-down is available for fields that have a codelist defined in the selected standard or specification.

5. To enter additional values to build a data dictionary:
   1. In the blank box in the Code column, enter the coded value expected in the records.
      If the field is blinded, the system displays the masked value automatically.
      
   2. In the blank box in the Decode column, enter the decoded value to display.
   3. Click the plus (+) icon to add the new value.
   4. Repeat as needed.
6. Click OK to save the decoded options.

Auto Decode for Controlled Terminology

For fields recognized as part of a standard or specification, the Auto Decode button automatically decodes existing data values defined in the selected codelist. This feature can set multiple values at once, rather than requiring manual selection.

Auto Decode requires the following:

• A standard or specification must be selected in the New/Edit Mapping window.
• The mapped domain must have a name that matches a domain in the selected standard or specification, or have the domain specified in the Class property.
• The variable name must match a variable in the standard or specification that has a defined codelist.

Auto Decode works in the following order:

1. If the raw value matches the CDISC Submission Value, it is entered as the decoded value.

2. If not, the raw value is compared to the NCI Term, CDISC Synonym, and Code, in that order.

3. If a match is found, the decoded value is automatically entered as the corresponding CDISC Submission Value.

4. If no match is found, the decoded value remains blank and must be entered manually.

Automatically decoded values appear in orange. A different value can still be selected from the drop-down if necessary.

The Auto Decode feature processes values present in the data at the time the Auto Decode button is selected. If new values for the variable are introduced in later data imports, the decode values must be updated by reselecting Auto Decode. Alternatively, when creating the domain mapping, all anticipated values can be entered manually before selecting Auto Decode if they do not yet appear in the data.

Manage the Variables List

Search for a Variable

To view details for a specific variable, enter the variable name in the Search field in the Edit Variables window. The list of variables automatically filters to match the entered term.

Include or Exclude a Variable

Editing the variable list allows management of which items are included in the mapping process. Fields can be excluded as needed.

When working with domain mappings, excluded fields can be hidden from the default view. To view hidden or deleted fields, select Show All.

To exclude a variable, clear the checkbox next to the variable number.

To view all available fields, including hidden or deleted ones, select the Show All checkbox at the top of the variable window.

A field can also be removed directly from the preview area. Hover over the column name and click the x icon.

After a variable is deleted or hidden, it remains in the variable list in the Domain Properties subpanel but is displayed in gray.

Important: Avoid removing a field used later as a key field in a Join or Union. Removing a field that is part of transformation logic causes that transformation to fail.

Cascade Changes

When a variable is included or excluded in a layer of a mapped domain, the change can affect subsequent transformations.

When excluding a variable from a transformation that is not the outermost layer, the Cascade button displays with a red line next to it. The default behavior prevents the removal from cascading to other existing transformations.

Selecting the Cascade checkbox removes the variable from all parent transformations. If multiple fields  are being removed, click the Cascade header to select all checkboxes for removed fields.

If the variable remains in subsequent parent transformations and the deletion is not cascaded, an error appears.

When a variable is added, such as including a previously excluded variable, creating a new derived variable, or importing new data that changes the mapped table, the Cascade checkbox displays a green line next to it. The variable becomes available to later transformations but is not automatically included in the display.

Select the Cascade checkbox to include the variable in downstream transformations.

Re-Order Variables

The column order number is displayed next to each variable selection checkbox.

To change the order of the variables, click the number and enter the new position manually.

Alternatively, click in the Column Order box and drag the field to the desired position.

If the domain name matches a domain in the selected standard or specification, click the Standards button at the top of the window and select Standards Order to re-order any fields recognized as part of the standard or specification so they appear in the correct order.

Edit Variable Names

The Edit Variables window contains two name columns:

• Name: Displays the current name of the variable or field.
• New Name: Allows editing of the field name.

Click the New Name field to edit the name manually. When changed, the text displays in dark blue.

Note: If the domain name matches one included in the selected standard or specification for the mapping (for example, AE, DM, LAB), the New Name field provides a drop-down list of possible field names that match the domain and standard or specification. As text is entered, the New Name field attempts to autocomplete based on the available standard or specification field names.

Variable names can also be edited from the Preview area. Double-click the field name in the header row to edit it.

Changing a variable name cascades the update through subsequent transformations when multiple layers of transformations exist. This allows a variable name to be changed later in the mapping process, even if the original value is nested under multiple transformation rules.

Note: Cascading renaming stops if a field has already been renamed within nested rules.
Example: If the SUBJECT field name in the original LB_CHEM panel is changed to SUBJID, but it has already been renamed in the Union results, the SUBJID name does not cascade through the Union.

Data Type and Length

The data type of a field can be changed as needed. Most data types can be cast to String. However, converting to Decimal or Integer from another data type may cause an error.

1. In the Data Type field, click the drop-down to select the data type from the list. If the data type is changed, it displays in orange text. When reviewing an existing mapping, the original data type displays with an asterisk (*).
2. In the Length field, click to edit the field length for variables with data types that allow modification (such as String or Decimal).

String to Date / Time Conversion

When converting between certain field types, errors or unexpected results may occur.

For example, converting a String field to a Date/Time field may add extra values or fail.
A string field that stores time in an hh:mm format and is converted to a Date/Time field has date information added to it. The default date added is 1900-01-01 (the SQL Server default).

If the original data in the field is recognized as Date/Time format compatible, it is automatically cast to Date/Time format.

If the field is in ISO format (date followed by the letter 'T' and then time), the system replaces the 'T' with a space.

If the data does not meet these criteria, an error occurs.

ISO8601 Dates

Date and time information, or partial date and time information, is often stored as text and must be mapped to the ISO8601 format. To convert partial or complete date or time fields to ISO8601, select ISO8601 as the data type for the field.

The ISO8601 data type can convert partial date information based on the following criteria:

• A time component in the format hh:mm:ss can be added to any of the formats below, as long as the date is complete.

• The date separator can be a dash or a slash (for example, 12/25/2017 or 12-25-2017).

• The date and time separator can be a space or a 'T' (for example, 12-25-2017 4:47PM or 12-25-2017T4:47PM).

• A partial date can include the year and month but omit the day, or include only the year. However, a date cannot include the day while omitting the month.

Edit Field Labels

A field label is a user-friendly description that helps identify the purpose of a variable. When a source table is added to a domain, labels are imported automatically. Labels can also be applied or modified through variable editing.

In the Label field, double-click to edit the label for the field. When changed, the text displays in dark blue.

If the domain is recognized, click the Standards Labels button to apply labels from the selected standard or specification.

Add Derived Variables

In many cases, a new variable may need to be created from existing data in the table.

Note: When derived variables use blinded source fields, downstream output behavior depends on the privileges of the user executing the mapping and any blinding rules configured for the output domain.

1. Click the ellipses (...) next to the Variables field in the Domain Properties subpanel. The Edit Variables window opens.
2. Click Add Derived Variable. A new variable is added to the bottom of the variable list.
   
3. Click the Click to Add hyperlink to edit the field. A list of standard Functions displays, along with a T-SQL box for entering SQL code manually.
   
4. Use a function (see the list of Built in Functions for details) or enter SQL code to define the new variable.
   
5. Click OK to save changes.

6. Double-click the New Name column to edit the field name.
   

   Note: Press the TAB key after editing to ensure the change is saved.

Derived variables can also be edited directly from the Variable Name list in the Domain Properties subpanel.

1. Scroll through the list to locate the derived variable and click the variable name.
2. A T-SQL Expression box opens for edits.
   
3. Click OK to save changes.

Built in Functions

The list of built-in derived variable functions is subject to change. Click a Function to see the details.

• AddDays

  This function allows adding a specified number of days to an existing date when an offset or range is needed.

  For example, if a drug is administered on a specific date and a follow-up visit is scheduled within 10 days, this function can create a field to calculate that 10-day comparison date. To subtract days from a date, enter a negative value in the Days field.

  1. In the Date field, click the drop-down to select the field containing the date to offset.
  2. In the Days field, enter the number of days to add.

• AddTime

  A common task during mapping is concatenating Date and Time fields to create a DateTime field. The AddTime function supports this type of derived variable.

  1. Click the AddTime function.
  2. In the Date field, select the field that contains the date (for example, AESTDAT).
  3. In the Time field, select the field that contains the time (for example, AESTTIM).

• Characters After

  This function extracts the characters that appear after a specified search expression in a string.

  1. In the Input String field, click the drop-down and select the field that contains the value to extract.
  2. In the Search Expression field, enter the value or click the drop-down to select the field that contains the expression that marks the start of extraction. 

  Example:

  INPUT STRING	SEARCH EXPRESSION	RESULTS
  123456-ABC	-	ABC

• Characters Before

  This function extracts the characters that appear before a specified search expression in a string.

  1. In the Input String field, click the drop-down and select the field that contains the value to extract.

  2. In the Search Expression field, enter the value or click the drop-down to select the field that contains the expression that marks the start of extraction.

  Example:

  INPUT STRING	SEARCH EXPRESSION	RESULT
  123456-ABC	-	123456

• Date from Parts

  This function creates a date field from Year, Month, and Day components. Select the fields that contain the Year, Month, and Day values.

  1. In the Year field, click the drop-down and select the field that contains the value for the year.

  2. In the Month field, click the drop-down and select the field that contains the value for the month.

  3. In the Day field, click the drop-down and select the field that contains the value for the day.

  Note: If a value is missing in any of the selected fields, the resulting value is NULL.

  Example:

  YEAR	MONTH	DAY	RESULT
  2020	03	15	2020-03-15T00:00:00
  2020	03		NULL

• Date-ISO from Parts

  This function creates an ISO8601 value from Year, Month, and Day components. Select the fields that contain the Year, Month, and Day values.

  1. In the Year field, click the drop-down and select the field that contains the value for the year.

  2. In the Month field, click the drop-down and select the field that contains the value for the month.

  3. In the Day field, click the drop-down and select the field that contains the value for the day.

  Note: This function supports creating partial dates.

  Example:

  YEAR	MONTH	DAY	RESULT
  2020	03	15	2020-03-15
  2020	03		2020-03

• Left

  This function selects the 'leftmost' characters from a field’s data.

  1. In the Variable field, click the drop-down and select the field that contains the text string to strip.
  2. In the Length field, enter the number of characters from the left to strip from the original text string.

• Lower Case

  This function converts the values from a specified variable to lowercase.

  In the Variable field, click the drop-down and select the field that contains the text string to convert to lowercase.

• Max of 2 or 3

  This function compares the values from two or three fields and returns the highest values.

  1. In the First Value field, click the drop-down and select the field that contains the value to compare.
  2. In the Second Value field, click the drop-down and select the field that contains the value to compare.

  Important: If the data types of the selected fields are not the same, the result may produce an error or unintended results. Use caution when selecting fields.

  Example:

  FIRST VALUE	SECOND VALUE	RESULT
  ABC	EFG	EFG
  12	23	3
  1/1/2020	2/1/2020	2/1/2020

   

• Min of 2 or 3

  This function compares the values from two or three fields and returns the lowest value.

  1. In the First Value field, click the drop-down and select the field that contains the value to compare.
  2. In the Second Value field, click the drop-down and select the field that contains the value to compare.

  Important: If the data types of the selected fields are not the same, the result may produce an error or unintended results. Use caution when selecting fields.

  Example:

  FIRST VALUE	SECOND VALUE	RESULT
  ABC	EFG	ABC
  12	23	12
  1/1/2020	2/1/2020	1/1/2020

• Replace

  This function replaces specified values in a variable’s data with new values automatically.

  1. In the Variable field, click the drop-down and select the field that contains the value to be replaced.

  2. In the Pattern field, enter the pattern to be replaced.

  3. In the Replacement field, enter the value that replaces the specified pattern.

• Replace Any

  This function replaces specified characters in a variable’s data with other characters. It is often used to replace separators with spaces.

  1. In the Variable field, click the drop-down and select the field that contains the value to be modified.

  2. In the Characters field, enter the characters to be replaced. These must be enclosed in single quotes.

  3. In the Replacement field, enter the characters that replace the specified characters. This entry must also be enclosed in single quotes.

• Substring

  This function strips a section of text from a character-type field. This function supports the creation of derived variables.

  1. In the Text field, click the drop-down and select the field that contains the text string to strip.
  2. In the Start field, enter the position number of the first character to keep from the string.
  3. In the Length field, enter the number of characters to strip starting from the specified position.

• SubtractDates

  This function creates a field that represents the duration of time between two existing dates.

  1. In the FirstDate field, click the drop-down and select the field that contains the first date.
  2. In the SecondDate field, click the drop-down and select the field that contains the second date.

• Trim

  This function removes leading and trailing spaces from a text expression.

  1. In the Text field, click the drop-down and select the field that contains the text string to trim.

• Upper Case

  This function converts the values from a specified variable to uppercase.

  1. In the Variable field, click the drop-down and select the field that contains the text string to convert to uppercase.
      

Inline Derived Variables

To streamline the process of working with derived variables, variables can be preselected for use with built-in functions or custom T-SQL code.

1. In the Edit Variables window, click a variable to preselect it. To select multiple variables, press Ctrl while clicking.
2. Hover over one of the selected variables and click the f(x) link in the Name/Expression column. A new derived variable is created in front of the variable where f(x) was selected. The Name/Expression column displays SQL: followed by the selected variable names.
3. Click the link in the Name/Expression field. The T-SQL box opens with the names of the selected variables automatically inserted.
4. Edit the T-SQL code as needed, or remove the variable names, select the Functions tab, and choose one of the built-in functions. The parameters for the function are populated with the selected variables.
5. Complete the creation of the derived variable and click OK to save.

Manual Derived Variable Examples

Concatenate Fields

A common type of derived variable is a concatenated field that combines values from two existing fields.

To combine text from two fields, enter the field names with the '+' symbol in the T-SQL Expression window.
For example:
Project + '-' + Subject
creates a concatenated result that uses a hyphen as a separator.

The following example shows a concatenation of the Project and Subject fields from the original table.

When concatenating fields, always use the current field names. If field names have been changed, the concatenated field must be created from the updated names by adding a new level, or by using the original field names if they exist at the same level.

Nested Case for Coded Terms

Although coded values can be created using the CT column, a CASE statement can also be used in a derived variable. This is useful when results must map from another field under certain conditions.

For example, in the Adverse Events table, the AEREL (causality) field records whether a reported AE is related to the study medication. The main options, such as 'Related' or 'Not Related,' can be mapped directly. However, if the entry for this field is 'Other,' details may need to be mapped from the AEOUT (Outcome of Adverse Event) field.

1. Click the ellipsis (...) next to the Variables field in the Domain Properties subpanel. The Edit Variables window opens.
2. Click Add Derived Variable. A new variable is added to the bottom of the variable list.
   
3. Click the Click to Add hyperlink to edit the field. The T-SQL opens for manual entry.
4. Enter the SQL code to create a new variable with a nested CASE statement. For example:
   Case AEREL
        when 'Possibly Related' then
             Case AEOUT
                  when 'Recovered/Resolved' then 'Resolved'
                  else 'Not Recovered'
             End
   End
5. Click OK to save changes.

The Preview area now displays the mapping results.

Tip: Whitespace can be added to improve the readability of T-SQL code. Use keyboard shortcuts such as Alt + 009 to insert a tab.