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!
You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.
- SAS Administration: Process Data Faster in RDBMS by Buffering the Data in Memory
- 3 Killer Tips for Coding INFOMAPS for Your BI Dashboard
- What’s the difference between SAS Enterprise Guide and SAS DI Studio?
- Describe Your Table in SAS to Write the SQL Code
- SAS Enterprise Guide: Updating the Metadata with New/Modified Datasets
Latest posts by Steve Overton (see all)
- Need to Migrate Users, Groups, and Roles between SAS Environments? - November 5, 2013
- Big Data Loading with PostgreSQL available in SAS 9.4 - October 22, 2013
- Everything Still Starts and Ends with Data in SAS Visual Analytics - July 30, 2013
- Coding: Quick Tip for Exploiting and Identifying SAS Formats - July 15, 2013
- Describe Your Table in SAS to Write the SQL Code - May 22, 2013