Managing Table Metadata

Updating Your Table Metadata to Match Data in Your Physical Tables

By default, PROC METALIB creates table definitions for any tables in the library that do not have table definitions and updates any table definition that does not reflect the current structure of the table that it represents. It does not, however, delete table metadata.

Use REPORT when you want an output listing that summarizes metadata changes, either before changes are made (by using NOEXEC) or to see afterward what changes were actually made. SAS output is the default.

Example: Default PROC METALIB Behavior

The following example uses the default PROC METALIB behavior. Summary notes are written to the SAS log regardless of whether you request a report. Unlike the example shown in Assessing Potential Changes in Advance, the summary does not mention any deleted tables.

proc metalib;
omr  (library="v9SASlib" repname="Meta Proc repos" );
run;

Here is the resulting SAS log.

85   proc metalib;
86   omr (library="v9SASlib" repname="Meta Proc repos" );
87   run;

NOTE: A total of 1 tables were analyzed for library "v9SASlib".
NOTE: Metadata for 0 tables was updated.
NOTE: Metadata for 1 tables was added.
NOTE: Metadata for 0 tables matched the data sources.
NOTE: 0 other tables were not processed due to error or UPDATE_RULE.
NOTE: PROCEDURE METALIB used (Total process time):
      real time           19.06 seconds
      cpu time            5.39 seconds

Changing the Update Rule

By using the optional UPDATE_RULE statement, you can change the default behavior of PROC METALIB. The principal rules that you can specify are shown as follows:

NOADD	specifies not to add table metadata to the metadata repository for physical tables that have no metadata.
NOUPDATE	specifies not to update existing table metadata to resolve discrepancies with the corresponding physical tables.
DELETE	specifies to delete table metadata if a corresponding physical table is not found in the specified library.

Examples: Adding, Updating, and Deleting Metadata

The following example shows how to use PROC METALIB to add metadata for new tables, update table definitions where necessary, and also delete table definitions that are no longer valid. (You can also perform these functions using SAS Data Integration Studio.)

proc metalib;
omr (library="sas91 lib2" repname="Meta Proc repos" );
update_rule=(delete);
report;
run;

The following example shows how to use UPDATE_RULE with DELETE, NOADD, and NO UPDATE to delete table definitions that are no longer valid, as well as suppress the default add and update actions:

proc metalib;
omr  (library="sas91 lib2" repname="Meta Proc repos" );
update_rule (delete noadd noupdate);
report;
run;

The resulting SAS output resembles the following sample:

                                The METALIB Procedure
  
                           Summary Report for Library sas91 lib2
                                Repository Meta Proc repos
                                         17MAR2005

                                 Metadata Summary Statistics

                             Total tables analyzed           2
                             Tables Updated                  0
                             Tables Added                    0
                             Tables matching data source     0
                             Tables not found                0

Specifying Which Tables Are Affected

You can use the optional SELECT or EXCLUDE statements to perform an operation against a subset of the tables in a library. SELECT and EXCLUDE are mutually exclusive, so you should use only one or the other.

When you set the SELECT statement, you can choose the tables for processing:

For tables, specify their SAS name. If no table definition is found in metadata, it is created in the repository that contains the library object. If a matching table definition is found in metadata, it is compared to the physical table. If differences are found, the table definition is updated in metadata.
For tables already registered in metadata, specify either the unique metadata identifier or the value in the SASTableName attribute. If you specify the metadata identifier, only the specified table definition is updated, not the first table definition in the association list.

You can use EXCLUDE to specify a single table or a list of tables to exclude from processing.

Examples: Specifying Tables

The following example shows how to use SELECT to process only a subset of tables:

ods html "myfile";
proc metalib;
omr (library="SAS91 lib2" repname="Meta Proc repos" );
select(spec_char_col ukeys ndx_multicol);
report;
run;

Here is the resulting ODS output:

[PROC METALIB output showing that table NDX_MULTICOL is updated and SPEC_CHAR_COL is added]

The following example shows how to use EXCLUDE to exclude a specific subset of tables:

proc metalib;
omr (library="Geography Lib" repname="Foundation");
exclude(country postal mystate2);
noexec;
report;
run;

Top of Page