topic Re: Import zipped XML files in SAS Programming

Import zipped XML files

athomson — Wed, 04 Jan 2017 21:30:26 GMT

All,

I get multiple zipped folders that contain XML files. I want to "point" SAS into the zipped folder and parse out the XML files into records in a SAS dataset.

Here's a diagram of what I'm dealing with:

data.zip
  |__sub_folder
        |__ file1.xml
        |__ file2.xml
        ...
        |__file500.xml

I know I can use a FILENAME with the ZIP option then read in the contents of a zipped folder:

filename inzip ZIP "C:\input_data\data.zip";

data folder_contents;
 length memname $200 isFolder 8;
 fid=dopen("inzip");
 if fid=0 then
  stop;
 memcount=dnum(fid);
 do i=1 to memcount;
	/*Scans and gives only the name of the xml file*/
  	memname=scan(dread(fid,i), 2, '\');
  	/*check for trailing / in folder name */
  	output;
 end;
 rc=dclose(fid);
run;

title "Files in the ZIP file";
proc print data=folder_contents noobs N;
run;

And I know I can use the LIBNAME engine with the XML option (in this case XMLV2) with an XML map (made from the XML Mapper) to read in XML files into a dataset:

filename SXLEMAP "C:\xml_map\my_map2.map";
libname my_xml_file XMLV2 "C:\XML_files\test.xml" xmlmap = SXLEMAP;
libname out "C:\output";

data out.xml_contents;
    set my_xml_file.test;
run;

How do I get a LIBNAME pointing "inside" a zipped folder, if I have to use the FILENAME engine to look inside said folder?

Right now, I believe we can only use the ZIP options in the FILENAME engine, not the LIBNAME.

I want to avoid unzipping since it takes a while to unzip all of the XML files (and IT at my agency has strong restrictions on using X commands. Otherwise, I'd use X commands to move the XML files from the zipped folder to a staging folder, then use a macro to import the XML files).

Re: Import zipped XML files

ChrisHemedinger — Wed, 04 Jan 2017 22:39:58 GMT

You're almost there. You just need to add a middle step in which you use DATA step to read the XML file out of the zip archive and then write it to a temp space in your session. Then you can use LIBNAME XML2 to read the XML as data.

I have a similar example with an Excel file in this blog post.

Re: Import zipped XML files

athomson — Thu, 05 Jan 2017 17:36:55 GMT

Got it!

Here is what I got:

filename inzip ZIP "C:\input_data\data.zip";

data folder_contents;
 length memname $200 isFolder 8;
 fid=dopen("inzip");
 if fid=0 then
  stop;
 memcount=dnum(fid);
 do i=1 to memcount;
	/*Scans and gives only the name of the xml file*/
  	memname=scan(dread(fid,i), 2, '\');
  	output;
 end;
 rc=dclose(fid);
run;

/* create a report of the ZIP contents */
title "Files in the ZIP file";
proc print data=folder_contents noobs N;
run;

/* identify a temp folder in the WORK directory */
filename xl "%sysfunc(getoption(work))/file1.xml" ;
 
/* hat tip: "data _null_" on SAS-L */
data _null_;
   /* using member syntax here */
   infile inzip(sub_folder\file1.xml) 
       	lrecl=256 
		recfm=F 
		length=length 
		eof=eof unbuf;
   file   xl lrecl=256 recfm=N;
   input;
   put _infile_ $varying256. length;
   return;
 eof:
   stop;
run;

filename SXLEMAP "C:\xml_map\my_map2.map';
libname my_xml XMLV2 "%sysfunc(getoption(work))/file1.xml" xmlmap=sxlemap access=readonly;
libname out "C:\Output";

data out.xml_contents;
	set my_xml.test;
run;

It's the intermediate data _null_ step I'm a little shaky on. So data step basically tells SAS to pluck the xml file from inside the zipped folder and stick it in a temporary work directory? This then allows me to set a LIBNAME on that file in the temporary work directory and leverage the XML engine to parse out the XML?

Re: Import zipped XML files

ChrisHemedinger — Thu, 05 Jan 2017 18:06:37 GMT

@athomson - Exactly! Good job!

Re: Import zipped XML files

athomson — Wed, 11 Jan 2017 03:15:40 GMT

Hi Chris (and anyone else reading this post)!

We've made some pretty good progress so far in trying to "macro-tize" the above so that it will read into multiple zipped folders and then parse out multiple XML files into records in a dataset.

See the post below to offer any thoughts how we can overcome a couple of problems, and even make the macro run more efficiently!

https://communities.sas.com/t5/General-SAS-Programming/Improving-a-macro-that-parses-out-multiple-XML-files-from/m-p/323844#M42600

Thanks!