XML - Join Tables Automatically

[agoldma]

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?

Thanks

[PhilC]

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.

[agoldma]

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.

[Reeza]

What does your code look like?

[agoldma]

Reeza,

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\XML_MAP1.map';

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 . . . */

;quit;

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.

[Satish_Parida]

This can be done using PROC TEMPLATE.

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