08-17-2015 04:54 PM
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:
I tried a natural join in PROC SQL, but that didn't work on more than 2 tables.
Any other options? PROC INFOMAP? ODS?
08-17-2015 05:17 PM
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.
08-17-2015 05:39 PM
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.
08-17-2015 06:22 PM
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 . . . */
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.