Maintaining Column Metadata

Problem

You want to add or modify column metadata for registered tables, temporary work tables, and external files.

Solution

You can use the Columns tab to maintain the metadata for columns in a table or external file. You can perform the following tasks on the metadata:

Tasks

Add Metadata for a Column

Perform the following steps to add a new column to the metadata for the current table:
  1. Open the properties window for the table or external file, and click the Columns tab. The metadata for the current columns, if any, appears in an ordered list.
  2. To add metadata for a new column to the end of the current list of columns, click the New column icon in the toolbar at the top of the Columns tab. Alternatively, you can right-click in a blank area of the Columns tab and select New column from the pop-up menu.
    To insert metadata for a new column after the metadata for a current column, right-click the metadata for the current column, and then select New column from the pop-up menu.
    After you perform these actions, a row of default metadata that describes the new column displays. The name of the column, Untitledn, is selected and ready for editing. The other attributes of the column have the following default values:
    • Description: Blank
    • Type: Character
    • Length: 8
    • Informat: (None)
    • Format: (None)
    • Is Nullable: Yes
    • Summary Role: (None)
    • Sort Order: (None)
  3. Change the name of the column to give it a meaningful name.
  4. Change the values of other attributes for the column as desired. For more information, see Modify Metadata for a Column.
  5. Click OK to save the new column metadata.
Note: You can add columns only when the columns table in the Columns tab is sorted on the # column.

Modify Metadata for a Column

To modify the metadata for a column in the current table, open the properties window for the table or external file, and click the Columns tab. Select the attribute that you want to change, make the change, and then click OK. The following table explains how to change each type of attribute.
Column Metadata Modifications
Attribute
Description
Instructions
Name
The SASColumnName of the column. This matches the physical name.
Perform the following steps to enter a name:
  1. Double-click the current name to make it editable.
  2. Enter a new name of 32 characters or fewer.
  3. Press the ENTER key.
Description
This can be the label of the column, and shows up as the label in the generated code.
Perform the following steps to enter a description:
  1. Double-click in the Description field.
  2. Edit the description, using 200 characters or fewer.
  3. Press the ENTER key.
Type
The type can be either numeric or character.
Perform the following steps to enter the data type:
  1. Double-click the current value to display the drop-down list arrow.
  2. Click the arrow to make a list of valid choices appear.
  3. Select a value from the list.
Length
This is the length of the column.
Perform the following steps to enter the column length:
  1. Double-click the current length.
  2. Enter a new length. A numeric column can be from 3 to 8 bytes long (2 to 8 in the z/OS operating environment). A character column can be 32,767 characters long.
  3. Press the ENTER key.
Informat
This specifies a pattern or set of instructions that SAS uses to determine how data values in an input file should be interpreted.
Perform the following steps to enter an informat:
  1. Double-click the current value to display the drop-down list arrow.
  2. Click the arrow to make a list of valid choices appear and then select a value from the list, or type in a new value and press ENTER.
Format
This specifies a pattern or set of instructions that SAS uses to determine how to display information.
Perform the same steps as for informat.
Is Nullable
This is used to determine whether the integrity constraint IsNullable is set for a specific column. This determines whether a column might have a value of null.
Perform the same steps as for type.
Summary Role
This is used for information purposes only.
Perform the same steps as for type.
Sort Order
This is used for information purposes only.
Perform the same steps as for type.
You can also edit a value by tabbing to it and pressing the F2 key or any alphanumeric key. For information about the implications of modifying metadata for a column, see the note at the end of "Delete Metadata for a Column" in Perform Additional Operations on Column Metadata.

Add and Maintain Notes and Documents for a Column

