topic Re: XMLMAP with XPATH statements in SAS Programming

XMLMAP with XPATH statements

deleted_user — Mon, 11 Jan 2010 22:17:39 GMT

I'm looking to transform some data where we have a group of question/answer sections for different options. The basic format is as such:

123

FavoriteColor
blue

FavoriteFood
pizza

There can be a large list of questions/answers but what I really want to do is setup a datasaet with id, FavoriteColor, and FavoriteFood variables. Can I setup the path in an XML map which basically says something such as:

person/qa[question='FavoriteFood']/answer
person/qa[question='FavoriteColor']/answer

Data should look like:

ID FavoriteFood FavoriteColor
123 pizza blue

When I attempt something similar to this in the SAS XML mapper I get no value. Is this even possible to do within a .map file?

I could possibly transpose this via PROC TRANSPOSE, but I am really only interested in mapping a few key values.

Thoughts or suggestions?

Re: XMLMAP with XPATH statements

SUN59338 — Tue, 12 Jan 2010 17:21:46 GMT

try to move id line inside each qa. it works to define a library refer to the file under SAS 9.2 . then you may use proc transpose or other approach to get what you need.

123
FavoriteColor
blue

123
FavoriteFood
pizza

libname test xml "yourxmlfile.xml";

Re: XMLMAP with XPATH statements

Paul_Kent_SAS — Wed, 13 Jan 2010 00:55:01 GMT

sorry. you cant use fancy xpath like you want to; You dont have to re-arrange the XML however.

i used the map/transpose technique you described.

here is the xml i used for your data (i added a mystuff root node):

123

FavoriteColor
blue

FavoriteFood
pizza

here is my map:

/mystuff/person/qa

/mystuff/person/id
numeric
integer

/mystuff/person/qa/question
character
string
13

/mystuff/person/qa/answer
character
string
5

and here is my sas job:
filename bryanmat 'C:\Paul\XML\bryanmat.xml';
filename SXLEMAP 'C:\Paul\XML\brianmat.map';
libname bryanmat xml92 xmlmap=SXLEMAP access=READONLY;

data work.person;
set bryanmat.person;
run;

proc print;
title 'before transpose';
run;

proc transpose data=person out=person;
by id;
id question;
var answer;
where question in ('FavoriteColor', 'FavoriteFood');
run;

proc print;
title 'after transpose';
run;

Re: XMLMAP with XPATH statements

deleted_user — Wed, 13 Jan 2010 15:12:47 GMT

Thanks Paul. I was working on going the PROC TRANSPOSE route but I didn't think of using it with a where clause. I think that helped to solve the missing piece of the puzzle.

Re: XMLMAP with XPATH statements

Paul_Kent_SAS — Wed, 13 Jan 2010 18:03:14 GMT

You bet; if the file are really huge, it makes sense to throw the unwanted ones away right when you extract them from the XML...

data work.person;
set bryanmat.person;
where question in ('FavoriteColor', 'FavoriteFood');
run;

Re: XMLMAP with XPATH statements

Paul_Kent_SAS — Wed, 13 Jan 2010 18:41:03 GMT

Oops. Sorry about the bryan/brian mix up. Daughters beau is a bryan and my tired fingers musta been on automatic 😞