The SAS Output Delivery System and reporting techniques

SAS to generate XML output

Reply
Frequent Contributor
Posts: 133

SAS to generate XML output

Hi SAS Experts,

The attached document shows the required XML output/tags that I need to generate.

I read about different methods like PROC XSL, ODS Tagsets, XMLMap and wonder which one is the most suitable.

Can you advise?

Thanks

Attachment
Valued Guide
Posts: 854

Re: SAS to generate XML output

try this as a template:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS to generate XML output

The png you have posted looks to me more like its an XSD file, i.e. an XML Schema Document?  Its pretty definately not an output for Excel.  Clarify what it is your trying to do. 

You can read/write XML using XMLMAP and the xml mapper tool provided by SAS: SAS Downloads: SAS XML Mapper

I haven't used them myself so if there any good or not I cant tell.

Second option is to learn to create your own tagset definition.  You can find templates/tagsets/styles if you right click on Results in SAS, and select templates.  Navigate until you find the Excelbase tagset.  You can open it and look at that for the amount of work involved.

Finally, if its simple, and your seems to be, you may consider just creating a dataset with all the tags etc. and just writing that straight out as a text file.

SAS Super FREQ
Posts: 8,645

Re: SAS to generate XML output

I think you're right, that does look like a schema definition. The XML Mapper would definitely be the way. I always like to ask for a "working" XML file to accompany the schema, this lets me see any data transformations that I might need to make with the SAS data that will be used to create the XML file.

cynthia

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

Hi SAS Experts,

Thanks for you hints. Yes indeed, it is the XSD file. I can´t really post the XML because

I havent generated it yet with SAS Smiley Happy  That is the whole topic of this thread.

I have clarified what I want to do and it was not "reading XML" but generating i.e. writing XML.

Can the XMLMAP deal with XML hiearchies like this one:

http://www.w3schools.com/xml/nodetree.gif

I created this thread asking for help so I dont run with one specific way of doing things i.e. SAS XML Library

or PROC XSL and figuring out after days of work that I can´t deal with "complex" XML hierarchies.

I dont need microsoft-specific-excel-xml but xml that works with my XSD.

Thanks

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

I will look into the XML Mapper.

Thanks for you hints. Yes indeed, it is the XSD file. I can´t really post the XML because

I havent generated it yet with SAS Smiley Happy  That is the whole topic of this thread.

I have clarified what I want to do and it was not "reading XML" but generating i.e. writing XML.

Can the XMLMAP deal with XML hiearchies like this one:

http://www.w3schools.com/xml/nodetree.gif

I created this thread asking for help so I dont run with one specific way of doing things i.e. SAS XML Library

or PROC XSL and figuring out after days of work that I can´t deal with "complex" XML hierarchies.

I dont need microsoft-specific-excel-xml but xml that works with my XSD.

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS to generate XML output

Well, apparently from SAS 9.2 XML Maps can be used to write out XML.  Again, not used it myself.  Am looking at exporting some data to XML in compliance with a Schema myself at the moment, best advice I can give currently is to get a proper XML editor, Oxygen or XML Fox.  They allow importing of delimited files, then create an XLST to transform the import data to XML.  It gives you the flexibility of XML, and you can run checks, queries etc. http://www.rustemsoft.com/xmlscenar.asp

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

Hi RW9,

Thanks for your feedback. Good that we have this forum to exchange the progress with exporting XML.

When I open the XSD in SAS XML Mapper an error pops up saying Input length =1 but nothing

else happens. So seems to be OK to ignore.

I followed this paper (example 4) to export into XML but get this error:

https://support.sas.com/rnd/base/xmlengine/XMLtipsheet.pdf

ERROR:  Read Access Violation DATASTEP (2)

Exception occurred at (04715F8E)

/*The XMLMap is needed to map the SAS data set

into a specific XML document structure rather

than a rectuangular one.*/

libname out xmlv2 'R:\sas_xml\xxxxx.xml'

xmltype=xmlmap xmlmap='R:\sas_xml\sas_xmlmap.map';

data out.sample;

    set positives;

run;

Esteemed Advisor
Posts: 5,989

Re: SAS to generate XML output

That looks like a case for SAS TS. Read Access Violation is a memory handling problem in the SAS system itself, which should not happen.

Basically all errors of this type have been repaired with a fix, if you look at the SAS knowledge base.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: SAS to generate XML output

It looks like that the SAS XMLMapper created table   <TABLE description="... for each hierarchy level and defined the path with

<TABLE-PATH . So far so good. with the following I can only write to ONE of those tables:

data out.SamplingType;   /*XML Target space*/

   set work.xml_kom;     /*SAS Dataset source*/

run;

I got about 5. how can I set which dataset columns go to which table or field in my xmlmap?

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

Hello SAS Experts,

I have somehow avoided the Read Access Violation by cleaning up the data set.

Now, after continued reading and experiments with the SAS XML Mapper I learned that

I have to specify a "Output" table within the XML MAP. However if I do that

the structure is not considered anymore and instead I see a flat hierarchy and not the XML

structure as it is defined in the XSD.

Can someone confirm that oberservation or am I on the wrong track?

data out.Results;   /*XML Target space*/

   set work.xml_kom;     /*SAS Dataset source*/

   rename PERIOD=Period

   SPECIESNAME=SamplingType;

run;

<?xml version="1.0" encoding="UTF-8"?>

<!-- ############################################################ -->

<!-- 2015-05-07T09:06:06 -->

<!-- SAS XML Libname Engine Map -->

<!-- Generated by XML Mapper, 904000.0.0.20130522190000_v940 -->

<!-- ############################################################ -->

<SXLEMAP name="nrkp_xml_kom" version="2.1">

    <!-- ############################################################ -->

    <OUTPUT>

        <TABLEREF name="Results"/>

    </OUTPUT>

The actual path (as in the XML Mapper defined) is:

    <TABLE description="Period" name="Period">

        <TABLE-PATH syntax="XPath">/ResidueMessage/ResultMessage/Period</TABLE-PATH>

        <COLUMN class="ORDINAL" name="ResultMessage_ORDINAL">

            <DESCRIPTION>ResultMessage_ORDINAL</DESCRIPTION>

            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/ResidueMessage/ResultMessage</INCREMENT-PATH>

            <TYPE>numeric</TYPE>

            <DATATYPE>integer</DATATYPE>

        </COLUMN>

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

I spoke to SAS technical support. There is currently NO WAY to export into XML files with complex hierarchies using XML Mapper.

XML Mapper let´s you only export into a single table...which is not helpful. SAS tech support said that customers had to resolve to writing the XML document using PUT statements. Very bad news. I spent days trying to get my head around XML Mapper only to figure out that its not the right way :/

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS to generate XML output

Yes, doesn't really surprise me.  As I always suggest though, use the right tool for the job.  If your dealing with XML, use some proper XML tools (XML Fox is free). 

Frequent Contributor
Posts: 133

Re: SAS to generate XML output

Hi,

I dont need a tool to look or validate XML documents, I got XML Spy. I need a way to write my SAS DataSet as XML validated through the XSD.

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS to generate XML output

Export your data to CSV.  Load both the XSD and the CSV into your XML Tool and export it to XML:

How to convert Excel Sheet to XSD using XML SPY | SCN

Post a Question
Discussion Stats
  • 15 replies
  • 2573 views
  • 6 likes
  • 5 in conversation