BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Admin, General

Update Table Metadata Using Base SAS Code

Submitted by on 2012-12-19 – 5:55 AM

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.

proc metalib

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.

The following two tabs change content below.
Spread the love

Tags: