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?
Thanks
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.
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.
What does your code look like?
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.
This can be done using PROC TEMPLATE.
You can write conditional codes to get one table xml mapper for the xml DS.
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.
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.
Ready to level-up your skills? Choose your own adventure.