Update Table Metadata Using Base SAS Code
Ever had the need to update as table metadata by submitting SAS code? If so, you can use the SAS BI METALIB procedure to do so. This may be useful if you want to automate an ETL process, which loads tables and registers in metadata at the same time.
Update Existing Table in SAS BI Metadata
The following code connects to a SAS library already registered in metadata and updates the table definition. The update_rule option specifies that if the table does not physically exist anymore, delete it from metadata. Important note: you must have ReadMetadata and WriteMetadata permissions on the table object, which are assigned by your SAS administrator.
proc metalib; omr (library="[INSERT METADATA LIBRARY NAME HERE]" metarepository="Foundation"); select("[INSERT TABLE NAME HERE]"); update_rule=(delete); report; run;
Here’s an example where the user had created a duplicate table called ADVERTISER_2 in the Blog library. You can see the code used to remove the library from the metadata. The underlying data table has already been removed in this example.
Doing More with PROC METALIB
You can do other useful things with this procedure, such as update metadata for all physical tables in a library. To do this simply remove the select option. SAS Support has additional information and options you can use within PROC METALIB to do even more with table metadata.
The update_rule option has a few interesting options as well as the select option. The standard way to update table metadata is through SAS Management Console. This technique allows you to take a more programmatic approach if necessary.
Have fun making your code a little more automagic!
Learn all about SAS BI
You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.
Latest posts by Steve Overton (see all)
- BulkLoading with PostgreSQL available in SAS 9.4 - 2013-10-22
- Everything Still Starts and Ends with Data in SAS Visual Analytics - 2013-07-30
- Describe Your Table in SAS to Write the SQL Code - 2013-05-22
- Update Table Metadata Using Base SAS Code - 2012-12-19
- SAS Administration: Fetch Data Faster across ODBC - 2012-11-01
Tags: Data Management