Generating and reading XML files with SAS 8.2

[Florent]

Dear SAS experts,

Had someone already used SAS to generate custom XML files ? By custom XML file, I mean XML files which can easily be read by another system (Oracle, Microsoft, ... etc).

Would you have some interesting documents, links, ... etc to share ? I've already tested the possibility to generate a XML and to read it in SAS but if I make the reading test with a XML generated with SQL Server I get an error message in the log.

It's important to know that the solution we have to provide to our client can only use SAS 8.2 Base & Advanced syntax (no other supplementary module provided).

Thanks in advance for you precious help !

With kind regards,

Florent

[Cynthia_sas]

Hi:
The SAS XML Libname Engine (SXLE) has a built in method to create Oracle-compliant XML. In SAS 8.2, this syntax, should work to create an Oracle XML file from a SAS dataset (export):
[pre]
libname classora xml 'c:\temp\class_ora.xml' xmltype=oracle;

** Predicted growth rate of 5% over 5 years;
** Create new variable for the XML file (do NOT need to do this part).;

data classora.CLASS;
set sashelp.class;
Height_in_5yr = height * 1.05;
run;

** Close the operating system files by clearing the LIBREF;
libname classora clear;

[/pre]

The key to using the XML Libname Engine is to understand that it uses a TAGSET template in order to define the type of XML tags that will be written around the data from SAS.

ODS can also create XML files, as it does when you use TAGSETS.EXCELXP to create Spreadsheet Markup XML for Excel.

All of the above was talking about EXPORTing from SAS to XML. Now, about IMPORTing XML into SAS form: Depending on the structure of the file that you've created with SQL Server, you may have created an XML file that is not "flat" enough to be imported into SAS data set form. If you look at the -default- XML that is created by SXLE, you will see that the output XML file is very regular and non-hierarchical. SAS, by default, wants to IMPORT this same kind of very regular XML with the XML Libname Engine:
[pre]
libname classxml xml 'c:\temp\class_default.xml';

** Predicted growth rate of 5% over 5 years;
** Create new variable for the XML file (do NOT need to do this part).;

data classxml.CLASS;
set sashelp.class;
Height_in_5yr = height * 1.05;
run;

** Close the operating system files by clearing the LIBREF;
libname classxml clear;
[/pre]

In order to import a hierarchical or non-standard XML file into SAS, you have 2 choices:
1) transform the XML file into a more standard form XML, as needed by SAS, using a 3rd party XML tool, such as XMLSpy, or an XSL transform, as described here:
http://support.sas.com/rnd/base/xmlengine/sxle82/prod82/index.html
or
2) build an XMLMAP that tells the SAS XML Libname Engine how to traverse the hierarchical XML tags in order to extract the data for a SAS dataset.

There is a tool called XMLMapper that helps build the Map syntax. XMLMapper (the tool) was first available in SAS 9 and there are some good screenshots and a usage scenario in these papers:
http://www2.sas.com/proceedings/forum2008/099-2008.pdf
http://www2.sas.com/proceedings/sugi29/119-29.pdf

What I can't remember is whether the XMLMap ability was also available in SAS 8.2 -- perhaps on an experimental basis. I looked at the main info sites, however, and it looks like XMLMap was first available in SAS 9:
http://support.sas.com/rnd/base/xmlengine/index.html
http://support.sas.com/rnd/base/xmlengine/sxle82/index.html

or you might wish to consult Tech Support to be absolutely sure. There were some Libname Engine capablilities that were backported to SAS 8.2 but I don't know whether XMLMap ability was one of those capabilities.

As for creating custom XML files for the SXLE Libname engine: if you want to generate custom XML -- such as for "Fred's In-House XML Program" or Justice Markup Language (http://www.iir.com/global/products/Global_Justice_XML_Data_Model_Overview.pdf)or ChemML (http://cml.sourceforge.net) you could modify one of the SAS provided tagset templates in order to generate "Fred's" XML tags or JusticeML or CML (ChemML) instead of the default XML tags that SXLE usually creates.

In order to create custom XML, you really need to understand the XML specification or "rules" for the XML tags that you need to create -- this generally involves an understanding of the DTD or XML Schema as well as the overall specification for the kind of XML you need to create.

I presented this PharmaSUG paper
http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt24.pdf
on creating custom tagset templates for use with the XML Libname Engine, however, the example code was written in SAS 9.1 syntax.

And, last, but not least, if your client is doing work in the Pharmaceutical industry, you may want to explore the PROC CDISC information at this site:
http://support.sas.com/rnd/base/xmlengine/proccdisc/index.html

cynthia

[Florent]

Cynthia,

As always, you provided a very clear and well documented response.

Thank you Very much for your help !

Florent

[Florent]

Cynthia,

Do you know if this is possible to use a XML Map file in order to create a XML file ?

E.g.: if I create the XML Map file from a sample of the XML file that in fact I want to create from multiple SAS datasets, would it be possible to reuse this XML map file in the other way ?

Sorry if this is not clear enough 😕

Kind regards,

Florent

[Cynthia_sas]

Florent:
In 9.2, you're supposed to be able to use an XMLMAP to write an XML file from a SAS dataset.
http://support.sas.com/documentation/cdl/en/whatsnew/61982/HTML/default/engxmlwhatsnew902.htm

But that capability is not available in SAS 8.2 or 9.1.
cynthia