How to properly export data from two SAS datasets to one XML data file?

2
votes

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>
1
xmlsasexportxls
What does the target XML need to look like? Are the two tables in two physically separate parts of the XML? If so you might be able to use the XML engine mapping feature to create two separate files and then stitch them together. Otherwise I would just craft your own data _null_ step(s) to write the XML and forget about the XML engine. - Tom
@Tom Thank you for the prompt answer. I edited the post with the desired XML output. I don't really understand what you mean when you say "physically separate parts" neither how to write it using data _null_ step(s). I am really new into XML. - Kermit
You won't be able to do this from two different files. You'll need to combine your data to a format that is pretty much identical to what you want to export and export that. An XML file is a text file with a specific structure. It's fairly simple to understand the basic structures and you can recreate them using a data step with PUT statements but that means creating every tag and ID manually as well so it's cumbersome for sure. - Reeza
If you're more familiar with XML I'd go for the data step solution, if you're more familiar with SAS, combining the data is likely easier. - Reeza
@Reeza How would you "combine" the data so that it matches the XML output ? Being totally new to XML, this might feels more complicated than you think. - Kermit

1 Answers

3
votes

Consider XSLT, the special purpose language designed to transform XML files, such as combining from different documents. SAS can run XSLT 1.0 even 2.0 using proc xsl.

However, you actually need two XSLT transformations since your desired output is not exactly a stack of the two XML files, namely elements in dim.xml are migrated to attributes.

XSLT 1 (save as .xsl file, a special .xml file)

<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                             xmlns:ns2="http://www.someurl/common/vo/message"
                             xmlns:ns3="http://www.someurl/common/vo/cube"> 
  <xsl:output indent="yes" encoding="UTF-8"/>
  <xsl:strip-space elements="*"/>
    
  <xsl:template match="/ns2:message">
    <ns2:message xmlns:ns2="http://www.someurl/common/vo/message" xmlns:ns3="http://www.someurl/common/vo/cube"> 
         <xsl:apply-templates select="ns2:content"/>
    </ns2:message>
  </xsl:template> 
 
   <xsl:template match="ns2:content">
    <xsl:copy> 
         <xsl:apply-templates select="ns2:dataSegment"/>
    </xsl:copy>
  </xsl:template> 
  
  <xsl:template match="ns2:dataSegment">
    <xsl:copy> 
         <xsl:attribute name="id">OBSERVATION</xsl:attribute>
         <xsl:apply-templates select="*"/>
    </xsl:copy>
  </xsl:template> 
  
  <xsl:template match="ns2:cube|ns3:obs">
     <xsl:apply-templates select="*"/>
  </xsl:template> 
  
  <xsl:template match="ns3:dim">
    <ns2:cube id="ID_CODE">
      <ns3:obs> 
         <xsl:for-each select="*">  
             <ns3:dim> 
                 <xsl:attribute name="name">
                    <xsl:value-of select="name()"/>
                 </xsl:attribute>
                 <xsl:attribute name="value">
                    <xsl:value-of select="text()"/>
                 </xsl:attribute>
            </ns3:dim>
         </xsl:for-each>
      </ns3:obs>     
     </ns2:cube>
  </xsl:template> 
  
</xsl:transform>

XSLT 2 (save as .xsl file; use absolute path reference with file:// keyword)

<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                             xmlns:ns2="http://www.someurl/common/vo/message"> 
  <xsl:output indent="yes" encoding="UTF-8"/>
  <xsl:strip-space elements="*"/>
  
  <xsl:template match="/ns2:message">
    <ns2:message xmlns:ns2="http://www.someurl/common/vo/message" xmlns:ns3="http://www.someurl/common/vo/cube"> 
         <!-- COPY CURRENT DATA -->
         <xsl:copy-of select="*"/>

         <!-- COMBINE ALL DATA FROM NEW dim.xml -->
         <xsl:copy-of select="document('file://absolute/path/to/new_dim.xml')/ns2:message/*" />
    </ns2:message>
  </xsl:template> 
  
</xsl:transform>

SAS (using macro to output multiple files)

*** OUTPUT INDIVIDUAL XML FILES;
%macro output_xmls(dset);
   filename xmlout "path\to\file\&dset..xml";
   libname xmlout xmlv2 xmltype=xmlmap xmlmap='path\to\file\XMLmap.map';

   data xmlout.&dset;
      set work.&dset;
   run;

   libname xmlout clear;
%mend output_xmls;

%output_xmls(header);
%output_xmls(dim);


*** TRANSFORM RAW dim.xml;
proc xsl 
   in  = "path\to\file\dim.xml"
   xsl = "path\to\file\script_1.xsl"
   out = "path\to\file\new_dim.xml";
run;

*** COMBINE header.xml and new_dim.xml FILES;
proc xsl 
   in  = "path\to\file\header.xml"
   xsl = "path\to\file\script_2.xsl"
   out = "path\to\file\final.xml";
run;

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:39:44</ns2:time>
   </ns2:header>
   <ns2:content>
      <ns2:dataSegment id="OBSERVATION">
         <ns2:cube id="ID_CODE">
            <ns3:obs>
               <ns3:dim name="CODE" value="ABC1"/>
               <ns3:dim name="VAR1" 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="VAR1" 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>