BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nancy05
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User

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;
Nancy05
Quartz | Level 8

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

Reeza
Super User

Can you open your XML file in Excel?

 

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

Nancy05
Quartz | Level 8

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!

Ksharp
Super User

Do you have XML Map ?

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

 

 

Nancy05
Quartz | Level 8

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.

Nancy05
Quartz | Level 8

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.

Cynthia_sas
SAS Super FREQ

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

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
  • 9 replies
  • 28252 views
  • 8 likes
  • 4 in conversation