topic Re: Querying Metadata using Proc Metadata in SAS Programming

Querying Metadata using Proc Metadata

Scott_Mitchell — Mon, 06 May 2019 04:57:37 GMT

Hi Everyone.

I am attempting to query metadata using Proc Metadata and am having some problems. I got the base query from https://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/viewer.htm#a003177416.htm (which works perfectly and seems to make sense when I compare the map and the query to the Metadata Browser) and am attempting to modify it to suit my needs. Initially I am attempting to get the name of all jobs registered in Metadata, then identify those which are deployed and lastly which flows the jobs are part of.

When I execute the following query the resulting table contains no data. Can anyone help get me back on track please? I have read all the documentation and it doesn't seem to help. I have also read a number of whitepapers, however converting the logic they are using to fit my use case is problematic.

Any assistance is greatly appreciated.

Regards,

Scott

/* Assign filerefs and libref. */
filename query "&datapath.\results2.xml" lrecl=256;
filename rawdata "&datapath.\results3.xml" lrecl=256; 
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>Job</Type>
   <Objects/>
   <Ns>SAS</Ns>
   <Flags>2309</Flags>
   <Options>
    <Templates>
      <Job/>
        <Column Name=""/>
    </Templates>
   </Options>

</GetMetadataObjects>
;;
run;

proc metadata
     in=query
     out=rawdata;
run;


data _null_;
   file map;
   input;
   put _infile_;
   datalines;

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

   <TABLE name="JobDetails"> 
   <TABLE-PATH syntax="xpath">
     /GetMetadataObjects/Objects/Job/
   </TABLE-PATH>

   <COLUMN name="Name" retain="yes">
     <PATH>
     	/GetMetadataObjects/Objects/Job/
     </PATH>
     <TYPE>character</TYPE>
     <DATATYPE>STRING</DATATYPE>
     <LENGTH>60</LENGTH>
   </COLUMN>
   </TABLE>
   
</SXLEMAP>
;
Run;

Re: Querying Metadata using Proc Metadata

ballardw — Mon, 06 May 2019 14:29:13 GMT

And what does your LOG show? You might want to post the log starting with the first filename statement.

Copy and paste into a code box opened with the forum's {I} or "running man" icon.

Re: Querying Metadata using Proc Metadata

Scott_Mitchell — Mon, 06 May 2019 22:30:17 GMT

22         /* Assign filerefs and libref. */
23         filename query "&datapath.\results2.xml" lrecl=256;
24         filename rawdata "&datapath.\results3.xml" lrecl=256;
25         filename map temp;
26         libname myxml xml xmlfileref=rawdata xmlmap=map;
NOTE: Libref MYXML was successfully assigned as follows: 
      Engine:        XML 
      Physical Name: 
27         
28         /* Create temporary query file.                           */
29         /* 2309 flag plus template gets table name, column name,  */
30         /* engine, libref, and object IDs. The template specifies */
31         /* attributes of the nested objects.                      */
32         
33         data _null_;
34            file query;
35            input;
36            put _infile_;
37            datalines;

NOTE: The file QUERY is:
      Filename=\\SASVM\SAS-DATA\LEV1\DATA\USER SANDPITS\SMLL\CLH SAS FORUM\DATA\results2.xml,
      RECFM=V,LRECL=256,File Size (bytes)=0,
      Last Modified=07 May 2019 08:29:12,
      Create Time=06 May 2019 13:50:53

NOTE: 14 records were written to the file QUERY.
      The minimum record length was 80.
      The maximum record length was 80.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
52         ;;

53         run;
2                                                          The SAS System                                 08:26 Tuesday, May 7, 2019

54         
55         proc metadata
56              in=query
57              out=rawdata;
58         run;

NOTE: PROCEDURE METADATA used (Total process time):
      real time           2.68 seconds
      cpu time            0.09 seconds
      

59         
60         
61         data _null_;
62            file map;
63            input;
64            put _infile_;
65            datalines;

NOTE: The file MAP is:
      Filename=F:\SAS\Lev1\SASWORK\_TD10044_SASVM_\#LN00282,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=07 May 2019 08:29:14,
      Create Time=07 May 2019 08:29:14

NOTE: 20 records were written to the file MAP.
      The minimum record length was 80.
      The maximum record length was 80.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
86         ;

87         Run;
88

@ballardw Log added as requested.