topic Re: SAS to generate XML output in ODS and Base Reporting

SAS to generate XML output

metallon — Wed, 08 Apr 2015 15:49:52 GMT

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

Re: SAS to generate XML output

Steelers_In_DC — Wed, 08 Apr 2015 16:49:40 GMT

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;

Re: SAS to generate XML output

RW9 — Wed, 08 Apr 2015 17:35:39 GMT

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.

Re: SAS to generate XML output

Cynthia_sas — Wed, 08 Apr 2015 18:35:22 GMT

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

Re: SAS to generate XML output

metallon — Thu, 09 Apr 2015 08:19:05 GMT

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 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

Re: SAS to generate XML output

metallon — Thu, 09 Apr 2015 08:23:25 GMT

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 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.

Re: SAS to generate XML output

RW9 — Thu, 09 Apr 2015 14:22:34 GMT

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

Re: SAS to generate XML output

metallon — Fri, 10 Apr 2015 11:40:12 GMT

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;

Re: SAS to generate XML output

Kurt_Bremser — Fri, 10 Apr 2015 11:45:34 GMT

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.

Re: SAS to generate XML output

metallon — Thu, 07 May 2015 07:14:18 GMT

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"?>

</OUTPUT>

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

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

<DESCRIPTION>ResultMessage_ORDINAL</DESCRIPTION>

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

<TYPE>numeric</TYPE>

<DATATYPE>integer</DATATYPE>

</COLUMN>

Re: SAS to generate XML output

metallon — Fri, 08 May 2015 12:18:04 GMT

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?

Re: SAS to generate XML output

metallon — Fri, 29 May 2015 07:11:23 GMT

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 😕

Re: SAS to generate XML output

RW9 — Fri, 29 May 2015 09:00:57 GMT

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).

Re: SAS to generate XML output

metallon — Mon, 01 Jun 2015 08:08:59 GMT

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.

Re: SAS to generate XML output

RW9 — Mon, 01 Jun 2015 08:19:06 GMT

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

Re: SAS to generate XML output

metallon — Thu, 23 Jul 2015 14:12:31 GMT

We are going to engagne SAS consulting services to develop us a nice and neat custom ODS tagset.

Re: SAS to generate XML output

jeffreyListed — Fri, 08 Feb 2019 18:54:16 GMT

I started using the newer 7.1 SAS EG and tried again to generate XML and then went back to using put statements within SAS trying to create a complex XML output manually as the mapper does to work for complexity with similar results in generation. I saw your suggestion on the other program “Fox” and wanted to state that I have had success with "Netbeans". This XML tool allows me to input SAS output files and run them threw the NetBeans' coding generating XML output and load into a server that is very particular of the format (meaning one wrong line and failure). This again lets me output from SAS a "Details file" (more line driven) and a "header file" (distinct counts and overall counts by portfolio / segment). These two files merry up so to speak in Netbeans which output extensive XML. I have had success with generating over 350 thousand rows for claims which require counts from the headers to the counts within the coding of the file.

If anyone has had success with the newer versions let me know but for now it’s SAS to generate outputs and then use Netbeans.

FYI: I do wish SAS would either partner with a company with its tools to output complex XML or fix the mapper to generate something similar. Their past experience has been great with tools like Apache, Microsoft etc.