topic Re: How to parse long value string in SAS Data Management

How to parse long value string

Chuy — Tue, 05 Mar 2013 19:24:02 GMT

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

<Label>

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

</Label>

</String>

</DefaultValue>

</ColumnInfo>

</LabelColumn>

</ColumnInfo>

</ValueColumn>

</PromptColumnValueProvider>

</ValueProvider>

</TextDefinition>

<Label>

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

</Label>

</String>

</DefaultValue>

</ColumnInfo>

</LabelColumn>

</ColumnInfo>

</ValueColumn>

</PromptColumnValueProvider>

</ValueProvider>

</TextDefinition>

</DefinitionsAndSubgroups>

<Label>

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

</Label>

</PromptGroup>"

Re: How to parse long value string

LinusH — Wed, 06 Mar 2013 09:59:15 GMT

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.

Re: How to parse long value string

Tom — Wed, 06 Mar 2013 15:32:07 GMT

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;

Re: How to parse long value string

Chuy — Wed, 06 Mar 2013 16:29:09 GMT

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

Re: How to parse long value string

SIV — Mon, 09 Oct 2017 17:36:34 GMT

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

Re: How to parse long value string

max7 — Fri, 20 Sep 2019 06:27:32 GMT

Hi Chuy,

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