Using PROC METADATA in SAS to query SAS Workspace Server Properties

workspace_server_reconnect_property.sas

/* configure connection to metadata server */
options metaserver="hostname" metaport=8561;

/* here we are defining out metadata query */
/* we are making a call to GetMetadataObjects 
   and requesting a response listing all SAS 
   Workspace Servers defined in metadata 
   along with the associated properties which
   contain the work reconnect in their 
   property name.                          */
filename req temp;
data _null_;
file req;
input @;
put _infile_;
cards;
<GetMetadataObjects>
    <Reposid>$METAREPOSITORY</Reposid>
	<Type>ServerComponent</Type>
	<Objects/>
	<NS>SAS</NS>
	<!-- OMI_TEMPLATE (4) + OMI_XMLSELECT (128) + OMI_GET_METADATA (256) -->
	<Flags>388</Flags>
	<Options>
        <XMLSELECT search="ServerComponent[@ClassIdentifier=''440196D4-90F0-11D0-9F41-00A024BB830C'']"/>
	    <Templates>
        <ServerComponent Name="">
            <Properties search="Property[@PropertyName ? ''reconnect'']">
			    <Property/>
            </Properties>
        </ServerComponent>
		<Property PropertyName="" DefaultValue=""/>
        </Templates>
    </Options>
</GetMetadataObjects>
;
run;

/* here we will create an SXLEMAP for the results of the query we will run against the metadata server */
filename map temp;
data _null_;
file map;
input @;
put _infile_;
cards;
<?xml version="1.0" encoding="windows-1252"?>
<SXLEMAP name="AUTO_GEN" version="2.1">
    <NAMESPACES count="0"/>
    <TABLE description="ServerComponent" name="ServerComponent">
        <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent</TABLE-PATH>
        <COLUMN class="ORDINAL" name="ServerComponent_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/GetMetadataObjects/Objects/ServerComponent</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="ServerComponent_Id">
            <PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/@Id</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>17</LENGTH>
        </COLUMN>
        <COLUMN name="ServerComponent_Name">
            <PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/@Name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>25</LENGTH>
        </COLUMN>
    </TABLE>
    <TABLE description="Property" name="Property">
        <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/Properties/Property</TABLE-PATH>
        <COLUMN class="ORDINAL" name="ServerComponent_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/GetMetadataObjects/Objects/ServerComponent</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN class="ORDINAL" name="Property_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/Properties/Property</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>
        <COLUMN name="Property_Id">
            <PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/Properties/Property/@Id</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>17</LENGTH>
        </COLUMN>
        <COLUMN name="Property_PropertyName">
            <PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/Properties/Property/@PropertyName</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>19</LENGTH>
        </COLUMN>
        <COLUMN name="Property_DefaultValue">
            <PATH syntax="XPath">/GetMetadataObjects/Objects/ServerComponent/Properties/Property/@DefaultValue</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>5</LENGTH>
        </COLUMN>
    </TABLE>
</SXLEMAP>
;
run;

/* here we submit the GetMetadataObjects 
   request and collect the response */
filename res temp;
proc metadata in=req out=res;
run;

/* here we combine the response from the 
   metadata server with the xmlmap we 
   defined earlier to create SAS readable 
   tables */
libname rslt xmlv2 xmlmap=map access=readonly;

/* here we print a report showing each 
   property and its value for each SAS 
   Workspace Server defined in metadata */
proc sql;

       select sc.ServerComponent_Id
            , sc.ServerComponent_Name
	        , p.Property_Id
	        , p.Property_PropertyName
	        , p.Property_DefaultValue
         from rslt.ServerComponent sc
    left join rslt.Property p
           on sc.ServerComponent_ORDINAL = p.ServerComponent_ORDINAL;

quit;