DATA Step, Macro, Functions and more

How can I import XML file into SAS?

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

How can I import XML file into SAS?

Hi,

 

I just wonder if SAS are able to import XML file. If it is doable, how to do it (SAS code please)? Any requirements on XML file?

 

Thanks!

Nancy

 

 


Accepted Solutions
Solution
‎08-08-2016 09:54 PM
Super User
Posts: 9,662

Re: How can I import XML file into SAS?

Give you an example :

 

 

filename xx temp;
libname xx xmlv2 '/folders/myfolders/Spearman.xml' automap=replace xmlmap=xx;
proc copy in=xx out=work;
run;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,740

Re: How can I import XML file into SAS?

There is a lot of information about how to import XML using the SAS XML Libname Engine. Depending on the structure of your XML, you might or might not need an XML MAP file to map from the XML to a SAS dataset. There is too much to convey in a small space. Here's the doc reference:
http://support.sas.com/documentation/cdl/en/engxml/64990/HTML/default/viewer.htm#n1v7e5etkcqpqgn1lqr...

cynthia
Solution
‎08-08-2016 09:54 PM
Super User
Posts: 9,662

Re: How can I import XML file into SAS?

Give you an example :

 

 

filename xx temp;
libname xx xmlv2 '/folders/myfolders/Spearman.xml' automap=replace xmlmap=xx;
proc copy in=xx out=work;
run;
Contributor
Posts: 61

Re: How can I import XML file into SAS?

Thank you for your SAS code! KSharp

I had tried your code by using real XML file. There is no syntax error! Woman Happy That is good! But only 3 out of 4000 line data imported Smiley Sad. Here is my output:

 

affordability_ORDINAL calculatedTotalAnnualGrossRental amount1
1 1 26000
2 2 13000
3 3 13000

 

My XML file would have multiple layout - it could be deep in 7 layers. It used "xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

 

Have you got SAS code to coping with much more complexed XML file please (ie: real world XML file not academic XML file)?

 

Thanks!

Nancy

Super User
Posts: 17,745

Re: How can I import XML file into SAS?

Can you open your XML file in Excel?

 

Without a sample of your XML its hard to help. Can you post a snippet?

Contributor
Posts: 61

Re: How can I import XML file into SAS?

I can only open XML in Excel as a read-only workbook. Please find the attached sample TXT file. Since I could not post the attachment as XML file, so I saved it in TXT file, you need NotePad++ to convert to XML file.

 

Please note the data in XML is artifical data not real values. Just to illustrate the XML data structure.

 

Please let me know if this is doable or not.

 

Thanks!

Super User
Posts: 9,662

Re: How can I import XML file into SAS?

Do you have XML Map ?

I attached a PDF. All thing about XML is in there.

 

 

Contributor
Posts: 61

Re: How can I import XML file into SAS?

I don't have XML map. I attached a sample XML file (saved in TXT format for posting). How to get XML map file? I only can get XML file.

Contributor
Posts: 61

Re: How can I import XML file into SAS?

Sorry, Just correct what I said before. The data is imported but not in the structured layout. There are all flat structures and in wrong hierarchy.

SAS Super FREQ
Posts: 8,740

Re: How can I import XML file into SAS?

Hi:

   The SAS dataset that you import will not maintain the hierarchical structure of your original XML file. Remember that a SAS dataset is a data structure composed of rows and columns, so every row or observation has the same number of columns.

On the other hand, with an XML definition, you could have one structure where the <WOMBAT> element was optional. But SAS does not have the possibility of optional columns. Every row will have the same number of columns -- some of the columns may have missing values on some rows, but the way that it works with importing XML into SAS data format is that your XML elements are turned into SAS columns. There's a very simple example of a non-hierarchical XML file shown in the screen shot below.

 
cynthia 

  import_xml_into_sas.png

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1524 views
  • 3 likes
  • 4 in conversation