Updating Your Table Metadata to Match Data in Your Physical Tables :: SAS(R) 9.3 Intelligence Platform: Data Administration Guide, Second Edition

Adding and Updating Table Metadata

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="lib1" repname="Foundation" );
run;

Without the REPORT statement, the options are written to the SAS log. The output resembles the following example:.

105  proc metalib;
106    omr  (library="lib1" repname="Foundation" );
107  run;

NOTE: A total of 3 tables were analyzed for library "lib1".
NOTE: Metadata for 2 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           0.20 seconds
      cpu time            0.06 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.

Example: 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="lib1" repname="Foundation" );
  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="lib1" repname="Foundation" );
  update_rule (delete noadd noupdate);
  report;
run;

The resulting SAS output resembles the following example:

Sample Summary Report

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.

Example: Specifying Tables

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

proc metalib;
  omr (library="lib1" repname="Foundation");
  select(lima);
  report;
run;

Here is the resulting ODS output:

The resulting SAS output resembles the following example:

PROC METALIB output showing that table LIMA is added

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

proc metalib;
  omr (library="lib1" repname="Foundation");
  exclude(licj libp);
  noexec;
  report;
run;