SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to parse long value string

Reply
Occasional Contributor
Posts: 10

How to parse long value string

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>"

Super User
Posts: 5,257

Re: How to parse long value string

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.

Data never sleeps
Super User
Super User
Posts: 6,502

Re: How to parse long value string

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;

Occasional Contributor
Posts: 10

Re: How to parse long value string

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

Ask a Question
Discussion stats
  • 3 replies
  • 337 views
  • 0 likes
  • 3 in conversation