Importing multiple XML files (hundreds at a time)

[fordcr2]

I know there are some posts about this but I have tried everything and nothing is working so I am getting pretty desperate. I am trying to import all XML files into one SAS dataset. I do not have much experience with this, and I am just starting to get comfortable with macros, so I might need a little extra help on how to tweak code. So far I have been able to successfully import one XML file using this code:

filename datafile 'h:\Practice\PARI\test-20181002-170338-230.xml';
filename mapfile 'h:\Practice\PARI\XML\test-20181002-170338-230.map';
libname datafile xmlv2 xmlmap=mapfile automap=replace;

proc copy in=datafile out=work; run;

This one XML file creates 7 SAS datasets in the work library. I will need to import hundreds of these files, as they come from a medical device company. Then I will need to put all of this information into one working dataset. Does anyone have any idea on how to import all of these files? Also in a way that won't leave me with hundreds of datasets in the work library?

[RW9]

Something like:

%macro imp_xml (fname=,mapname=);

  /* This is your original code, I have just replaced the file and map name as parameters */
  filename datafile 'h:\Practice\PARI\&fname.';
  filename mapfile 'h:\Practice\PARI\XML\&mapname.';
  libname datafile xmlv2 xmlmap=mapfile automap=replace;

  proc copy in=datafile out=work; 
  run;
  
  filename datafile clear;
  filename mapfile clear;
  libname datafile clear;

%mend imp_xml;
 
/* Clean work so that we can append all datasets created */
proc datasets lib=work memtype=data kill nolist;
run;

/* This is a pipe from your Operating system, it is the same as doing a directory listing
   from the command prompt.  It will feedback all the filenames with the extension xml
   in the folder given */
filename inlist pipe 'dir "h:/Practice/PARI/*.xml" /b';

/* I replace the _null_ part here, you can look at dirlist dataset in work to see what is read
   in from the directory listing */
data dirlist;
  length buff mfile $200;
  infile inlist;
  input buff $;
  mfile=tranwrd(lowcase(buff),".xml",".map");
