topic Re: How to read XML file data? in SAS Programming

How to read XML file data?

RameshChandraKeer — Wed, 28 Nov 2012 11:39:47 GMT

Hi,

I have an XML file need to read data from this.The contents of XML file as below:

<ABCD>

</ABCD>

Now after reading above file I need data in below format:

Name LastName Age

Jorge Frenklin London 25

Abdul J Mclean 50

Please,anyone help how can I get this?

Thanks,

Re: How to read XML file data?

Tom — Wed, 28 Nov 2012 15:05:32 GMT

If your file can be made to conform to standard XML format then you could use the SAS XML Mapper tool to generate and XML mapping that you could use with the XML engine for a libname statement.

If the lines are simple like your example and the values always appear on one line then you could possible read it with a data step like this.

filename sample 'C:\downloads\sample.xml';

data user ;

infile sample ;

input @ ;

if index(_infile_,'<USER');

length name lastname $100 age 8 ;

l1=index(_infile_,'Name=');

l2=index(_infile_,'LastName=');

l3=index(_infile_,'Age=');

if l1 then input @l1 + 5 name :& $quote100. @ ;

if l2 then input @l2 + 9 lastname :& $quote100. @ ;

if l3 then input @l3 + 4 age @;

drop l1 l2 l3 ;

run;

Re: How to read XML file data?

RameshChandraKeer — Thu, 29 Nov 2012 09:27:17 GMT

Hi Tom,

Thanks a lot.

It's working fine.

Now I am looking to make it generic.

For example suppose in future I want to add one more column Address in XML file, In such case I need to update the sas code to read XML file.

Is it possible to make it generic?

Re: How to read XML file data?

Tom — Thu, 29 Nov 2012 15:22:05 GMT

If you create a well formed XML file then you can use the XML mapper tool to generate a map that you can use with the XML libname engine. I am not an XML expert but I was able to try and open the XML file with Windows Explorer and it did not like the construct AGE=25. It wanted quotes around the 25. Once I did this I was able to get the XML mapper tool to read the file.

Here is a simple map that can be used to read the file (once the quotes are added).

<?xml version="1.0" encoding="windows-1252"?>
<SXLEMAP version="1.2" >
<TABLE name="USER">
<TABLE-PATH syntax="XPath">/ABCD/USER</TABLE-PATH>

        <COLUMN name="Name">
            <PATH syntax="XPath">/ABCD/USER/@Name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>5</LENGTH>
        </COLUMN>

        <COLUMN name="LastName">
            <PATH syntax="XPath">/ABCD/USER/@LastName</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>16</LENGTH>
        </COLUMN>

        <COLUMN name="Age">
            <PATH syntax="XPath">/ABCD/USER/@Age</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

</TABLE>

</SXLEMAP>

Then you can use this program to read the table USER.

filename sample 'sample.xml';

filename sampmap 'sample.map';

libname sample xml92 xmlmap=sampmap access=readonly;

proc contents data=sample._all_; run;

proc print data=sample.user; run;

Re: How to read XML file data?

AhmedAl_Attar — Thu, 29 Nov 2012 15:34:15 GMT

Ramesh,

Check the SAS(R) 9.2 XML LIBNAME Engine: User's Guide, Second Edition , it has few sections and examples on how to import XML document.

Hope this helps,

Ahmed