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 secondsPlease 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.
I am curious about using these output files. However when I’ve tried this on my own metadata I find that only the summary is created the other output files are not created and I receive this warning message for deltab, updttab and addtab:
Output ‘deltab’ was not created. Make sure that the output object name, label, or path is spelled correctly. Also,
verify that the appropriate procedure options are used to produce the requested output object. For example, verify that
the NOPRINT option is not used.
Do you have any idea as to what I might be doin wrong?
here is my code:
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=”library” repname=”SASApp” );
update_rule=(delete);
Select (list of tables I want to process );
*noexec;
report;
run;
ods output close;
ods trace off;
Thank you,
Dennis
Hi Dennis,
Thanks for your question.
That’s right, if an expected ODS object is not created then you’ll get a warning message. Proc metalib with ods output will not generate an output object if there is nothing to report for that category. i.e. when there are no tables to add, then no addtab object will be created and when there are no tables to delete the deltab object wont be created.
If you are expecting that some ODS objects might not be generated (as you probably would) then you can suppress the warning using the NOWARN option like so:
ods output factoid1=work.ml_sum updtab(nowarn)=work.ml_up addtab(nowarn)=work.ml_add deltab(nowarn)=work.ml_del;
BTW I notice in the code you pasted that you had the repository name as “SASApp”. Is that your repository name or is it a typo? It looks like a SAS application server name to me, hence the question.
Cheers
Paul
… and I forgot to mention that the documentation for the ods output nowarn option is available at http://support.sas.com/documentation/cdl/en/odsug/65308/HTML/default/viewer.htm#p0oxrbinw6fjuwn1x23qam6dntyd.htm
It took me a little while to get to grips with the syntax for the nowarn option which is why I posted the code snippet above.
Paul,
This is a Wonderful Post and i have used this in my Code too. Thanks for taking time to Share this with us.
However i do notice something in both the results from my Code and your Example above .
In the Example above the Metadata for only 1 table would have been updated but then the NOTE in the bottom of the example shows this :
NOTE: The data set WORK.ML_UP has 4 observations and 8 variables.
I would have expected only ONE observation corresponding the table being updated in the dataset WORK.ML_UP.
Even when i use this method in my code i too get a different number ( ie more ) of Observations in the WORK.ML_UP dataset instead of the exact Number of tables whose metada has been updated. The number of observations i see in this dataset makes no sense to me BUT i see that except for the table names which have been updated the rest of observations have blank values in this dataset.So i guess i can delete these blank values to get the correct number of observations in this dataset.
Are u also observing the same ?. I guess this may be a BUG but something which can fixed by deleting the blank values to get the correct Number of observations corresponding to updated tables.
Thanks for your comment and question Prashant.
Regarding the additional table rows, whilst the number of tables that would be updated might only be 1, there may be multiple changes to metadata that are required to get that single table’s metadata back in sync with the physical table. Each of those table row in ML_UP represents a single change. The rows look to be mostly blank because of the non-repeating values for the left-most columns. In my case the right-most column (Change) specifies which metadata attribute is being changed. Compare the dataset contents with the output report that is also generated and that should make it clearer. Remember that ODS OUTPUT is used to capture what would normally be printed output as SAS tables.
Thanks again Paul. That explains why i see more than one record in the ML_UP table.
Hi there,
I used your code to check whether metadata is similar to the physical structure.
I now have warnings about primary & foreign keys that can’t be updated.
WARNING: Processing primary key table . Foreign key table was not selected, so no foreign key changes will be
applied for it.
I don’t even want him to do that, but how can I suppress these warnings? My job crashes because of this warning (using LSF as a schedular).
Hope you have a solution.
Thanx in advance!
Hi Dennis,
My guess is that perhaps you have 1 or more tables in that library that have constraints referring to tables that no longer exist in that library or refer to tables in another library. There is a SAS note that explains the latter scenario: SAS Problem Note 20776: “The METALIB procedure cannot register metadata for tables that contain foreign key integrity constraints that refer to tables in another library” http://support.sas.com/kb/20/776.html
Cheers
Paul