BookmarkSubscribeRSS Feed
Pyrite | Level 9

Can SAS join my tables automatically after I designate a large XML file as a SAS library?

If I would have only 2 or 3 tables in this XML file, I would just join them by hand and not ask this question, but I have 200 tables.

I'm hoping to find an automatic joining option because:

  1. SAS provides the *_ORDINAL fields in every table when mapping an XML file to a SAS library (this is very useful for joing the tables manually, but I'm looking for an automatic option)
  2. If SAS provides the *_ORDINAL fields, then it knows how the tables fit together
  3. It took me a few hour to write a query to join 49 of the 200 tables (easy but tedious)
  4. Microsoft Excel joins all 200 tables into one dataset automatically

I tried a natural join in PROC SQL, but that didn't work on more than 2 tables.

Any other options? PROC INFOMAP? ODS?


Rhodochrosite | Level 12

I'm not sure about "automatic" ways, but I have seen code automating such endeavors which deal with great numbers of datasets.  Perhaps code in this post can help you: How to append all dataset in one library.

Pyrite | Level 9

PhilC, thank you for this suggestion.

That thread #245030 talks about vertical stacking -- an interesting and efficient way to stack 100 datasets vertically.

Unfortunately, I need merging -- side-by-side merging on key fields -- not vertical stacking

... and my variables are different in most tables.

Super User

What does your code look like?

Pyrite | Level 9


This is what my code looks like

I used the automap option (but I tried the XML Mapper too... maybe there's an option in the XML mapper that I missed)

filename  XML_file 'C:\folder1\xml_file_1.xml';

filename  XML_MAP  'C:\folder1\';

libname   XML_file xmlv2 automap=replace xmlmap=XML_MAP access=READONLY;

proc sql; create table Test1 as

   select    DISTINCT              /* distinct is probably optional */

             t1.Header_ORDINAL          as Header_ORDINAL

            ,t1.QueryId                 as Header_QueryId         

            ,t4.response_ORDINAL        as Response_ORDINAL

            ,t1.Status                  as Header_Status          

           ,t1.TransactionId           as Header_TransactionId   

           ,t2.Exceptions_ORDINAL      as Exceptions_ORDINAL

                /* . . . many fields . . . */

   from      XML_file.Header        t1

   left join XML_file.Exceptions    t2   on t1.Header_ORDINAL      = t2.Header_ORDINAL

   left join XML_file.Item          t3   on t2.Exceptions_ORDINAL  = t3.Exceptions_ORDINAL

   left join XML_file.response      t4   on t1.response_ORDINAL    = t4.response_ORDINAL

    /* . . . many tables . . . */


If I look at the contents of all the datasets in the XML_file, I can see which Ordinals go with other Ordinals in other tables... so I can do this by hand... hoping for an automatic option.

Also, I don't really know if Excel does it correctly; I just know it does it.

Lapis Lazuli | Level 10

This can be done using PROC TEMPLATE.


You can write conditional codes to get one table xml mapper for the xml DS.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4 in conversation