What would be a proper way to export multiple (two or more) SAS datasets (dataset1, dataset2) to one XML data file and at the same time to refer to different tables in the XMLMap ?
When executing the following it works perfectly:
filename xmlout 'path\to\file\want.xml';
libname xmlout xmlv2 xmltype=xmlmap xmlmap='path\to\file\XMLmap.map';
data xmlout.dim;
set work.have_dim;
run;
I got data from another SAS table to export in the header (ID and time as shown in the .map). I would then just modify the .map with:
<OUTPUT>
<TABLEREF name="dim" />
<TABLEREF name="header" />
</OUTPUT>
and execute the following code:
data xmlout.header;
set work.have_header;
run;
However, I can not add a TABLEREF
to the header table as it is specified in the documentation: TABLEREF= specifies the name of the table in the XMLMap to be exported. The name must be unique in the XMLMap definition, and the name must be a valid SAS name, which can be up to 32 characters.
(cf. XML LIBNAME Engine
)
So my question is, how can I export those two SAS tables in the same XML file if I can not use different TABLEREF
?
At some point I was thinking to use a data step where I would just set the two tables together and only use one TABLEREF
in the .map like this:
data aggregated;
set have_dim have_header;
run;
But then, how can I redirect some of the variables to the header
table (ID and time) and the others in the dim
table in the XML output?
The desired XML output
would be:
<?xml version="1.0" encoding="UTF-8"?>
<ns2:message xmlns:ns2="http://www.someurl/common/vo/message" xmlns:ns3="http://www.someurl/common/vo/cube">
<ns2:header>
<ns2:ID>1234</ns2:ID>
<ns2:time>2021-01-19T09:20:47</ns2:time> <!-- the time of the day -->
</ns2:header>
<ns2:content>
<ns2:dataSegment id="OBSERVATION">
<ns2:cube id="ID_CODE">
<ns3:obs>
<ns3:dim name="CODE" value="ABC123" />
<ns3:dim name="VAR" value="VAR1"/>
<ns3:dim name="VALUE" value="Y"/>
<ns3:dim name="DATE" value="2021-01-01T00:00:00" />
</ns3:obs>
</ns2:cube>
<ns2:cube id="ID_CODE">
<ns3:obs>
<ns3:dim name="CODE" value="DEF456" />
<ns3:dim name="VAR" value="VAR2"/>
<ns3:dim name="VALUE" value="N"/>
<ns3:dim name="DATE" value="2021-01-01T00:00:00" />
</ns3:obs>
</ns2:cube>
</ns2:dataSegment>
</ns2:content>
</ns2:message>
I displayed only 2 observations for example's sake. Nonetheless there might be more than 1 million observations.
EDIT: Reproducible Example:
SAS Code
:
data have_header;
ID = 1234;
time = datetime();
run;
data have_dim;
infile datalines delimiter=",";
input CODE :$8. VAR :$8. VALUE $1. DATE:datetime20.;
format DATE datetime20.;
datalines;
ABC1,VAR1,Y,31DEC2020:00:00:00
ABC2,VAR2,N,31DEC2020:00:00:00
;
run;
filename dim'path\to\file\dim.xml';
libname dim xmlv2 xmltype=xmlmap xmlmap='path\to\file\map_dim.map';
data dim.dim;
set have_dim;
run;
filename header 'path\to\file\header.xml';
libname header xmlv2 xmltype=xmlmap xmlmap='path\to\file\map_header.map';
data header.header;
set have_header;
run;
libname dim clear;
libname header clear;
*** COMBINE ALL XML FILES;
proc xsl
in = "path\to\file\header.xml"
xsl = "path\to\file\script.xsl"
out = "path\to\file\final_output.xml";
run;
XML.map
for dim:
<?xml version="1.0" encoding="UTF-8"?>
<!-- ############################################################ -->
<!-- 2021-01-19T08:41:21 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 904300.0.0.20150204190000_v940m3 -->
<!-- ############################################################ -->
<!-- ### Validation report ### -->
<!-- ############################################################ -->
<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="2.1">
<NAMESPACES count="2">
<NS id="1" prefix="ns2">http://www.someurl/common/vo/message</NS>
<NS id="2" prefix="ns3">http://www.someurl/common/vo/cube</NS>
</NAMESPACES>
<OUTPUT>
<TABLEREF name="dim" />
</OUTPUT>
<!-- ############################################################ -->
<TABLE description="message" name="message">
<TABLE-PATH syntax="XPathENR">/{1}message</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="header" name="header">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}header</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="header_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}header</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="content" name="content">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="content_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="dataSegment" name="dataSegment">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</TABLE-PATH>
<COLUMN class="ORDINAL" name="content_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="dataSegment_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="dataSegment_id">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/@id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>11</LENGTH>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="cube" name="cube">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</TABLE-PATH>
<COLUMN class="ORDINAL" name="dataSegment_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="cube_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="cube_id">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/@id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>27</LENGTH>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="obs" name="obs">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</TABLE-PATH>
<COLUMN class="ORDINAL" name="cube_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="obs_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="dim" name="dim">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim</TABLE-PATH>
<COLUMN class="ORDINAL" name="obs_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="dim_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="CODE">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim/CODE</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>8</LENGTH>
</COLUMN>
<COLUMN name="VAR">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim/VAR1</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>8</LENGTH>
</COLUMN>
<COLUMN name="VALUE">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim/VALUE</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>1</LENGTH>
</COLUMN>
<COLUMN name="DATE">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim/DATE</PATH>
<TYPE>numeric</TYPE>
<DATATYPE>datetime</DATATYPE>
<FORMAT width="10">IS8601DA</FORMAT>
<INFORMAT width="10">IS8601DA</INFORMAT>
</COLUMN>
</TABLE>
</SXLEMAP>
XML.map
for header:
<?xml version="1.0" encoding="UTF-8"?>
<!-- ############################################################ -->
<!-- 2021-01-19T08:41:21 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 904300.0.0.20150204190000_v940m3 -->
<!-- ############################################################ -->
<!-- ### Validation report ### -->
<!-- ############################################################ -->
<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="2.1">
<NAMESPACES count="2">
<NS id="1" prefix="ns2">http://www.someurl/common/vo/message</NS>
<NS id="2" prefix="ns3">http://www.someurl/common/vo/cube</NS>
</NAMESPACES>
<OUTPUT>
<TABLEREF name="header" />
</OUTPUT>
<!-- ############################################################ -->
<TABLE description="message" name="message">
<TABLE-PATH syntax="XPathENR">/{1}message</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="header" name="header">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}header</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="header_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}header</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="ID">
<PATH syntax="XPathENR">/{1}message/{1}header/{1}ID</PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="time">
<PATH syntax="XPathENR">/{1}message/{1}header/{1}time</PATH>
<TYPE>numeric</TYPE>
<DATATYPE>datetime</DATATYPE>
<FORMAT width="19">IS8601DT</FORMAT>
<INFORMAT width="19">IS8601DT</INFORMAT>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="content" name="content">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content</TABLE-PATH>
<COLUMN class="ORDINAL" name="message_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="content_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="dataSegment" name="dataSegment">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</TABLE-PATH>
<COLUMN class="ORDINAL" name="content_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="dataSegment_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="dataSegment_id">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/@id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>11</LENGTH>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="cube" name="cube">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</TABLE-PATH>
<COLUMN class="ORDINAL" name="dataSegment_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="cube_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN name="cube_id">
<PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/@id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>27</LENGTH>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="obs" name="obs">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</TABLE-PATH>
<COLUMN class="ORDINAL" name="cube_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="obs_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
<!-- ############################################################ -->
<TABLE description="dim" name="dim">
<TABLE-PATH syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim</TABLE-PATH>
<COLUMN class="ORDINAL" name="obs_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
<COLUMN class="ORDINAL" name="dim_ORDINAL">
<INCREMENT-PATH beginend="BEGIN" syntax="XPathENR">/{1}message/{1}content/{1}dataSegment/{1}cube/{2}obs/{2}dim</INCREMENT-PATH>
<TYPE>numeric</TYPE>
<DATATYPE>integer</DATATYPE>
</COLUMN>
</TABLE>
</SXLEMAP>
XML output for dim
(dim.xml):
<?xml version="1.0" encoding="UTF-8"?>
<!--
SAS XML Libname Engine (SAS92XML)
SAS XMLMap Generated Output
Version 9.04.01M3P06242015
Created 2021-01-19T09:20:47
-->
<ns2:message xmlns:ns2="http://www.someurl/common/vo/message">
<ns2:content>
<ns2:dataSegment>
<ns2:cube>
<ns3:obs xmlns:ns3="http://www.someurl/common/vo/cube">
<ns3:dim>
<CODE>ABC1</CODE>
<VAR1>VAR1</VAR1>
<VALUE>Y</VALUE>
<DATE>2020-12-31T00:00:00</DATE>
</ns3:dim>
<ns3:dim>
<CODE>ABC2</CODE>
<VAR1>VAR2</VAR1>
<VALUE>N</VALUE>
<DATE>2020-12-31T00:00:00</DATE>
</ns3:dim>
</ns3:obs>
</ns2:cube>
</ns2:dataSegment>
</ns2:content>
</ns2:message>
XML output for header
(header.xml):
<?xml version="1.0" encoding="UTF-8"?>
<!--
SAS XML Libname Engine (SAS92XML)
SAS XMLMap Generated Output
Version 9.04.01M3P06242015
Created 2021-01-19T09:39:44
-->
<ns2:message xmlns:ns2="http://www.someurl/common/vo/message">
<ns2:header>
<ns2:ID>1234</ns2:ID>
<ns2:time>2021-01-19T09:39:44</ns2:time>
</ns2:header>
</ns2:message>
Desired Final XML output:
<?xml version="1.0" encoding="UTF-8"?>
<ns2:message xmlns:ns2="http://www.someurl/common/vo/message" xmlns:ns3="http://www.someurl/common/vo/cube">
<ns2:header>
<ns2:ID>1234</ns2:ID>
<ns2:time>2021-01-19T09:20:47</ns2:time> <!-- the time of the day -->
</ns2:header>
<ns2:content>
<ns2:dataSegment id="OBSERVATION">
<ns2:cube id="ID_CODE">
<ns3:obs>
<ns3:dim name="CODE" value="ABC1" />
<ns3:dim name="VAR" value="VAR1"/>
<ns3:dim name="VALUE" value="Y"/>
<ns3:dim name="DATE" value="2020-12-31T00:00:00" />
</ns3:obs>
</ns2:cube>
<ns2:cube id="ID_CODE">
<ns3:obs>
<ns3:dim name="CODE" value="ABC2" />
<ns3:dim name="VAR" value="VAR2"/>
<ns3:dim name="VALUE" value="N"/>
<ns3:dim name="DATE" value="2020-12-31T00:00:00" />
</ns3:obs>
</ns2:cube>
</ns2:dataSegment>
</ns2:content>
</ns2:message>
Result when using proc xls with Parfait's script.xls:
<?xml version="1.0" encoding="UTF-8"?><ns2:message xmlns:ns2="http://www.someurl/common/vo/message"><ns2:header>
<ns2:ID>1234</ns2:ID>
<ns2:time>2021-01-19T09:39:44</ns2:time>
</ns2:header></ns2:message>
data _null_
step(s). I am really new into XML. – Kermit