/* The next step creates one macro call for every filename returned by the directory listing
   from the command prompt */
  call execute(cats('%macro imp_xml (fname=',buff,',
                                     mapname=',mfile,');'));
run;

filename inlist clear;

/* Combine all the datasets in work */
data _null_;
  set sashelp.vtable (where=(libname="WORK"));
/* These next steps will generate firstly one dataset called want, then from there on in
   one proc append for every dataset in the work library */
  if _n_=1 then call execute(cats('data want; set ',strip(memname),';run;'));
  else call execute(cats('proc append base=want append=',strip(memname),' force;run;'));
run;

/* Remove all files except the final one */
proc datasets lib=work memtype=data kill nolist;
  save want;
run;

Note not tested!

[fordcr2]

Hi, 

Thank you for the quick response. This is what I got from the log:

256
257 %macro imp_xml (fname=,mapname=);
258
259 filename datafile 'h:\Practice\PARI\&fname.';
260 filename mapfile 'h:\Practice\PARI\XML\&mapname.';
261 libname datafile xmlv2 xmlmap=mapfile automap=replace;
262
263 proc copy in=datafile out=work;
264 run;
265
266 filename datafile clear;
267 filename mapfile clear;
268 libname datafile clear;
269
270 %mend imp_xml;
271
272 /* Clean work so that we can append all datasets created */
273 proc datasets libname=work memtype=data kill nolist;
-------
1
WARNING 1-322: Assuming the symbol LIB was misspelled as libname.

NOTE: Deleting WORK.BASE_DATA (memtype=DATA).
NOTE: Deleting WORK.DRUG_NAME (memtype=DATA).
NOTE: Deleting WORK.DRUG_NAMES (memtype=DATA).
NOTE: Deleting WORK.FILESINFOLDER (memtype=DATA).
NOTE: Deleting WORK.INHALATIONDATA (memtype=DATA).
NOTE: Deleting WORK.INHALATION_DATA (memtype=DATA).
NOTE: Deleting WORK.MT_STUDYINHALATIONDATA01 (memtype=DATA).
274 run;

275
276 filename inlist pipe 'dir "h:/PracticePARI/*.xml" /b';
277

NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.06 seconds
cpu time 0.04 seconds

278 data _null_;
279 length buff mfile $200;
280 infile inlist;
281 input buff $;
282 mfile=tranwrd(lowcase(buff),".xml",".map");
283 call execute(cats('%macro imp_xml (fname=',buff,',
284 mapname=',mfile,');'));
285 run;

NOTE: The infile INLIST is:
Unnamed Pipe Access Device,
PROCESS=dir "h:/PracticePARI/*.xml" /b,RECFM=V,
LRECL=32767

Stderr output:
File Not Found
NOTE: 0 records were read from the infile INLIST.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

286
287 filename inlist clear;
NOTE: Fileref INLIST has been deassigned.
288
289 /* Combine all the datasets in work */
290 data _null_;
291 set sashelp.vtable (where=(libname="WORK"));
292 if _n_=1 then call execute('data want; set ',strip(memname),';run;');
-------
253
293 else call execute('proc append base=want append=',strip(memname),' force;run;');
-------
253
ERROR 253-185: The EXECUTE subroutine call has too many arguments.

294 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.00 seconds

295
296 /* Remove all files except the final one */
297 proc datasets lib=work memtype=data kill nolist;
298 save want;
299 run;

ERROR: The file WORK.WANT (memtype=DATA) was not found, but appears on a SAVE statement.
NOTE: Statements not processed because of errors noted above.

[RW9]

I have updated the code originally provided to cover these errors.  Do note I am not going to do this for you, you need to do some debugging, learning what that code does and adapting it to your needs.

[fordcr2]

That didn't work either... I don't expect you to write it for me but I do not understand what you are doing in this code and therefore I don't know how to start with the debugging. 

The code isn't reading in any observations at all and I don't know why that is...

[RW9]

I have added some comments to the code.  Open a command prompt (its on windows start or just search for com), type in the pipe command:

dir "h:/Practice/PARI/*.xml"

 The output from that is fed back to your dataset for processing, it should list all the XML files in the directory:

h:/Practice/PARI/

That is the directory you provided.

[fordcr2]

Thank you for your comments. So that directory is where all of the XML files are stored, but for whatever reason it is still reading in 0 observations: 

NOTE: The infile INLIST is:
Unnamed Pipe Access Device,
PROCESS=dir "h:/Practice/PARI/*.xml" /b,
RECFM=V,LRECL=32767

Stderr output:
File Not Found
NOTE: 0 records were read from the infile INLIST.
NOTE: The data set WORK.DIRLIST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.01 seconds

258
259 filename inlist clear;
NOTE: Fileref INLIST has been deassigned.

[RW9]

And per the previous post, what does it show when you type the dir command at the command prompt?

[fordcr2]

Microsoft Windows [Version 10.0.17134.523]
(c) 2018 Microsoft Corporation. All rights reserved.

H:\>h:/Practice/PARI/
'h:/Practice/PARI/' is not recognized as an internal or external command,
operable program or batch file.

H:\>

[RW9]

The line you should enter at the command prompt is exactly the same as it is run:

dir "h:/Practice/PARI/*.xml"

[fordcr2]

Ok. When I did that it said:

Directory of h:\Practice\PARI

File Not Found

[RW9]

So what does that tell you?  It tells me, that in the directory h:\Practice\PARI, there are no files with the extension .xml.  Please supply the real path so that the process can find your xml files.

[fordcr2]

That is the folder in which all of the XML files are held though? I specifically made the folder on my H drive in a sub folder practice then another folder called PARI which I dragged and dropped all of the xml files to. 

[RW9]

Are you using SAS University Edition?  If so then please follow the guidance for how to get files onto the virtual image.  You cannot access local files directly as a mapped drive, you have to copy them to myfolders.

If not, please state what software you are using, and show a picture of the explorer folder containing the files in question, showing the exact path.

[fordcr2]

I am using SAS 9.4