BookmarkSubscribeRSS Feed
metallon
Pyrite | Level 9

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


xml_req.png
16 REPLIES 16
Steelers_In_DC
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Cynthia_sas
SAS Super FREQ

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

metallon
Pyrite | Level 9

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

metallon
Pyrite | Level 9

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

metallon
Pyrite | Level 9

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;

Kurt_Bremser
Super User

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.

metallon
Pyrite | Level 9

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?

metallon
Pyrite | Level 9

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>

metallon
Pyrite | Level 9

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 😕

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

metallon
Pyrite | Level 9

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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
  • 16 replies
  • 8832 views
  • 6 likes
  • 6 in conversation