Examples: Using Metadata Language Elements

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 ODS 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 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>
   <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 file 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\';

ods html file="tables.html" path=reports;
proc print data=mybase.final;
run;

ods html close;

Here is the HTML report:

[untitled graphic]

Top of Page