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 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:
For examples of other
types of information that you can obtain with PROC METADATA, see METADATA Procedure.