PROC Metadata

[nickb]

I'm working on putting together a proc metadata script that help me find out all prompt values in a stored procedure that have a value of 3.  I have been working on this and can't get it to work.  Any suggestions?

options metaserver="##########"

        metaport=8561

        metauser="sasadm"

        metapass="########"

        metarepository="Foundation"

        metaprotocol=BRIDGE;

filename outxml "c:\temp\stps.xml";

filename SXLEMAP "c:\temp\stps.map";

proc metadata in=

"<GetMetadataObjects>

    <Reposid>$METAREPOSITORY</Reposid>

    <Type>ClassifierMap</Type>

    <Objects/>

    <NS>SAS</NS>

    <Flags>2441</Flags>

    <Options>

       <XMLSelect search=""*[@PublicType='StoredProcess']""/>

       <Templates>

          <ClassifierMap Name="""" Id="""">

              <Prompts/>

          </ClassifierMap>

          <PromptGroup Name="""" Id="""" GroupInfo=""""/>

       </Templates>

    </Options>

</GetMetadataObjects>" out=outxml;

run;

data _null_;

  file SXLEMAP;

  put '<?xml version="1.0" encoding="windows-1252"?>';

  put '<SXLEMAP name="SXLEMAP" version="2.1">';

  put '<TABLE name="STP_Prompts">';

  put '<TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Prompts/PromptGroup</TABLE-PATH>';

  put '   <COLUMN name="PromptName">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Prompts/PromptGroup/@Name</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>10</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="PromptId">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Prompts/PromptGroup/@Id</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>17</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="GroupInfo">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Prompts/PromptGroup/@GroupInfo</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>12666</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="STPName" retain="YES">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/@Name</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>44</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="STPId" retain="YES">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/@Id</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>17</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="FolderName" retain="YES">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Trees/Tree/@Name</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>9</LENGTH>';

  put '   </COLUMN>';

  put '   <COLUMN name="FolderId" retain="YES">';

  put '      <PATH syntax="XPath">/GetMetadataObjects/Objects/ClassifierMap/Trees/Tree/@Id</PATH>';

  put '      <TYPE>character</TYPE>';

  put '      <DATATYPE>string</DATATYPE>';

  put '      <LENGTH>17</LENGTH>';

  put '   </COLUMN>';

  put '</TABLE>';

  put '</SXLEMAP>';

run;

libname  outxml xmlv2 xmlmap=SXLEMAP access=READONLY;

data PromptMax3;

  set outxml.stp_prompts(where=(groupinfo ? 'max="3"'));

run;

proc print data=PromptMax3;

  var STPName STPId FolderName FolderId;

  title "List of SAS Stored Processes that have a maximum prompt value = 3";

run;

[Cynthia_sas]

Hi:

  I think this is probably a question for Tech Support. Unless folks have the BI Platform installed AND they use PROC METADATA, they are not likely to know what PROC METADATA is. Most folks using ODS for reporting are using BASE SAS and even some folks who write stored processes don't have privileges to use PROC METADATA against the repository.

cynthia

[PaulHomes]

Hi nickb,

I'm not quite sure what you meant by "can't get it to work" because it was working for me with little change.  Were you getting error messages, or just not the results you expected?

I took your code as a starting point and made a few changes.  I have attached the variation as a .sas file to this reply. 

I noticed that you had templates in your request but were not using the OMI_TEMPLATE (4) flag.  You did have OMI_ALL (1) so were getting everything you needed anyway. I've added in the OMI_TEMPLATE flag and the missing <Trees/> association and <Tree /> template

I was able to get it to report a test stored process that I created with a numeric prompt with a maximum value of 3.  This was the GroupInfo value for that test prompt for me:

<PromptGroup promptId="PromptGroup_1338612866026_660069" version="1.0">

  <DefinitionsAndSubgroups>

    <IntegerDefinition max="3" min="0" name="TestPrompt" promptId="PromptDef_1338612866027_580598">

      <Label>

        <Text xml:lang="en-AU">Test Prompt</Text>

      </Label>

    </IntegerDefinition>

  </DefinitionsAndSubgroups>

  <Label>

    <Text xml:lang="en-AU">Parameters</Text>

  </Label>

</PromptGroup>

Since the filter is a 'contains' into a large XML string in the GroupInfo variable for the prompt I am not sure how robust it would be and might potentially find the value in other text or descriptions in the XML string. Here's an example of the GroupInfo value for such a situation where the filter string is in the description too:

<PromptGroup promptId="PromptGroup_1338612866026_660069" version="1.0">

  <DefinitionsAndSubgroups>

    <IntegerDefinition max="3" min="0" name="TestPrompt" promptId="PromptDef_1338612866027_580598">

      <Label>

        <Text xml:lang="en-AU">Test Prompt</Text>

      </Label>

      <Description>

        <Text xml:lang="en-AU">A prompt for testing max="3" filter.</Text>

      </Description>

    </IntegerDefinition>

  </DefinitionsAndSubgroups>

  <Label>

    <Text xml:lang="en-AU">Parameters</Text>

  </Label>

</PromptGroup>

It's a bit of a contrived example   I guess it's unlikely (but possible) that this might happen for real. If necessary you could look into using SXLE again to parse each GroupInfo value specifically for the max attribute using the SXLE XPath '/PromptGroup/DefinitionsAndSubgroups/IntegerDefinition/@max'.

I hope this helps.

Cheers

Paul

[nickb]

Yeah, I should have been more descriptive about what happens when I run the code.  Below is the error that I receive when I run this:

ERROR: IOM call failed because of a data conversion error.

ERROR: Failed to transcode data from U_UTF8_CE to U_WLATIN1_CE encoding because it contained

characters which are not supported by your SAS session encoding.  Please review your encoding= and

locale= SAS system options to ensure that they can accommodate the data that you want to process.  A

portion of the source string, in hex representation is:

NOTE: b450138: 3c 47 65 74 4d 65 74 61 64 61 74 61 4f 62 6a 65 |<GetMetadataObje|

NOTE: b450148: 63 74 73 3e 3c 52 65 70 6f 73 69 64 3e 41 30 30 |cts><Reposid>A00|

ERROR: Some code points did not transcode.

[PaulHomes]

I have experienced this very same error myself.  I had previously changed the SAS configuration for my Linux platform to non-unicode rather than the default unicode (UTF-8), by having sas linked to sas_en rather than sas_u8. This was to work around an issue I was having with SAS/ACCESS to ODBC with the MySQL ODBC driver on Linux.  When I switched back to having unicode servers I no longer got the transcode error you also saw.

When I ran your sample code I noticed that it extracted metadata for the SAS provided sample stored processes which themselves include metadata with unicode characters in them for a variety of languages.

I think you could overcome this error by reconfiguring your servers, if appropriate for your site, to be unicode servers (-encoding UTF-8), or perhaps try adding conditions to your XMLSelect to exclude the SAS sample stored processes which return UTF-8 characters.

For more info have a look at the SAS 9.3 Intelligence Platform: Application Server Administration Guide under Encoding and Locale Information

[nickb]

I got through this error by starting SAS using the u8 config option.  -config "D:\Program Files\SASHome\SASFoundation\9.3\nls\u8\sasv9.cfg"

When I run this script without the where statement I am able to see all the SP's.  When I run it with the where statement I get an error stating "where clause operator requires character variables."  I ran a proc contents and the groupinfo is a character value.  I have tried several different combinations and still can't get it to run with the where clause and I'm not sure what is the problem.

With no where clause (obs=2):

1	Top 100 Report	A59JTH6S.B10002BD
2	courses	A59JTH6S.B10002BE	Stored Processes	A59JTH6S.AK000693

Thanks!

[nickb]

I should have also mentioned that Tech support supplied this script.  I'm new to the PROC meta function and I will take any suggestions on getting this information out.

Thanks.

[Cynthia_sas]

Hi:

  If you got the script from Tech Support and if the script is not working, then you should follow-up with Tech Support and ask for help modifying their script.

cynthia

[max7]

Hi Paul,

I have a similar requirement and trying to extract all the prompts associated with stored processes. tried using the proc metadata procedure and able to get the details (including Groupinfo) in the xml form. However I am unable to decode the xml-coded text completely as my 'Groupinfo' property contains values greater than 32767 bytes length. The same issue happens when we use the metadata datastep functions as well as sas datastep only supports 32767b character length. Is there any options in XML mapping which accomadates mapping of longer attribute values(>32767). Any xml map options which can search into the 'Groupinfo' attribute values and get only the required part rather than the entire 'Groupinfo' value which is of huge size (in this case > 32767) ?

Or any other work arounds?