BookmarkSubscribeRSS Feed
Chuy
Calcite | Level 5

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

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Fluorite | Level 6

Hi Chuy,

 

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

SIV
Calcite | Level 5 SIV
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1345 views
  • 0 likes
  • 5 in conversation