Example: Creating a Report with the METADATA Procedure and the XML Engine

This example creates a report about all the tables in a user's library, including the tables' column names, librefs, and engines.
PROC METADATA requests the column names, and so on, from metadata, and outputs the values in an XML file. Then, the XML LIBNAME engine uses an XML map to read the XML file and create SAS data sets. When the information is in SAS data sets, an administrator can run SAS code like DATA steps and procedures. This example uses PROC PRINT to create an HTML report.
To be clear, the files that are used in this example are described in the following list. The XML files are temporary and exist during the session only. However, you can also create permanent files.
  • the user's library, which contains an unknown number of tables
  • an input XML file, which is created by a DATA step to query the metadata
  • an output XML file, which is created by PROC METADATA and contains information about the user's tables
  • an XML map, created by a DATA step
  • two SAS data sets, created by the XML LIBNAME engine and an XML map
  • a third SAS data set, created by a DATA step MERGE
  • an HTML report, created by ODS (Output Delivery System) statements
The METADATA procedure is documented in this book; see METADATA Procedure. The XML LIBNAME engine and XML maps are not documented in this book; see SAS XML LIBNAME Engine: User's Guide.
The example begins by connecting to the SAS Metadata Server, updating the metadata about the library, and creating the input XML file.
/* submit connection information to server */

options metaport=8561
        metaserver="a123.us.company.com"
        metauser="myuserid"
        metapass="mypasswd";

/* Run PROC METALIB to be sure the metadata is current.                  */
/* The library must be registered already in the SAS Metadata Server.    */
/* Use the library name that is defined in the metadata, not the libref. */

proc metalib;
  omr (library="mylib");
  report;
run;

/* Assign filerefs and libref. */
filename query temp;
filename rawdata temp; 
filename map temp; 
libname myxml xml xmlfileref=rawdata xmlmap=map;

/* Create temporary query file.                           */
/* 2309 flag plus template gets table name, column name,  */
/* engine, libref, and object IDs. The template specifies */
/* attributes of the nested objects.                      */

data _null_;
   file query;
   input;
   put _infile_;
   datalines;
<GetMetadataObjects>
   <Reposid>$METAREPOSITORY</Reposid>
   <Type>PhysicalTable</Type>
   <Objects/>
   <Ns>SAS</Ns>
   <!-- OMI_ALL (1) + OMI_TEMPLATE(4) + 
OMI_GET_METADATA(256) + OMI_SUCCINCT(2048) flags -->
   <Flags>2309</Flags>
   <Options>
    <Templates>
      <PhysicalTable/>
      <Column SASColumnName=""/>
      <SASLibrary Engine="" Libref=""/>
    </Templates>
   </Options>
</GetMetadataObjects>
;;
run;
proc metadata
     in=query
     out=rawdata;
run;
The next section of example code creates a temporary text file that contains the XML map. The map enables the XML LIBNAME engine to process the XML returned by the metadata query as two data sets, ColumnDetails and LibrefDetails.
In the ColumnDetails data set, the observation boundary (TABLE-PATH) is at Column. Putting the boundary at Column is necessary because the PhysicalTable elements have multiple Column elements. If you need to read multiple elements, you must set the observation boundary at that element, so the XML LIBNAME engine can create multiple observations for the element.
Because the observation boundary is set at Column, each observation stops at Column, and any elements that follow Column are not properly read. Therefore, another data set is required. The LibrefDetails data set contains the SASLibrary elements. Later in the code, the ColumnDetails and LibrefDetails data sets are merged into a final data set.
The XML map is created in the following code to illustrate the process. You can use a graphical interface, SAS XML Mapper, to generate an XML map. For more information, see SAS XML LIBNAME Engine: User's Guide.
data _null_;
   file map;
   input;
   put _infile_;
   datalines;

<?xml version="1.0" ?>
  <SXLEMAP version="1.2">

   <TABLE name="ColumnDetails"> 
   <TABLE-PATH syntax="xpath">
     /GetMetadataObjects/Objects/PhysicalTable/Columns/Column
   </TABLE-PATH>

   <COLUMN name="SASTableName" retain="yes">
     <PATH>
        /GetMetadataObjects/Objects/PhysicalTable/@SASTableName
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>14</LENGTH>
   </COLUMN>

   <COLUMN name="Columns">
     <PATH>
        /GetMetadataObjects/Objects/PhysicalTable/Columns/Column/@SASColumnName
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>12</LENGTH>
   </COLUMN>

   <COLUMN name="Column IDs">
     <PATH>
        /GetMetadataObjects/Objects/PhysicalTable/Columns/Column/@Id
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>17</LENGTH>
   </COLUMN>
   
   </TABLE>


   <TABLE name="LibrefDetails"> 
   <TABLE-PATH syntax="xpath">
     /GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary
   </TABLE-PATH>

   <COLUMN name="SASTableName">
     <PATH>
        /GetMetadataObjects/Objects/PhysicalTable/@SASTableName
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>14</LENGTH>
   </COLUMN>

   <COLUMN name="Libref">
     <PATH>
       
/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Libref
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>10</LENGTH>
   </COLUMN>

   <COLUMN name="Engine">
     <PATH>
       
/GetMetadataObjects/Objects/PhysicalTable/TablePackage/SASLibrary/@Engine
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>10</LENGTH>
   </COLUMN>

   </TABLE>
   
</SXLEMAP>
;

/* Optional: print XML mapped data sets before the merge. */

title 'Tables and their Columns';
proc print data=myxml.ColumnDetails;
run;

title 'Tables and their Librefs';
proc print data=myxml.LibrefDetails;
run;

/* Create data sets that contain the metadata. */

libname mybase base 'c:\myxml\data';

data mybase.ColumnDetails;
  set myxml.ColumnDetails;
run;

data mybase.LibrefDetails;
  set myxml.LibrefDetails;
run;

/* Sort by table name.  */

proc sort data=mybase.ColumnDetails out=mybase.ColumnDetails;
  by  SASTableName; 
run;

proc sort data=mybase.LibrefDetails out=mybase.LibrefDetails;
   by SASTableName; 
run;

/* Merge into one data set. */

data mybase.final;
   merge mybase.ColumnDetails mybase.LibrefDetails ;
   by SASTableName;
run;
After ColumnDetails and LibrefDetails are merged into the final data set, an ODS step creates the HTML report:
title 'Table Metadata';
filename reports 'c:\myxml\reports\';

proc print data=mybase.final;
run;
Here is a portion of the report:
Table Metadata
For examples of other types of information that you can obtain with PROC METADATA, see METADATA Procedure.