BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Haikuo
Onyx | Level 15

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:

metalibrary.PNG


  

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

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

works for our instance:

namelibrefenginepathmdschemanameschema
MDMUP07MDMUP07ORACLEMDMUP07MDM
RevLib3REVLIB3BASE//sasuser/MichaelC/MCLib/RevLib3
StarLMKSTARLMKBASE//sasuser/MichaelC/MCLib/StarLMK
CubelibCUBELIBBASE//sasuser/Biju/Cubelib
ChnGrwCHNGRWBASE//sasuser/MichaelC/MCLib/ChnGrw
RRRLibRRRLIBBASE//sasuser/MichaelC/MCLib/RRRLib
TMDMTMDMORACLETMDMMDM
RevLib2REVLIB2BASE//sasuser/MichaelC/MCLib/RevLib2
SASApp1 - SASDATASASDATABASEData
RCLIBRCLIBBASE//sasuser/Rachel
CardAsoCARDASOBASE//sasuser/MichaelC/MCLib/CardAso
RevLibREVLIBBASE//sasuser/MichaelC/MCLib/RevLib
CCLIBCCLIBBASE//sasuser/MichaelC/MCLib/CCLib
VCCLibVCCLIBBASE//sasuser/MichaelC/MCLib/VCCLib
CORP_RPTCORP_RPTSQLSVRCORP_RPTDBO

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;

View solution in original post

7 REPLIES 7
TomKari
Onyx | Level 15

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
Onyx | Level 15

Thanks, Tom! That is the spirit I like.

Haikuo

Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

is it as simple as

proc sql;

select * from sashelp.vslib;

quit;

Haikuo
Onyx | Level 15

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

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
Lapis Lazuli | Level 10

works for our instance:

namelibrefenginepathmdschemanameschema
MDMUP07MDMUP07ORACLEMDMUP07MDM
RevLib3REVLIB3BASE//sasuser/MichaelC/MCLib/RevLib3
StarLMKSTARLMKBASE//sasuser/MichaelC/MCLib/StarLMK
CubelibCUBELIBBASE//sasuser/Biju/Cubelib
ChnGrwCHNGRWBASE//sasuser/MichaelC/MCLib/ChnGrw
RRRLibRRRLIBBASE//sasuser/MichaelC/MCLib/RRRLib
TMDMTMDMORACLETMDMMDM
RevLib2REVLIB2BASE//sasuser/MichaelC/MCLib/RevLib2
SASApp1 - SASDATASASDATABASEData
RCLIBRCLIBBASE//sasuser/Rachel
CardAsoCARDASOBASE//sasuser/MichaelC/MCLib/CardAso
RevLibREVLIBBASE//sasuser/MichaelC/MCLib/RevLib
CCLIBCCLIBBASE//sasuser/MichaelC/MCLib/CCLib
VCCLibVCCLIBBASE//sasuser/MichaelC/MCLib/VCCLib
CORP_RPTCORP_RPTSQLSVRCORP_RPTDBO

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1454 views
  • 0 likes
  • 3 in conversation