BookmarkSubscribeRSS Feed
RameshChandraKeer
Calcite | Level 5

Hi,

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

<ABCD>

<USER  Name="Jorge" LastName="Frenklin London "  Age=25 />

<USER  Name="Abdul" LastName="J Mclean" Age=50 />

</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,

4 REPLIES 4
Tom
Super User Tom
Super User

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;

RameshChandraKeer
Calcite | Level 5

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?


Tom
Super User Tom
Super User

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;

AhmedAl_Attar
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 815 views
  • 5 likes
  • 3 in conversation