SAS administrator tip: How to identify and prevent duplicate librefs in SAS metadata

This post was kindly contributed by SAS Users - go there to comment and to read the full post.

If you operate in SAS^® Enterprise Business Intelligence environment, you must realize that many of your data and user access functionalities and scopes are defined in and handled by the SAS metadata. Therefore, SAS metadata health is a paramount for successful SAS operations in your organization.

As a SAS user, have you ever been annoyed by a pop-up window in SAS Enterprise Guide? It reads “Duplicate librefs detected. Multiple libraries were detected in metadata that use the same libref for server SASApp. This is not supported configuration and may cause problems. Please contact your SAS administrator.” Here’s how it looks:

Of course, the suggested solution to “contact your SAS administrator” is easy, but what if the SAS administrator is YOU?

Unfortunately, this warning message does not specify the duplicated librefs. How do you know which librefs are duplicates? Data libraries are listed alphabetically in the Data Library Manager plug-in of the SAS Management Console, but they are listed by their library names, not by librefs. Certainly, you can look up each library’s libref by viewing their Properties or View Libname, but this might be a daunting task if you have a large number of libraries.

But fear this not! Here is how you can quickly find those pesky duplicate librefs.

Identifying duplicate LIBREFs in SAS Metadata

Fortunately, there is an assortment of SAS DATA step functions for reading and writing metadata. These metadata functions enable SAS administrators to programmatically retrieve (or set) various attributes, associations and properties of the metadata objects.

SAS program to list all metadata libraries and identify duplicate librefs

The following little program will 1) create a SAS data table listing all the metadata libraries; 2) create another data table specifically listing only libraries with duplicate librefs.

/* Connect to the SAS metadata server */
options
   metaserver = 'your_metadata_server'
   metaport = 8561
   metarepository = Foundation
   metauser = 'sasadm@saspw'
   metapass = '{SAS002}encrypted_password'
   ;
 
/* Create metadata libraries listing/inventory */
data META_LIBS (drop=i rc ouri);
   length NAME $256 LIBREF $8 ouri $35;
   call missing(of _char_);
   do i=1 by 1 while(metadata_getnobj("omsobj:SASLibrary?@Id contains '.'", i, ouri) > 0);
      rc = metadata_getattr(ouri, 'Name', NAME);
      rc = metadata_getattr(ouri, 'Libref', LIBREF);
      LIBREF = upcase(LIBREF);
      output;
   end;
run;
 
/* Output metadata libraries with duplicate librefs */
proc sort data=META_LIBS nouniquekey out=DUP_LIBREFS;
   by LIBREF;
run;

Code highlights

Connection OPTIONS statement makes sure you connect to the metadata server of your interest.
CALL MISSING makes sure that all character variables are initialized to blanks – to eliminate NOTES in the SAS log that variables are
DO-loop iterates through all the libraries in the metadata. Note, that we specify only a start-value (i=1), increment value (by 1) and WHILE-condition of entering into the DO-loop (metadata_getnobj(“omsobj:SASLibrary?@Id contains ‘.'”, i, ouri) > 0).
Here we use METADATA_GETNOBJ function which produces the output URI (ouri) of the i-th object that matches the specified input URI (“omsobj:SASLibrary?@Id contains ‘.'”). Besides returning the output URI, the function’s return value represents the number of objects matching the specified input URI (that is why we have while-condition as “> 0“). When negative value -4 is returned, it means that all SAS library objects are exhausted and DO-loop stops its iterations.
METADATA_GETATTR function is used twice and it assigns values of ‘Name’ and ‘Libref’ attributes of the metadata library object identified by the ouri
Since librefs are case insensitive, in order to eliminate case sensitivity in the captured values of libref we simply convert all its characters to upper case using the UPCASE function.
Finally, we use PROC SORT with NOUNIQUEKEY option to extract all non-unique LIBREF values and output observations with found duplicates to the DUP_LIBREFS data table. (NOUNIQUEKEY checks for and eliminates observations from the output data set that have a unique sort key.)

Duplicate librefs output

Here is an example of the produced data table that lists library names and duplicated librefs:

In case of no duplicate librefs, you will get the following NOTE in the SAS log:

NOTE: The data set WORK.DUP_LIBREFS has 0 observations and 2 variables.

Remediation of the duplicate librefs

Now, when you know the culprit(s), you can easily modify your metadata library definition to change the duplicate librefs to unique names. That is how you fix already existing problem.

However, such a fix would not preclude you from falling into the same trap again. How do you know that your new libref is unique and not repeating some other libref? Here is how to assuredly prevent this from happening.

Prevention from assigning duplicate LIBREF

The above method allows you to catch those accidentally (or not) assigned duplicate librefs. In this section, we will show how to prevent creating that mess in the first place! Here is how we can check (validate) if a libref that we are about to assign to a new metadata library is unique and not being used in one of the libraries that have already been defined in the metadata.

Solution 1: Querying metadata libraries listing

One simple solution is to use the data table META_LIBS created in the previous section. In essence, this data table is a listing (inventory) of all the data libraries defined in the SAS metadata. You can visually browse it or you can query it. For example, the following code will validate your new libref for exclusivity:

/* Libref to check for uniqueness */
%let NEW_LIBREF=Abc;
 
/* Libref available or taken */
data _null_;
   set META_LIBS end=eof;
   if LIBREF=upcase("&NEW_LIBREF") then taken + 1;
   if eof and taken then put "***Libref &NEW_LIBREF already taken.";
   if eof and not taken then put "***Libref &NEW_LIBREF available.";
run;

It will produce either of the following two “verdicts” in the SAS log:

***Libref Abc available.
or
***Libref Abc already taken.

Solution 2: Using METADATA_RESOLVE function

Another, even simpler, single step solution uses the METADATA_RESOLVE function, which allows querying SAS metadata directly:

/* Libref to check for uniqueness */
%let NEW_LIBREF=Abc;
 
/* Check if requested Libref available or taken */
data _null_;
   length type id $20;
   call missing (of _character_);
   select(metadata_resolve("omsobj:SASLibrary?@Libref='&NEW_LIBREF'", type, id));
      when(0)   put "***Libref &NEW_LIBREF available.";
      when(-1)  put "***Unable to connect to the metadata server.";
      otherwise put "***Libref &NEW_LIBREF already taken.";
   end;
run;

METADATA_RESOLVE function resolves a URI specified in its first argument into an object’s type and id on the metadata server. In addition, and that what is important for our purpose, its return value represents the number of objects that match the specified URI. That is the return value of

0 means no matches were found and our &NEW_LIBREF libref is available for use in the new library
-1 means that function was unable to connect to the metadata server
1+ means that there is one (or more) libref &NEW_LIBREF already existing in the metadata and this libref is not available for the new library.

Questions? Thoughts? Comments?

Do you find this blog post useful? How do you prevent and handle duplicate librefs in the metadata? Do you have questions, concerns, suggestions, or comments? Please share with us below in the Comments section.

Additional Resources

SAS administrator tip: How to identify and prevent duplicate librefs in SAS metadata was published on SAS Users.