How to retrieve library names from EBI platform

[Haikuo]

We manage an EBI platform including 6 app servers and supporting more than 100 SAS users. As of right now we have more than 300 libraries and counting. Some of them are built and managed directly by Admin team, the rest are built by users but managed by Admin team. The way we update those libraries is to run Proc Metalib in EG:

proc metalib;

      omr(library=”library names could be any string”);

      update_rule=(delete);

       report;

    run;

Here is screen shot of sample libraries:

Now my question: is there a way or place I can pull up all those library names (or repository id for that matter) programmatically? So that this part :library=”library names could be any string” can be Macro-variable-nized and Macro automation could be implemented. Right now we have to manually input those library names one for each Proc Metalib, and it is getting more and more difficult  to keep up. I am aware that there is a table named “wlibrary” located at metadata server, “C:\SAS\Config\Lev1\SASMeta\MetadataServer\MetadataRepositories\Foundation”, However, I know better not to mess around it (you need to stop metadata server or unlock it before you can access to those tables).

Any suggestions and comments would be highly appreciated!

Haikuo

[TomKari]

Wow! Interesting question.

When you find the answer, please post, as I'd love to see it.

Try shooting a reference to this off to the Metacoda gang...they know this stuff inside out.

I'll send it off to a friend who's really good with this stuff also.

Good luck!

  Tom

[Haikuo]

Thanks, Tom! That is the spirit I like.

Haikuo

[Haikuo]

Tom,

here is something back from SAS tech support, in case you are interested:

Hi Haikuo,

I have attached a program, get_all_LibrariesAndServerContext.sas, which queries the metadata server for all SASLibrary objects. It returns a SAS data set containing the Library Name and the Server Context(s) to which the library is associated, as well as other data.

You can use this data set as input to a macro to generate PROC METALIB code.

I hope this is helpful. If you have follow-up questions, or need clarification, please respond to this email.

Regards,

[DBailey]

is it as simple as

proc sql;

select * from sashelp.vslib;

quit;

[Haikuo]

Thanks, David. I sure wish it were that simple .

There are some issues remained running your code:

1. Only gets "Assigned" libraries.

2. Only gets Librefs(8 char), not library names per EBI system

3. Only gets those Librefs assigned to certain App servers.

If we have assigned all the libraries first (put aside performance overhead on the servers), then we would have already known the library names. 

Haikuo

[DBailey · Accepted solution]

works for our instance:

name	libref	engine	path	mdschemaname	schema
MDMUP07	MDMUP07	ORACLE		MDMUP07	MDM
RevLib3	REVLIB3	BASE	//sasuser/MichaelC/MCLib/RevLib3
StarLMK	STARLMK	BASE	//sasuser/MichaelC/MCLib/StarLMK
Cubelib	CUBELIB	BASE	//sasuser/Biju/Cubelib
ChnGrw	CHNGRW	BASE	//sasuser/MichaelC/MCLib/ChnGrw
RRRLib	RRRLIB	BASE	//sasuser/MichaelC/MCLib/RRRLib
TMDM	TMDM	ORACLE		TMDM	MDM
RevLib2	REVLIB2	BASE	//sasuser/MichaelC/MCLib/RevLib2
SASApp1 - SASDATA	SASDATA	BASE	Data
RCLIB	RCLIB	BASE	//sasuser/Rachel
CardAso	CARDASO	BASE	//sasuser/MichaelC/MCLib/CardAso
RevLib	REVLIB	BASE	//sasuser/MichaelC/MCLib/RevLib
CCLIB	CCLIB	BASE	//sasuser/MichaelC/MCLib/CCLib
VCCLib	VCCLIB	BASE	//sasuser/MichaelC/MCLib/VCCLib
CORP_RPT	CORP_RPT	SQLSVR		CORP_RPT	DBO

scraped from sas support:

options metaserver="metadataserver"

    metaport=8561

    metauser="admin user"

    metapass="admin pw"

    metarepository="Foundation";

data metadata_libraries;

    length

        liburi upasnuri $256

        name $128

        type id $17

        libref engine $8

        path mdschemaname schema $256;

    keep

        name

        libref

        engine

        path

        mdschemaname

        schema;

  call missing(liburi,upasnuri,name,engine,libref);

  nlibobj=1;

  librc=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",nlibobj,liburi);

  do while (librc>0);

     /* Get Library attributes */

         rc=metadata_getattr(liburi,'Name',name);

         rc=metadata_getattr(liburi,'Engine',engine);

         rc=metadata_getattr(liburi,'Libref',libref);

      /* The METADATA_GETNASN function specifies to get objects associated to the

library via the UsingPackages association. The n argument specifies to return the

first associated object for that association type. upasnuri is an output variable.

It will store the URI of the associated metadata object, if one is found.  */

        n=1;

        uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

        /* When a UsingPackages association is found, the METADATA_RESOLVE function

is called to resolve the URI to an object on the metadata server. The CALL MISSING

routine assigns missing values to output variables.  */

        if uprc > 0 then do;

           call missing(type,id,path,mdschemaname,schema);

           rc=metadata_resolve(upasnuri,type,id);

           /* If type='Directory', the METADATA_GETATTR function is used to get its

path and output the record */

           if type='Directory' then do;

              rc=metadata_getattr(upasnuri,'DirectoryName',path);

              output;

              end;

           /* If type='DatabaseSchema', the METADATA_GETATTR function is used to get

the name and schema, and output the record */

           else if type='DatabaseSchema' then do;

               rc=metadata_getattr(upasnuri,'Name',mdschemaname);

               rc=metadata_getattr(upasnuri,'SchemaName',schema);

              output;

              end;

        /* Check to see if there are any more Directory objects */

            n+1;

            uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

          end; /* if uprc > 0 */

     /* Look for another library */

     nlibobj+1;

     librc=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",nlibobj,liburi);

  end; /* do while (librc>0) */

run;

/* Print the metadata_libraries data set */

proc print data=metadata_libraries; run;

[Haikuo]

Nice. I am not very familiar with those metadata functions, but this looks overwhelmingly promising to me. Will report back and mark correct answer after I try it out.

Thanks again, David

Haikuo