11-28-2012 06:39 AM
I have an XML file need to read data from this.The contents of XML file as below:
<USER Name="Jorge" LastName="Frenklin London " Age=25 />
<USER Name="Abdul" LastName="J Mclean" Age=50 />
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?
11-28-2012 10:05 AM
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 @ ;
length name lastname $100 age 8 ;
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 ;
11-29-2012 04:27 AM
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?
11-29-2012 10:22 AM
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" >
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;
11-29-2012 10:34 AM
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,