XMLMAP with XPATH statements

[deleted_user]

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?

[SUN59338]

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

[Paul_Kent_SAS]

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;

[deleted_user]

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.

[Paul_Kent_SAS]

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;

[Paul_Kent_SAS]

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