BookmarkSubscribeRSS Feed
agoldma
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?

Thanks

5 REPLIES 5
PhilC
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.

agoldma
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.

Reeza
Super User

What does your code look like?

agoldma
Pyrite | Level 9

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1804 views
  • 0 likes
  • 4 in conversation