The SAS® METALIB Procedure, available in SAS 9.3, 9.2 and 9.1, is used to keep logical table metadata in sync with the physical tables that the metadata describes. It can also be used to report on any differences or discrepancies between the physical table and logical metadata descriptions. This post shows how ODS OUTPUT can be used in combination with PROC METALIB to capture its report output as SAS tables for further analysis and processing.
PROC METALIB can be used to do both the initial metadata registration of new tables and update those registrations if and when the physical table structures change. Sometimes people might change physical table structures (adding, updating and deleting tables or columns) without updating the corresponding metadata. This can have an impact on things that depend on that metadata such as SAS Data Integration Studio jobs. As an administrator it can be useful to get advanced warning of when those changes (or discrepancies) occur and so the METALIB procedure can also be used in report mode where it doesn’t actually make any changes to metadata, it just tells you what changes it would make if you let it.
In the past I have used the ability of PROC METALIB to generate a report of discrepancies, together with the capabilities of ODS OUTPUT in capturing the ODS output from SAS procedures as tables, to obtain PROC METALIB metadata discrepancy information in a more readily usable form for further processing. Once in SAS tables that information can be further analysed and appropriate action taken when necessary, such as sending out emails.
In case you ever need to do something similar here’s some sample code that demonstrates the use of PROC METALIB and ODS OUTPUT to capture the report of logical/physical metadata differences as SAS datasets.
ods trace on; ods output factoid1=work.ml_sum /* Summary information. */ updtab=work.ml_up /* List of tables that need to be updated in metadata. */ addtab=work.ml_add /* List of tables that need to be added to metadata. */ deltab=work.ml_del /* List of tables that might need to be deleted from metadata. */ ; proc metalib; omr (library="My Library Name"); noexec; report; update_rule (delete); ** BE VERY CAREFUL WHEN USING DELETE WITHOUT NOEXEC; run; ods output close; ods trace off;
The initial ODS OUTPUT statement is used to direct the ODS objects that PROC METALIB generates into appropriate SAS tables (which will be created as necessary). How do we know the names of those ODS objects in the first place? That’s where the ODS TRACE statements come in. They are used to get SAS to log the names of the ODS objects being generated by PROC METALIB. The trace statements are only used during development of the code and can be removed/commented when it is working well. I have left them in to show how those ODS object names were derived. Here is an example log fragment you might see from running the code above.
23 proc metalib; 24 omr (library="My Library Name"); 25 noexec; 26 report; 27 update_rule (delete); ** BE VERY CAREFUL WHEN USING DELETE WITHOUT NOEXEC; 28 run; NOTE: A total of 5 tables were analyzed for library "My Library Name". NOTE: NOEXEC statement in effect. No Metadata changes applied. NOTE: Metadata for 1 tables would have been updated. NOTE: Metadata for 1 tables would have been deleted. NOTE: Metadata for 3 tables would have been added. NOTE: Metadata for 0 tables matched the data sources. NOTE: 0 other tables were not processed due to error or UPDATE_RULE. Output Added: ------------- Name: Factoid1 Label: Metadata Summary Statistics Template: base.metalib.sumfact Path: Metalib.Factoid1 ------------- Output Added: ------------- Name: updtab Template: base.metalib.reptab Path: Metalib.updtab ------------- Output Added: ------------- Name: deltab Template: base.metalib.reptab Path: Metalib.deltab ------------- Output Added: ------------- Name: addtab Template: base.metalib.reptab Path: Metalib.addtab ------------- NOTE: The data set WORK.ML_DEL has 1 observations and 3 variables. NOTE: The data set WORK.ML_ADD has 3 observations and 3 variables. NOTE: The data set WORK.ML_UP has 4 observations and 8 variables. NOTE: The data set WORK.ML_SUM has 6 observations and 3 variables. NOTE: PROCEDURE METALIB used (Total process time): real time 0.11 seconds cpu time 0.08 seconds
Please take notice of the warning about using update_rule (delete). When used with noexec it just tells you which tables in metadata might be deleted because there are no corresponding physical tables to match them. If you take out the noexec then those metadata table registrations (if any) would get deleted and this can have a very significant impact on potentially many other metatadata objects that depend on them (like SAS DI jobs!). Sometimes those physical tables might not appear to exist because they might only be present during end of the month processing, or the user running the code does not have file system or database permissions to see them. Hence update_rule (delete) should be used with great care (a good reason it’s not the default behaviour).
If you like to find out more about PROC METALIB the SAS 9.3 Language Interfaces to Metadata book is a great place to start.