topic Re: How can I import XML file into SAS? in SAS Programming

How can I import XML file into SAS?

Nancy05 — Wed, 13 Jul 2016 02:18:36 GMT

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

Re: How can I import XML file into SAS?

Cynthia_sas — Wed, 13 Jul 2016 03:00:53 GMT

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#n1v7e5etkcqpqgn1lqrombqm6z6b.htm

cynthia

Re: How can I import XML file into SAS?

Ksharp — Wed, 13 Jul 2016 03:09:32 GMT

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;

Re: How can I import XML file into SAS?

Nancy05 — Wed, 13 Jul 2016 03:45:47 GMT

Thank you for your SAS code! KSharp

I had tried your code by using real XML file. There is no syntax error! That is good! But only 3 out of 4000 line data imported . 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

Re: How can I import XML file into SAS?

Nancy05 — Wed, 13 Jul 2016 03:55:48 GMT

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.

Re: How can I import XML file into SAS?

Reeza — Wed, 13 Jul 2016 04:36:11 GMT

Can you open your XML file in Excel?

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

Re: How can I import XML file into SAS?

Ksharp — Wed, 13 Jul 2016 04:58:05 GMT

Do you have XML Map ?

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

Re: How can I import XML file into SAS?

Cynthia_sas — Wed, 13 Jul 2016 13:22:45 GMT

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

Re: How can I import XML file into SAS?

Nancy05 — Wed, 13 Jul 2016 23:42:44 GMT

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!

Re: How can I import XML file into SAS?

Nancy05 — Wed, 13 Jul 2016 23:51:11 GMT

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.