13197

Retrieving tables located on metadata server with SAS

Question:

I want to list all tables on metadata server. I've tried with the following data step but it only pics one table per library and I can't figure out why.

This is the code I've been using:

options metaserver="xxxx" metaport=8561 metauser="xxxx" metapass="xxxx" METAPROTOCOL=BRIDGE metarepository="Foundation"; data meta_libraries; length uri serveruri conn_uri domainuri libname ServerContext AuthDomain path_schema usingpkguri type tableuri coluri $256 id $17 desc $200 libref engine $8 isDBMS $1 table colname coltype collen $32; keep libname desc libref engine ServerContext path_schema AuthDomain table colname coltype collen IsPreassigned IsDBMSLibname id; nobj=.; n=1; uri=''; serveruri=''; conn_uri=''; domainuri=''; /***Determine how many libraries there are***/ nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri); /***Retrieve the attributes for all libraries, if there are any***/ if n>0 then do n=1 to nobj; libname=''; ServerContext=''; AuthDomain=''; desc=''; libref=''; engine=''; isDBMS=''; IsPreassigned=''; IsDBMSLibname=''; path_schema=''; usingpkguri=''; type=''; id=''; nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri); rc= metadata_getattr(uri, "Name", libname); rc= metadata_getattr(uri, "Desc", desc); rc= metadata_getattr(uri, "Libref", libref); rc= metadata_getattr(uri, "Engine", engine); /*rc= metadata_getattr(uri, "IsDBMSLibname", isDBMS);*/ rc= metadata_getattr(uri, "IsDBMSLibname", IsDBMSLibname); rc= metadata_getattr(uri, "IsPreassigned", IsPreassigned); rc= metadata_getattr(uri, "Id", Id); /*** Get associated ServerContext ***/ i=1; rc= metadata_getnasn(uri, "DeployedComponents", i, serveruri); if rc > 0 then rc2= metadata_getattr(serveruri, "Name", ServerContext); else ServerContext=''; /*** If the library is a DBMS library, get the Authentication Domain associated with the DBMS connection credentials ***/ if isDBMS="1" then do; i=1; rc= metadata_getnasn(uri, "LibraryConnection", i, conn_uri); if rc > 0 then do; rc2= metadata_getnasn(conn_uri, "Domain", i, domainuri); if rc2 > 0 then rc3= metadata_getattr(domainuri, "Name", AuthDomain); end; end; /*** Get the path/database schema for this library ***/ rc=metadata_getnasn(uri, "UsingPackages", 1, usingpkguri); if rc>0 then do; rc=metadata_resolve(usingpkguri,type,id); if type='Directory' then rc=metadata_getattr(usingpkguri, "DirectoryName", path_schema); else if type='DatabaseSchema' then rc=metadata_getattr(usingpkguri, "Name", path_schema); else path_schema="unknown"; end; /*** Get the tables associated with this library ***/ /*** If DBMS, tables are associated with DatabaseSchema ***/ if type='DatabaseSchema' then do; t=1; ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri); if ntab>0 then do t=1 to ntab; tableuri=''; table=''; ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri); tabrc= metadata_getattr(tableuri, "Name", table); output; end; else do; put 'Library ' libname ' has no tables registered'; output; end; end; end; else if type='Directory' then do; t=1; ntab=metadata_getnasn(uri, "Tables", t, tableuri); if ntab>0 then do t=1 to ntab; tableuri=''; table=''; ntab=metadata_getnasn(uri, "Tables", t, tableuri); tabrc= metadata_getattr(tableuri, "Name", table); output; end; else put 'Library ' libname ' has no tables registered'; end; /***If there aren't any libraries, write a message to the log***/ else put 'There are no libraries defined in this metadata repository.'; run;

I've would appreciate if someone has any suggestions?

Answer1:

I would recommend you to use "proc metadata" instead of the data step functions.

This is a possible solution although it can be improved since it is bringing more information than it should.

