asked on

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

Hello everyone,
In my organization we are currently in a process transferring from using ASCII files to using XML data files when working (importing and exporting) with SAS datasets.

So, my question is: what would be a proper way to export multiple ( two or more) SAS datasets (dataset1, dataset2) to one XML data file (XMLData) and at the same time to make sure that XML data file actually has those multiple SAS datasets in it as tables.

The only way that I was able to export multiple SAS datasets to one XML data file is by appending one SAS dataset to another but that's not what I want because at the end the XML data file does not have multiple tables in it - all data from multiple SAS datasets look as just one big table. Below is working code but again it only appends multiple SAS datasets and it does not create tables in the XML data file.

libname xmldir xmlv2 '/home/profile1/xml/XMLData.xml' xmltype=generic;
libname mydir '/home/profile/sas';

data xmldir.XMLData;
       set mydir.dataset1
             mydir.dataset2; 
run;

Open in new window

If you know how tackle this problem from any angle please let me know. Any help would be very appreciated!

Ian

Hi there labradorchik,

Just run multiple dataset creation steps in a session with the same libref. When you close the libref (as in

libname xmldir clear

Open in new window

the xml file associated with the library xmldir will be closed. Until then additional members created in the library will append lots of lines to the xml file for the particular table (SQL speak) or library member dataset (SAS speak).

For example

libname xmldir xmlv2 "&basepath\examples\XMLData.xml" xmltype=generic;

data xmldir.class(label="datset class from sashelp library");
       set sashelp.class(obs=4);
run;

data xmldir.cars(label="datset cars from sashelp library");
       set sashelp.cars(obs=5 keep=Make model); 
run; 

libname xmldir clear;

Open in new window

produces

<?xml version="1.0" encoding="windows-1252" ?>
<TABLE>
   <CLASS>
      <Name>Alfred</Name>
      <Sex>M</Sex>
      <Age>14</Age>
      <Height>69</Height>
      <Weight>112.5</Weight>
   </CLASS>
   <CLASS>
      <Name>Alice</Name>
      <Sex>F</Sex>
      <Age>13</Age>
      <Height>56.5</Height>
      <Weight>84</Weight>
   </CLASS>
   <CLASS>
      <Name>Barbara</Name>
      <Sex>F</Sex>
      <Age>13</Age>
      <Height>65.3</Height>
      <Weight>98</Weight>
   </CLASS>
   <CLASS>
      <Name>Carol</Name>
      <Sex>F</Sex>
      <Age>14</Age>
      <Height>62.8</Height>
      <Weight>102.5</Weight>
   </CLASS>
   <CARS>
      <Make>Acura</Make>
      <Model> MDX</Model>
   </CARS>
   <CARS>
      <Make>Acura</Make>
      <Model> RSX Type S 2dr</Model>
   </CARS>
   <CARS>
      <Make>Acura</Make>
      <Model> TSX 4dr</Model>
   </CARS>
   <CARS>
      <Make>Acura</Make>
      <Model> TL 4dr</Model>
   </CARS>
   <CARS>
      <Make>Acura</Make>
      <Model> 3.5 RL 4dr</Model>
   </CARS>
</TABLE>

Open in new window

=============

Ian

labradorchik

ASKER

Hi Ian,
Thank you for your quick response!

So, in your example two different tables are Class table and Cars table, which are under one main Table, correct?
If so, is there a way to rename this one main Table in the SAS code to, for example, Payload? So, in this example, Payload would have two tables: Class table and Cars table, which were our two SAS datasets.

I have a few other questions in regards to creation of a proper XML data file Header from SAS code as well as creating data trees in the XML data file, but I will ask those questions later in my next post...

Ian

You don't appear to exactly understand the way SAS handles the XLM file here (for xmltype=generic).

With the libname statement, you are allocating a LIBRARY which resides in the XML file. The whole SAS library in a single file! This is a similar concept to how SAS treats library engines for EXCEL and SPSS data.

When you make a new dataset in the library, the system adds a series of groups lines to the XML file. Each group exactly corresponds to one record in the dataset.

The group of XML lines corresponding to one data-set record is of the form

  <{dataset-name}>
      <{First variable name}>{first variable value}</{First variable name}>
      <{Second variable name}>{Second variable value}</{Second variable name}>
      ...
   </{dataset-name}>

Open in new window

There is NO containing dataset, The LIBRARY contains all the datasets! The one XML file is the library, with library members being consecutive groups or lines.

SAS correctly handles the situation of replacing a dataset in the library - though that may be costly in terms of CPU for large XML libraries.

-------

It is possible to specify your own XML tag system instead of using "xmltype=generic". It may get complicated - when I previously did it I spent many days tweaking the code to get the required structure.

Ian

ASKER CERTIFIED SOLUTION

Ian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

labradorchik

ASKER

Thank you very much, Ian!
Now I understood that libname statement is actually the XML data file were all datasets are placed. I had those questions because I was thinking that SAS datasets are actually appending to that one XML data file and not the library. And I will change <TABLE> to a different name in the XML data file in both places, so that's an easy fix.

Thank you for your explanations and examples!