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=&mlibname repname= &mrepname );
run;

Here is the resulting SAS log.

85   proc metalib;
86   omr (library=&mlibname repname= &mrepname );
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 PROC METALIB's default behavior. The principal rules that you can specify are shown below:

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 Data Integration Studio.)

proc metalib;
omr (library=&mlibname repname= &mrepname );
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=&mlibname repname= &mrepname );
  update_rule (delete noadd noupdate);
  report;
run;

The resulting SAS output would resemble 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                    2
                             Tables matching data source     0
                             Tables not found                0


 ------------------------------------------------------------------------------------
                                       Tables Added
 ------------------------------------------------------------------------------------


 Metadata Name                     Metadata ID          SAS Name

 COUNTRY                           A5HJ58JU.AX001LPV    COUNTRY
 POSTAL                            A5HJ58JU.AX001LPW    POSTAL

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 select tables or table definitions for processing:

For tables, specify their SAS name. If no table definition is found, it is created in the repository that contains the SASLibrary object. If a matching table definition is found, it is compared to the physical table. If differences are found, the table definition is updated.
For table definitions, 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=&mlibname repname= &mrepname );
select(spec_char_col ukeys ndx_multicol);
report;
run;

Here is the resulting ODS output.

[untitled graphic]

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

proc metalib;
omr (library=&mlibname repname= &mrepname);
exclude(country postal mystate2);
noexec;
report;
run;

Top of Page