options metaport = &metaport metaserver = "&metaserver" metarepository = "Foundation" metauser = &meta_user. metapass = "&meta_pass"; * use a temporary fileref to hold the request; filename request temp; * use a data step to create the XML request; data _null_; file request; put '<GetMetadataObjects>'; put ' <Reposid>$METAREPOSITORY</Reposid>'; put ' <Type>PhysicalTable</Type>'; put ' <Objects />'; put ' <NS>SAS</NS>'; * Flags: <!-- OMI_ALL(1) + OMI_GET_METADATA(256) + OMI_TEMPLATE (4)+ OMI_SUCCINT (2048) = 2309 -->; put ' <Flags>2309</Flags>'; put '<Options>'; put ' <Templates>'; put ' <PhysicalTable/>'; put ' <SASLibrary Engine="" Libref=""/>'; put ' </Templates>'; put ' </Options>'; put '</GetMetadataObjects>'; run; * use a temporary fileref to hold the response; filename response TEMP; *"%sysfunc(getoption(work))/response.xml"; proc metadata in=request out=response verbose; run; * release the temporary request; filename request; filename mapa temp; data _null_; file mapa; put '<?xml version="1.0" encoding="windows-1252"?>'; put '<!-- ############################################################ -->'; put '<!-- 2014-02-05T15:41:54 -->'; put '<!-- SAS XML Libname Engine Map -->'; put '<!-- Generated by XML Mapper, 903200.3.0.20120523190000_v930m2 -->'; put '<!-- ############################################################ -->'; put '<SXLEMAP name="AUTO_GEN" version="2.1">'; put ' <NAMESPACES count="0"/>'; put ' <!-- ############################################################ -->'; put ' <TABLE description="SASLibrary" name="SASLibrary">'; put ' <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary</TABLE-PATH>'; put ' <COLUMN name="LibId">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Id</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>17</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="Engine">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Engine</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>4</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="Libref">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Libref</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>8</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="TabID" retain="YES">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@Id</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>17</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="Name_Table" retain="YES">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@Name</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>32</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="SASTableName" retain="YES">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@SASTableName</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>32</LENGTH>'; put ' </COLUMN>'; put ' <COLUMN name="TableName" retain="YES">'; put ' <PATH syntax="XPath">/GetMetadataObjects/Objects/PhysicalTable/@TableName</PATH>'; put ' <TYPE>character</TYPE>'; put ' <DATATYPE>string</DATATYPE>'; put ' <LENGTH>32</LENGTH>'; put ' </COLUMN>'; put ' </TABLE>'; put '</SXLEMAP>'; run; libname fim xmlv2 xmlfileref=response xmlmap=MAPA access=READONLY; /* * Local Extraction */ DATA SASLibrary; SET fim.SASLibrary; run; * release the temporary file and libname; filename response clear; libname fim clear;

Answer2:

Your else if type='Directory' then do; line was outside of the necessary do; end; group.

Other than that, your code runs fine! See below.