The Columns tab enables you to attach text notes, and documents produced by word processors, to the metadata for a table column. Such a note or document usually contains information about the table column or the values that are stored in that column.
Note: If a column currently has notes or documents associated with it, you can see a notes icon to the left of the column name.
To add a note or document to a column, modify an existing note or document, or remove an existing note or document, you can use the Notes window. Perform the following steps to display this window:
  1. Right-click the column that you want to work with and click Properties in the pop-up menu. Then, click Notes to access the Notes tab for the selected column.
  2. Perform one or more of the following tasks in the Notes group box:
    • Enter the text in the Quick Note field. Quick notes are private to this column, while the other type of notes are shared notes.
    • Click New to create a new note. Enter a title in the Assigned field and the text of the note in the Note text field. Use the editing and formatting tools at the top of the window if you need them.
    • Click the name of an existing note in the Assigned field to review or update the content in the Note text field.
    • Click Delete to delete the note.
    • Click Attach to access the Select Additional Notes window and attach an additional note to the column.
  3. Perform one or more of the following steps in the Documents group box:
    • Click New to attach a new document to the note. Enter a title in the Name field. Then, enter a path to the document in the Path field.
    • Click the name of an existing document in the Name field to review or update the path in the Path field.
    • Click Delete to delete the document.
    • Click Attach to access the Select Additional Documents window and attach an additional document to the column.
  4. Click OK to save the contents of the note.

Perform Additional Operations on Column Metadata

The following table describes some additional operations you can perform on metadata in the Columns tab.
Additional Operations on Column Metadata
Task
Action
Delete Metadata for a Column
Perform the following steps to delete the metadata for a column in the current table:
  1. Select a column.
  2. Click Delete.
Note: When you modify or delete the metadata for a column in a table and that table is used in a SAS Data Integration Studio job, you might also have to make the same modifications to other tables in the job. For example, if you change the data type of a column and that table is used as a source in a job, then you need to change the data type of that column in the target table and in the temporary work tables in the transformations in that job.
Changes to column metadata in SAS Data Integration Studio do not appear in the physical table automatically. You must select the Replace in the Load Style field and the Entire table in the Replace field on the Load Technique tab of the Table Loader transformation that loads the current table.
Column level impact analysis can help you gather information about deleting metadata for a column. To perform impact analysis, right-click on a table and select Analyze. Note that you can also obtain information about reverse impact analysis on another tab in the same window.
Import Metadata for a Column
Perform the following steps to import column metadata that has been added to the metadata server that is specified in your current connection profile:
  1. Click Import columns to access the Import Columns window.
  2. Locate the table with columns that you want to import. Select one or more columns from the Available field in the Import Columns window.
  3. Select the right arrow to move the selected columns into the Selected field.
  4. Reorder the columns in the Selected Columns field by selecting columns and clicking the Moves selected items up or Moves selected items down arrows.
  5. Click OK to import the columns into the table.
Be aware of the following implications if you add or import metadata for a column:
  • You might need to propagate that column metadata through the job or jobs that include the current table.
  • Changes to column metadata in SAS Data Integration Studio do not appear in the physical table automatically. You must select the Replace in the Load Style field and the Entire table in the Replace field in the Load Technique tab of the Table Loader transformation that loads the current table.
Maintain Indexes
Indexes are registered automatically when using Register tables to register metadata about existing tables. In SAS 9.3, indexes are imported correctly when import/export is used. Update table metadata also updates indexes. See Maintaining Indexes.
Maintain Keys
Primary, foreign, and unique keys are registered automatically when using Register tables to register metadata about existing tables. In SAS 9.3, keys are imported correctly when import/export is used. Update table metadata also updates them, although currently it does not handle foreign key updates.
It is important when working with foreign keys to include ALL of the tables that are related in a single registration. Otherwise, foreign key relationships cannot be maintained. See Maintaining Keys.
Propagate Column Metadata from One Table to Other Tables in a Job
See Managing the Scope of Column Changes in Jobs.
Reorder Columns and Rows
You can rearrange the columns in a table (without sorting them) by dragging a column to a new location. You can reorder rows by (1) using the arrow buttons at the top of the window, or (2) dragging a column to a new location by dragging the column-number cell.
Restore the Order of Columns
Click the column number heading to restore all of the rows to their original order.
Save Reordered Columns
Some windows allow you to change the default order of columns. Then, you can save that new order in the metadata for the current table or file. If you can save reordered columns before you exit the current window, SAS Data Integration Studio displays a dialog box that asks if you want to save the new order.
Sort Columns
You can sort the columns in a table based on the value of any column attribute (such as Name or Description) in either ascending or descending order. For example, you can sort the columns in ascending order by name by clicking the Name heading. To sort the columns in descending order by name, you can click the same heading a second time.
View or update extended attributes for columns
From the Columns tab, select the desired column, and then click the Properties icon in the toolbar. In the properties window, click the Extended Attributes tab. Use this tab to view or update extended attributes.
Copyright © SAS Institute Inc. All rights reserved.