topic Re: Reading Multiple XML files into SAS in SAS Programming

Reading Multiple XML files into SAS

msalam92 — Wed, 16 Jan 2019 21:43:38 GMT

I have hundreds of XML files (all with same structure) that I want to read/write in SAS.

What is the most efficient way? I was thinking of adding in loops that iterates through the number of XML files and stops once all files are read. Can this be done?

Your feedback will be greatly appreciated.
Thank You.

Re: Reading Multiple XML files into SAS

PeterClemmensen — Wed, 16 Jan 2019 21:46:45 GMT

Do you want to read the files into separate SAS data sets or into a single data set since they all have the same structure?

Re: Reading Multiple XML files into SAS

msalam92 — Wed, 16 Jan 2019 21:51:02 GMT

I want them all to be read into separate SAS datasets.

Re: Reading Multiple XML files into SAS

Reeza — Wed, 16 Jan 2019 21:52:48 GMT

Then it's a three step process.
1. Get a list of all files.
2.Write a program to read one file
3. Wrap it in a macro to read all files

If you show how you're reading one file, generalzing the other two is relatively easy. Do you know how to read one file?

Re: Reading Multiple XML files into SAS

msalam92 — Wed, 16 Jan 2019 21:59:31 GMT

I do not, also where will the macros need to be created?

Apologies for the ignorance... SAS is brand new to me.
Thank You in advance.

Re: Reading Multiple XML files into SAS

Reeza — Wed, 16 Jan 2019 22:07:37 GMT

Maybe start there then, figure out how to read the file first. Then worry about how to do it for more than one. Macro's are programs, so you can store it wherever you're storing other programs.

Re: Reading Multiple XML files into SAS

msalam92 — Wed, 16 Jan 2019 22:22:23 GMT

Ok, I was able to code for one XML and get it to read successfully.

What is the next step? I am having a little tough time understanding. Am I just going to run the same code/ change file path 100 times?

Please suggest on how to move forward.

Re: Reading Multiple XML files into SAS

Reeza — Wed, 16 Jan 2019 22:27:09 GMT

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Re: Reading Multiple XML files into SAS

Reeza — Wed, 16 Jan 2019 22:27:30 GMT

Example of importing all Excel Files at once:
https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

Re: Reading Multiple XML files into SAS

Cynthia_sas — Wed, 16 Jan 2019 22:31:18 GMT

Hi:
Here is a paper that explains the basics of Macro Processing: https://support.sas.com/resources/papers/proceedings13/120-2013.pdf. It does not show using a Macro %DO loop, but once you get the basics out of the way, you can find examples on the forums of using CALL EXECUTE or a %DO loop to repeat a macro invocation multiple times.
Cynthia

Re: Reading Multiple XML files into SAS

msalam92 — Wed, 16 Jan 2019 22:33:07 GMT

But the data is XML, not excel. Will the macro be created after the XML is generated into excel file?

Thanks for sharing Reeza.

Re: Reading Multiple XML files into SAS

Reeza — Wed, 16 Jan 2019 22:36:19 GMT

THose are examples of what you're trying to do, not the solution to your question. You can use it as an example of how to build your processes. Try walking through the tutorial, but if you have no SAS experience it may be kinda hard for you.

UCLA also has a good SAS Tutorial on macros.

Re: Reading Multiple XML files into SAS

msalam92 — Thu, 17 Jan 2019 16:19:00 GMT

Here is the code I used to read one xml file.

Any suggestions on how to continue adding other XMLs?

A loop?

filename test1 url '.xml file path';
libname test1 XML xml;

proc copy in=test out=work;
run;

proc print;
run

Re: Reading Multiple XML files into SAS

Reeza — Thu, 17 Jan 2019 16:27:29 GMT

Ok, next step, do you have a list of all the files you need to read in to a data set? Is there a systematic naming system to your xml files?

Re: Reading Multiple XML files into SAS

msalam92 — Thu, 17 Jan 2019 16:33:27 GMT

Yes I have all the files.

All the xml files are systematic. The URLs only change slightly based off the issue type.

Ex: http://www.mysite.com/xml/testxml.xml

Re: Reading Multiple XML files into SAS

Reeza — Thu, 17 Jan 2019 16:45:24 GMT

So what's the pattern, it's something that can easily be coded? Or you have the file names in a data step? If so, then you can look at that macro tutorial I included in my first answer and you can convert that program above to a macro and import all the files using call execute.

Here's another good reference.
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

If you're having issues, post the full code you're using and the log to help with debugging.

When programming macros, I also recommend turning on the debug options:

options mprint symbolgen;