Importing XML - Store Filename as Variable?

[shl007]

Hi - I'm using the following macro to import XML files. Is there any way I can store the filename of each XML file, as a variable? There is a /* 12/30/2020 */ comment I started a bit down in the code but wasn't sure how to proceed.

%macro drive(dir,mapfile);

   /** Creates a fileref **/
   %let filerf=mydir;

   /** Assigns the fileref to the directory you
       passed in **/
   %let rc=%sysfunc(filename(filrf,&dir));

   /** Opens the directory to be read **/
   %let did=%sysfunc(dopen(&filrf));

   /** Returns the number of members in the directory that
       you passed in **/
   %let memcnt=%sysfunc(dnum(&did));

   %let cnt=0;
   %do i = 1 %to &memcnt;
      /** Return the extension of the file found **/
      %let ext=%upcase(%scan(%qsysfunc(dread(&did,&i)),2));
	  %let extlow=%LOWCASE(%scan(%qsysfunc(dread(&did,&i)),2));

      /** Return the first name of the file **/
      %let fname=%scan(%qsysfunc(dread(&did,&i)),1);

      %if &ext=XMLOUT %then %do;
         %let cnt=%eval(&cnt+1);
         /** Create a different macro variable for each file
             found **/
         %let name&cnt=&fname..&extlow;
      %end;

   %end;

   %do j = 1 %to &cnt;

/*      %if &mapfile ne %then %do;*/
         filename mapfile "&mapfile";
         libname sxle&i xmlv2 "&dir.&&name&j" xmlmap=mapfile automap=replace;
/*      %end;*/
/*      %else %do;*/
/*         libname sxle&i xmlv2 "&dir.&&name&j";*/
/*      %end;*/

      proc copy in=sxle&i out=work;
      run;

      /* Copy the files to a temporary work file */

      proc sql;
         select count(memname) into:total
         from dictionary.tables
         where libname="WORK" and memtype="DATA" ;

         select memname into:mem1-:mem%left(&total)
         from dictionary.tables
         where libname="WORK" and memtype="DATA" ;
      run;

      /* Copy the files from the temporary work file to a library */

      %do i=1 %to &total;
	  	/* 12/30/2020 SL - add filedate to dataset*/
	  	data &&mem&i;

         proc append base=templib.&&mem&i data=&&mem&i force;
         run;

	 proc datasets lib=work;
	    delete &&mem&i;
	 run;
	 quit;

      %end;

      libname sxle&i clear;
   %end;

   /** Close the directory **/
   %let rc=%sysfunc(dclose(&did));

%mend drive;

[ballardw · Accepted solution]

Are you looking for something like this where the comment is?

data &&mem&i;
   set &&mem&i;
   length xmlname $ 200;
   xmlname = "&dir.&&name&j";
end;

I really wouldn't want to use the

data &&mem&i;

   set &&mem&i;

construct but left the name so you might identify what is going on a bit better.

It is not clear whether you want the entire path with the XML file name or just the &&name&j part.

If you path is long enough you should make the length of the Xmlname variable longer to accommodate expected length (plus maybe a few more characters just in case ...)

[shl007]

Thanks! Can I ask why you wouldn't use the construct above mentioned? Just a style preference or something more?

[ballardw]

---

@shl007 wrote:
Thanks! Can I ask why you wouldn't use the construct above mentioned? Just a style preference or something more?

---

When you use the construct where the source data set has the same name as the output in a data step like that the data set is replaced unless you have a fatal error.

That means in a simple case that of program logic (not syntax) error it is possible to drop variables or modify values in a manner that you did not want. At that point you cannot recover without going several steps back and repeating the procedure to duplicate the data. This can be aggravated by macro coding where obscures things. When you have a source and output data set different then when values appear in a result you can trace them back to which step modified them. You will find some examples on this forum of people who have used that construct 4 or 5 times in the body of a  single macro and are asking "where did the bad data come from" or "where did that variable disappear.

A brief example of a suspect data step:

data result;

   set result;

   if x=2 then x=1;

   else x=2;

  /* other code*/

run;

I inherited similar code and the data that went with it.

Can you see what happens if someone, while working on the other "other code", makes a change and reruns the data step?

The specific code was supposed to match the coding of a different data set for gender. However while developing the "other code" the data step was run multiple times while testing.

Switching the gender code every single time.

If you run the data step an odd number of times then the behavior for that one variable works out. If an even number of times then the x variable is reset to the original value.

When means the model using the gender variable was completely hosed as half the records that were supposed to be male were actually female and vice versa.

There are others, but anything code that replaces values of an existing variable has potential to cause interesting results that may not be obvious and may be very difficult to determine where things go wrong.

Consider code that replaces a 1 to 10 rating scale with a collapsed to 1 to 5 replacing 1 and 2 as 1, 3 and 4 as 2 and so forth. Then rerun the data step. The first time you reduced the maximum value from 10 to 5. So the second time would map the 5 to 3, and the third time would map the 3 to 2. If this is run a fourth time all the values will be 1.

Unless the data sets are large I tend to make new sets.

And with a macro looping over lots of data sets I might be tempted to not place the clean up code inside the loop so when I get odd output I have some of those temporary data sets around to examine and verify the operations were behaving at different points.

The problem may not even be changing values. Macro code logic could create "new" variables that duplicate the names of other existing variables with lots of permutations of badness resulting. Variable drop or keep lists especially something involving one of the variable list short cut forms.