topic Re: To import XML file (Hierarchical structured) into SAS in SAS Programming

To import XML file (Hierarchical structured) into SAS

Jessy — Sat, 07 Jun 2014 22:35:11 GMT

Hi,
I am trying to import XML file (which looks like the one attached) into SAS EG 6.1, to convert into a SAS dataset. This attached XML file, I am importing is not a well formatted file, it has a hierarchical XML structure and hence using the libname statement does not work.

I have two options to do this,

1. To open the XML file and then save it as Excel file and then import it into SAS EG.
2. Use a SAS XML Mapper tool to import this XML file and then generate a XML map from the XML structure.
-Copy the generated SAS code and use it in SAS EG 5.1, make necessary changes to the code and then go from there.

Neither of the above options work for me, because it had to be done manually. I am supposed to write a program in SAS EG, to import that XML file into a SAS data set and this same program will be scheduled to run every month (automated program).

Can somebody suggest me on how to import a hierarchical XML file into SAS programmatically.

Or, if you have any other options, like any other tools or software to work with XML files, please share here.

Thank you.

Re: To import XML file (Hierarchical structured) into SAS

Tom — Sun, 08 Jun 2014 13:07:27 GMT

I would follow the XML mapper process. That is "writing a program" that you can run in EG to read the XML data. It just happens that part of the program is the XML map file needed to make the XML engine work for this file. If you need to you can create a program that uses a data step to write the xml map file.

filename xmlmap temp;

data _null_;

file xmlmap;

put '......';

run;

libname in xml 'xml.file.xml' xmlmap=xmlmap;

Re: To import XML file (Hierarchical structured) into SAS

Jessy — Sun, 08 Jun 2014 20:43:48 GMT

Hi Tom,

Thanks for looking into it. I am not an expert with XML stuff. Can you be more clear on this. How does this converts the XML into a SAS data set?

Thank you.

Re: To import XML file (Hierarchical structured) into SAS

Tom — Mon, 09 Jun 2014 22:39:34 GMT

The XML libname engine will allow you to read XML files into datasets. But for it to work you need to create instructions for how the items and attributes in your XML sheet map to SAS variables in the table. You can begin reading more about it here: SAS(R) 9.3 XML LIBNAME Engine: User's Guide, Second Edition

Once you have created a map that you can use to convert your XML to a nice rectangular table structure (or multiple tables if need be) then you can being working with the data in SAS.

Re: To import XML file (Hierarchical structured) into SAS

Reeza — Tue, 10 Jun 2014 00:21:14 GMT

Does the map have any correspondence to an XSD file?

Re: To import XML file (Hierarchical structured) into SAS

Jessy — Wed, 11 Jun 2014 21:26:13 GMT

Yes, it has a corresponding XSD file. Does it had to do something with XML and SAS dataset.

Re: To import XML file (Hierarchical structured) into SAS

Reeza — Wed, 11 Jun 2014 22:08:30 GMT

The XSD file should help you generate your XML map to feed the map portion. You'll still have to read up on how to do this though.

Re: To import XML file (Hierarchical structured) into SAS

BrunoMueller — Wed, 11 Jun 2014 22:26:49 GMT

Since SAS9.3 there is an AUTOMAP= option for the XMLV2 libname engine. This will create the map file automatically for you. See more details here SAS(R) 9.3 XML LIBNAME Engine: User's Guide, Second Edition the below sample code was taken from the doc:

filename myxml 'C:\temp\xmlexample.xml'; 
filename mymap 'C:\temp\xmlexample_generate.map'; 

libname myxml xmlv2 automap=replace xmlmap=mymap; 

proc contents data=myxml._all_;
run;

Please be aware that this might create quite a number of datasets depending on the nesting levels of you XML, yours has quite some nesting. These data sets then need to be joined together.

Since the XMLMAP= file is only a text file, in can be easily created using a DATA Step like the one below:

filename mymap temp; 
data _null_; 
  infile cards; 
  input; 
  file mymap;
  put _infile_; 
cards4; 
<?xml version="1.0" encoding="UTF-8"?>
<SXLEMAP version="2.1">
    <!-- ############################################################ -->
    <TABLE description="CLASS" name="CLASS">
        <TABLE-PATH syntax="XPath">/TABLE/CLASS</TABLE-PATH>
        <COLUMN name="Name">
            <PATH syntax="XPath">/TABLE/CLASS/Name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>7</LENGTH>
        </COLUMN>
        <COLUMN name="Sex">
            <PATH syntax="XPath">/TABLE/CLASS/Sex</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>1</LENGTH>
        </COLUMN>
    </TABLE>
</SXLEMAP>
;;;;

libname myxml xmlv2 "c:\temp\myclass.xml" xmlmap=mymap;
proc copy in=myxml out=work;
run; 
libname myxml clear;

Re: To import XML file (Hierarchical structured) into SAS

BillM_SAS — Thu, 03 Jul 2014 14:17:08 GMT

As Bruno points out, the AUTOMAP option can produce a large number of SAS data sets in the process of flattening the XML file. If your XML files are of the same format, they should be able to use just 1 XML Mapper file. So by creating a custom XML Mapper file using the XML Mapper application, you could produce an XML Mapper file that creates only the data sets you need. There is no need to create the XML Mapper file via DATA step code. Once the file exists, just reference it from the XMLMAP option. I discuss these issues and have examples in my 2013 SAS Global Forum paper.

http://support.sas.com/resources/papers/proceedings13/024-2013.pdf

For the AUTOMAP option, see pages 5-10. For tailoring an XML Mapper file, see pages 14-16. Note that you can download all the example code I use in the paper from here: http://support.sas.com/rnd/papers/sasgf13/024_2013_paper_examples.zip

Re: To import XML file (Hierarchical structured) into SAS

RobertHuey — Wed, 24 Sep 2014 17:08:41 GMT

How can you use the XSD file to import your XML file?

Re: To import XML file (Hierarchical structured) into SAS

Reeza — Wed, 24 Sep 2014 18:02:48 GMT

I never figured that out.

Bruno's method worked for me.

Re: To import XML file (Hierarchical structured) into SAS

ptimusk — Wed, 20 Jul 2016 04:41:01 GMT

I can also import schemes this way to make maps that work in XML mapper.