06-07-2014 06:35 PM
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.
06-08-2014 09:07 AM
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;
libname in xml 'xml.file.xml' xmlmap=xmlmap;
06-08-2014 04:43 PM
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?
06-09-2014 06:39 PM
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.
06-11-2014 06:26 PM
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:
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:
07-03-2014 10:17 AM
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.
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