topic Re: XML - Join Tables Automatically in SAS Procedures

XML - Join Tables Automatically

agoldma — Mon, 17 Aug 2015 20:54:33 GMT

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:

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)
If SAS provides the *_ORDINAL fields, then it knows how the tables fit together
It took me a few hour to write a query to join 49 of the 200 tables (easy but tedious)
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

Re: XML - Join Tables Automatically

PhilC — Mon, 17 Aug 2015 21:17:31 GMT

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.

Re: XML - Join Tables Automatically

agoldma — Mon, 17 Aug 2015 21:39:30 GMT

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.

Re: XML - Join Tables Automatically

Reeza — Mon, 17 Aug 2015 21:49:04 GMT

What does your code look like?

Re: XML - Join Tables Automatically

agoldma — Mon, 17 Aug 2015 22:22:23 GMT

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.

Re: XML - Join Tables Automatically

Satish_Parida — Mon, 22 Jan 2018 07:19:50 GMT

This can be done using PROC TEMPLATE.

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