How to parse long value string

[Chuy]

I am attempting to get prompt information associated with a Stored Process by using Proc Metadata.  The output i get is an XML file of which is then used to create an XML Map with SAS XML Mapper.

One resulting variable/column has all the necessary information (see below), but the problem is the information is one long string and needs to be parsed, hence my question.

How can this be parsed from a single column into numerous columns?

I have formatted the value string so the pattern can be deciphered.

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

<DefinitionsAndSubgroups>

<TextDefinition allowUserValues="false" hidden="true" name="Year" promptId="PromptDef_1361550692752_245256">

<Label>

<Text xml:lang="en-US">Select Year:</Text>

</Label>

<DefaultValue>

<String value="2002">

</String>

</DefaultValue>

<ValueProvider>

<PromptColumnValueProvider distinct="true" formatLabels="true" sortFirst="SortNone" tableUrl="URL::/User Folders/User/Data/MYTABLE1(Table)" threshold="1000">

<LabelColumn>

<ColumnInfo format="-Default-" name="YEAR">

</ColumnInfo>

</LabelColumn>

<ValueColumn>

<ColumnInfo name="YEAR">

</ColumnInfo>

</ValueColumn>

</PromptColumnValueProvider>

</ValueProvider>

</TextDefinition>

<TextDefinition allowUserValues="false" hidden="true" name="Location" promptId="PromptDef_1362503931112_781477">

<Label>

<Text xml:lang="en-US">Select Location:</Text>

</Label>

<DefaultValue>

<String value="My Location">

</String>

</DefaultValue>

<ValueProvider>

<PromptColumnValueProvider distinct="true" formatLabels="true" sortFirst="SortNone" tableUrl="URL::/User Folders/User/Data/MYTABLE2(Table)" threshold="1000">

<LabelColumn>

<ColumnInfo format="-Default-" name="LOCATION">

</ColumnInfo>

</LabelColumn>

<ValueColumn>

<ColumnInfo name="LOCATION">

</ColumnInfo>

</ValueColumn>

</PromptColumnValueProvider>

</ValueProvider>

</TextDefinition>

</DefinitionsAndSubgroups>

<Label>

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

</Label>

</PromptGroup>"

[LinusH]

Are you asking how to this in XML Map Studio?

I'm not very familiar with that, but once mapped, I guess you could parse these string using SAS programs, where there are a bunch of string functions at hand.

[Tom]

The only value that looks like it might need to be parsed is: promptId="PromptDef_1361550692752_245256"

If you have done the XML map properly then this value should be available. You can use the SCAN() function in a data step to parse it.

libname xml xml ..... ;

data want;

  set xml.have;

  length PromptID1-PromptID3 $20;

  promptid1 = scan(promptid,1,'_');

  promptid2 = scan(promptid,2,'_');

  promptid3 = scan(promptid,3,'_');

run;

[Chuy]

I've resolved this by writing out the column to an XML file in a data step and creating another XML map then using the XML Engine to read it in.

- thanks

[max7]

Hi Chuy,

Can you share the details (steps, code ) to achieve this ?

[SIV]

can you share the details of what you did to get the XML string parsed?