使用SAS检索位于元数据服务器上的表 [英] Retrieving tables located on metadata server with SAS

问题描述

我想列出元数据服务器上的所有表.我尝试了以下数据步骤，但每个库只能显示一张表，我不知道为什么.

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?

推荐答案

我建议您使用"proc元数据"代替数据步骤功能.

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;

这篇关于使用SAS检索位于元数据服务器上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！