DATA Step, Macro, Functions and more

Reading in XML files in a macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Reading in XML files in a macro

Hello Everyone,

 

I am trying to read a file folder full of XMLs.  I want my sas program to read the file names in the directory, as well as count them.  Then I want my macro to run data steps on each individual xml file after reading it in.  

 

The following code executes, but each of the sas files it creates have no data in them.  When I run the libref xml statements outside of the macro (ie, I explicitly state the file name) it works just fine.  It just won't work in the macro.  What am I doing wrong here?

 

This works all by itself:

 


filename sim 'S:\Shared Folders\TruDecision Inbound Leads\L20170515131953681608.xml';
filename map 'c:\sasdata\leadmap.map';

libname sim xmlv2 xmlmap=map;

 

data d.lead ;
set sim.lead;
run;

 

When I print 'd.lead' (above), all the info prints out perfectly.

 

When I embed that in this macro, it creates all the right files from each xml, just no data in it (empty file).  No error messages show up in log either.

 

Here's my code:

 


libname d 'c:\sasdata';

filename DIRLIST pipe 'dir "S:\Shared Folders\TruDecision Inbound Leads\L*.xml" /b ';


data dirlist ;
infile dirlist lrecl=200 truncover;
input file_name $100.;
run;

 

data _null_;
set dirlist end=end;
count+1;
call symputx('read'||put(count,4.-l),cats('S:\Shared Folders\TruDecision Inbound Leads\',file_name));
call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
if end then call symputx('max',count);
run;

 

data d.dirlist(rename=(file_name=leadfile));
set dirlist;
order=_n_;run;

 

options mprint symbolgen;

%macro readin;
%do i=1 %to &max;

 

data d.leadtarget&i;
set d.dirlist;
if &i=order;
run;

 

proc sql noprint;
select leadfile
into :lf
from d.leadtarget&i;
quit;

 

filename sim 'S:\Shared Folders\TruDecision Inbound Leads\&lf';
filename map 'c:\sasdata\leadmap.map';

libname sim xmlv2 xmlmap=map;

 

data d.lead;
set sim.lead;
run;


%end;
%mend readin;

%readin;


Accepted Solutions
Solution
‎05-16-2017 02:06 PM
Occasional Contributor
Posts: 7

Re: Reading in XML files in a macro

Solution!

 

As I suspected originally, my sas code that I posted was a thing of beauty and worked perfectly all along to mass import xmls (with the exception that I shoudl have used double quotes in order to get the macro reference to work in the filename statement for xml.

 

My problem all along was that I was using an FTP sharefile.  I have a drive mapper that lets me map right to it, but for some reason the security settings won't allow sas to work properly or some other nuance of sas working with xmls remotely.  Not sure, but at any rate I resolved the problem without changing my code.

 

I use proc datasets to bring the xml's over to a local folder, run sas in the local folder, and use proc datasets to deploy my resulting files.  If anyone wants to mass import xml files into a series of sas datasets, this works like a charm.

 

Thank you to those who took the time to help me.  This is a very valuable community!

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Reading in XML files in a macro

May I suggest a redesign?

 

  1. Take your working code and wrap that into a macro with three parameters:
    1. Input XML file
    2. XML map file
    3. name of output dataset
  2. Create a list of files in the folder - many options for this, search on here or see the macro appendix for a macro solution. 
  3. Use CALL EXECUTE to pass the data from #2 to #1

 

This avoids creation of many macro variables and no macro loops and it's easier to understand IMO.

 

There's an example of that here, except I use PROC IMPORT and you'll want to change that section to be a libname import for your XML data.

 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

 

%macro list_files(dir,ext);
	%local filrf rc did memcnt name i;
	%let rc=%sysfunc(filename(filrf,&dir));
	%let did=%sysfunc(dopen(&filrf));

	%if &did eq 0 %then
		%do;
			%put Directory &dir cannot be open or does not exist;

			%return;
		%end;

	%do i = 1 %to %sysfunc(dnum(&did));
		%let name=%qsysfunc(dread(&did,&i));

		%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then
			%do;
				%put &dir\&name;
				%let file_name =  %qscan(&name,1,.);
				%put &file_name;

				data _tmp;
					length dir $512 name $100;
					dir=symget("dir");
					name=symget("name");
					path = catx('\',dir,name);
					the_name = substr(name,1,find(name,'.')-1);
				run;

				proc append base=list data=_tmp force;
				run;

				quit;

				proc sql;
					drop table _tmp;
				quit;

			%end;
		%else %if %qscan(&name,2,.) = %then
			%do;
				%list_files(&dir\&name,&ext)
			%end;
	%end;

	%let rc=%sysfunc(dclose(&did));
	%let rc=%sysfunc(filename(filrf));
%mend list_files;

%macro import_file(path, file_name, dataset_name );

	proc import 
		datafile="&path.\&file_name."
		dbms=xlsx
		out=&dataset_name replace;
	run;

%mend;

%list_files(c:\_localData\temp, xlsx);

data _null_;
	set list;
	string = catt('%import_file(', dir, ', ',  name,', ', catt('test', put(_n_, z2.)), ');');
	call execute (string);
run;

 

Occasional Contributor
Posts: 7

Re: Reading in XML files in a macro

I'm still struggling with this.  In my original code, why did SAS return no records in the file when outside the macro it worked fine?

 

 

Super User
Posts: 19,815

Re: Reading in XML files in a macro

1. Your code will overwrite the results each time. You do not have a mechanism for uniquely identifying each file extracted.

2. Run your macro with MPRINT and SYMBOLGEN options on and post the log. It will indicate why you haeve no records. 

3. I can't decipher all your code but I noticed that you created the macro variable with the full path but then at the end only use the last part? I'm guessing that doesn't work as expected.

 

If you want to continue with this style of a solution then you need to incorporate something to deal with #1, and you can use #2 to help debug  the path. Pay attention to what the macro variables are resolving to directly. 

Solution
‎05-16-2017 02:06 PM
Occasional Contributor
Posts: 7

Re: Reading in XML files in a macro

Solution!

 

As I suspected originally, my sas code that I posted was a thing of beauty and worked perfectly all along to mass import xmls (with the exception that I shoudl have used double quotes in order to get the macro reference to work in the filename statement for xml.

 

My problem all along was that I was using an FTP sharefile.  I have a drive mapper that lets me map right to it, but for some reason the security settings won't allow sas to work properly or some other nuance of sas working with xmls remotely.  Not sure, but at any rate I resolved the problem without changing my code.

 

I use proc datasets to bring the xml's over to a local folder, run sas in the local folder, and use proc datasets to deploy my resulting files.  If anyone wants to mass import xml files into a series of sas datasets, this works like a charm.

 

Thank you to those who took the time to help me.  This is a very valuable community!

Contributor
Posts: 20

Re: Reading in XML files in a macro

Hi Dapstat,

I'm trying to clon your code and I get 0 observations in the lead table. I can't find the solution, I've tried to change single to doble quotes but no result. Can you please help me? It is my first time dealing with xml files and I have hundreds to import. Thank you very much in advance. I attach the log, no errors either.

 

filename  SXLELIB '\\tsclient\C\Transferencia de Archivos\IMPORT TO SAS\sgfxml\02303_20171016070200.xml';
filename  SXLEMAP '\\tsclient\C\Transferencia de Archivos\IMPORT TO SAS\SGFXML\siammapV2.map';
libname   SXLELIB xmlv2 xmlmap=SXLEMAP access=READONLY;
 

DATA alerta; SET SXLELIB.alerta; run;



libname siamia 'C:\Users\SAS\Desktop\siamia';
filename DIRLIST pipe 'dir "\\tsclient\C\Transferencia de Archivos\IMPORT TO SAS\sgfxml\0*.xml" /b';

data dirlist ;
infile dirlist lrecl=200 truncover;
input file_name $100.;
run;
 
data _null_;
set dirlist end=end;
count+1;
call symputx('read'||put(count,4.-l),cats("\\tsclient\C\Transferencia de Archivos\IMPORT TO SAS\SIAM\",file_name));
call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
if end then call symputx('max',count);
run;



data siamia.dirlist(rename=(file_name=leadfile));
set dirlist;
order=_n_;run;
 
options mprint symbolgen;
%macro readin;
%do i=1 %to &max;
 
data siamia.leadtarget&i;
set siamia.dirlist;
if &i=order;
run;
 
proc sql noprint;
select leadfile
into :lf
from siamia.leadtarget&i;
quit;
 
filename sgfxml "C:\Users\SAS\Desktop\siamia\&lf";
filename map "\\tsclient\C\Transferencia de Archivos\IMPORT TO SAS\SGFXML\siammapV2.map";
libname sgfxml xmlv2 xmlmap=map;
 
data siamia.lead;
set siamia.lead;
run;

%end;
%mend readin;
%readin;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 191 views
  • 0 likes
  • 3 in conversation