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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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