data meta_libraries; length uri serveruri conn_uri domainuri libname ServerContext AuthDomain path_schema usingpkguri type tableuri coluri $256 id $17 desc $200 libref engine $8 isDBMS $1 table colname coltype collen $32; keep libname desc libref engine ServerContext path_schema AuthDomain table colname coltype collen IsPreassigned IsDBMSLibname id; nobj=.; n=1; uri=''; serveruri=''; conn_uri=''; domainuri=''; /***Determine how many libraries there are***/ nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri); /***Retrieve the attributes for all libraries, if there are any***/ if n>0 then do n=1 to nobj; libname=''; ServerContext=''; AuthDomain=''; desc=''; libref=''; engine=''; isDBMS=''; IsPreassigned=''; IsDBMSLibname=''; path_schema=''; usingpkguri=''; type=''; id=''; nobj=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",n,uri); rc= metadata_getattr(uri, "Name", libname); rc= metadata_getattr(uri, "Desc", desc); rc= metadata_getattr(uri, "Libref", libref); rc= metadata_getattr(uri, "Engine", engine); rc= metadata_getattr(uri, "IsDBMSLibname", isDBMS); rc= metadata_getattr(uri, "IsDBMSLibname", IsDBMSLibname); rc= metadata_getattr(uri, "IsPreassigned", IsPreassigned); rc= metadata_getattr(uri, "Id", Id); /*** Get associated ServerContext ***/ i=1; rc= metadata_getnasn(uri, "DeployedComponents", i, serveruri); if rc > 0 then rc2= metadata_getattr(serveruri, "Name", ServerContext); else ServerContext=''; /*** If the library is a DBMS library, get the Authentication Domain associated with the DBMS connection credentials ***/ if isDBMS="1" then do; i=1; rc= metadata_getnasn(uri, "LibraryConnection", i, conn_uri); if rc > 0 then do; rc2= metadata_getnasn(conn_uri, "Domain", i, domainuri); if rc2 > 0 then rc3= metadata_getattr(domainuri, "Name", AuthDomain); end; end; /*** Get the path/database schema for this library ***/ rc=metadata_getnasn(uri, "UsingPackages", 1, usingpkguri); if rc>0 then do; rc=metadata_resolve(usingpkguri,type,id); if type='Directory' then rc=metadata_getattr(usingpkguri, "DirectoryName", path_schema); else if type='DatabaseSchema' then rc=metadata_getattr(usingpkguri, "Name", path_schema); else path_schema="unknown"; end; /*** Get the tables associated with this library ***/ /*** If DBMS, tables are associated with DatabaseSchema ***/ if type='DatabaseSchema' then do; t=1; ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri); if ntab>0 then do t=1 to ntab; tableuri=''; table=''; ntab=metadata_getnasn(usingpkguri, "Tables", t, tableuri); tabrc= metadata_getattr(tableuri, "Name", table); output; end; else do; put 'Library ' libname ' has no tables registered'; output; end; end; else if type='Directory' then do; t=1; ntab=metadata_getnasn(uri, "Tables", t, tableuri); if ntab>0 then do t=1 to ntab; tableuri=''; table=''; ntab=metadata_getnasn(uri, "Tables", t, tableuri); tabrc= metadata_getattr(tableuri, "Name", table); output; end; else put 'Library ' libname ' has no tables registered'; end; end; /***If there aren't any libraries, write a message to the log***/ else put 'There are no libraries defined in this metadata repository.'; run; proc sort; by libref table;run;

Recommend

  • jsTree: loading the jquery.jstree.js file from an app dir behaves differently from loading it from a
  • Get Error: You must install at least one postgresql-client- package when deploy to heroku
  • Installation of Visual Studio 2010 Service Pack 1 Beta and Asp.net MVC 3
  • forcing usage of bitwise and instead of boolean and
  • Dotnet Restore Failed on Build Server
  • How to run Docker commands as non-root user in Docker in Docker?
  • how do i make diffrent registration form in drupal?
  • What kind of data model models function parameters and results?
  • Show recently visited html pages by any visitor
  • How to open Theme editor in android studio
  • Serializable transactions not protecting me from double inserts
  • How to distinguish field that requires null=True when blank=True is set in Django models?
  • What is the best data type to store boolean values in a database
  • Raphael.js function getBBox give back NAN/NAN/NAN in IE8
  • node.js POST request fails
  • XMLBeans Duplicate Class and Class Not Found Errors
  • How to rotate the dial in a gauge chart? Using python plotly
  • How to draw a line dynamically in android [duplicate]
  • New Firebase failed: First argument must be a valid firebase URL and the path can't contain “.”
  • php script is parsing content from RTE (tt_news) but internal links are not appearing as speaking ur
  • Suppressing passwd when calling sqlplus from shell script
  • Jquery popup on mouse over of calendar control
  • Authentication in Play! and RestEasy
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • how to upload multiple files in c# windows application
  • MS Access - How to change the linked table path by amend the table
  • JBoss External Properties Files in Classpath
  • Connect .sks to skscene.h
  • Should I or shouldn't I use the CachingConnectionFactory with hornetq 2.4.1
  • Abort upload large uploads after reading headers
  • Email verification using google app script and google forms
  • Why does access(2) check for real and not effective UID?
  • Checking free space on FTP server
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • How to recover from a Spring Social ExpiredAuthorizationException
  • script to move all files from one location to another location
  • ILMerge & Keep Assembly Name
  • Symfony2: How to get request parameter
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • How do you join a server to an Active Directory (domain)?