10-20-2017 02:11 PM
I've tried the XML Map (XMLV2) but sadly don't have the XML mapper installed.
What I've tried:
filename sample "OPEN_DATA_1.xml"; filename mapfile "test.map"; libname sample xmlv2 xmlmap=mapfile automap=replace; proc copy in=sample out=work; run;
And the log:
1200 filename sample "OPEN_DATA_1.xml";
1201 filename mapfile "test.map";
1202 libname sample xmlv2 xmlmap=mapfile automap=replace;
ERROR: The creation of the XML Mapper file failed.
ERROR: Error in the LIBNAME statement.
1204 proc copy in=sample out=work;
ERROR: Libref SAMPLE is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: Parsing with high validation.
ERROR: Unable to parse the specified file:
Exception class: java.lang.OutOfMemoryError
NOTE: The SAS System stopped processing this step because of
Any help would be very much appreciated in reading this file. I've attached the file as well but it can be downloaded from here:
I'd be fine with scraping the API as well, which returns JSON.
Sadly I'm on SAS 9.4M3 which doesn't have the JSON lib yet.
Note that I've changed the extension from XML to TXT and zipped it to allow for the upload to occur.
10-21-2017 07:43 AM
After a week (years really) of bloody battle with the XML libname, i am bailing with trying to get it to read/write complex XML.
I would suggest using an alternate tool for the XML/JSON such as C# (my choice)/PowerShell/Python. Spend time getting the data formed then bring it into SAS.
Your alternate is to parse it. Poor alternative when there are engines that can handle it.
10-21-2017 12:28 PM
I can't use SAS UE because it's for work
@AlanC I did parse it manually, it took two hours AFTER I spend about 4 fighting with R/SAS and getting this file in...I think I'll try Python instead now. I still want it automated because I have to read about 39 of these every week....
@Ksharp The JSON is via an API, here's the call:
It will return a JSON file.
10-21-2017 12:40 PM
10-21-2017 11:47 PM
I wrote a C# program that parses all of these XML files and turns them into tab-delimited files with separate files per XML file. I have zipped it up and put it here. Be patient if you run it because the XML is very layered so the parse takes time:
The exe takes 2 args at the command-line:
The source directory where the XML is located and the ouput location for the unwound files. here is an example:
You will find the exe in the bin directory under debug.
To provide a sense, it took me around 20 mins to code this. I would suggest if you want further control, you load the data into an XDocument and then XPath what you need out. I am happy to help you do the coding if desired. I will be onsite this week so will have limited time but will respond in the evening if this is the direction you wish to go. Personally, I would suggest writing these to a SQL Express database instead of delimited but delimited illustrates the issue.
Good luck. If you need help on the JSON side, ping me.
10-22-2017 08:39 PM
@AlanC wow! Thanks, so much! I'll have to look at this tomorrow and see what I can run on my desktop, looking forward to it and appreciate your help a ton!
10-23-2017 07:52 AM