Loop through files in folder and import if not empty

Seb_A_Sanders — Mon, 20 May 2019 16:03:28 GMT

Hi,

I'm doing a looped import of a set of files in a folder. So far I have this:

%let path=\\server\Monthly;
%let tranloc = 2019-04;

filename folder "&path\&transloc\";
options validmemname=extend;
 
/* Making a list of all files in the folder */
data IL_FilesInFolder;
   length Line 8 File $300;
   List = dopen('folder');
   do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
		output;
   end;
   drop list line;
run;

DATA IL_FIF;
SET IL_FilesInFolder;
WHERE File CONTAINS 'Monthly_Transaction_Extract';
RUN;

data _null_;
     set IL_FIF end=final;
     call symput(cats('File', _N_), trim(File));
	 call symput(cats('Name', _N_), trim(nliteral(substr(File,1,LENGTH(File)-4))));
	 call symput(cats('Table', _N_), trim(nliteral(substr(File,41,10))));
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
run;

%macro inloop;
%do i = 1 %to &Total;
  proc import datafile="&path\&transloc\&&name&i...csv"
       out=WORK.&&Table&i
       dbms=csv
       replace;
	   GUESSINGROWS=5000;
  run;
%end;
%mend inloop;
%inloop

This month I had an error during the inloop section. The problem... one of the transaction extract files was empty. This is entirely possible so no problem there.

What I then considered was conditionally importing based on whether the file was empty. I figured file size was the best way to do this. I found the following:

https://blogs.sas.com/content/sasdummy/2017/09/08/filename-zip-details/

Which seemed like it might be the right starting point, but I'm struggling to adapt this to loop through my list of files, I'm guessing because this assigns the file name based on a single zip as opposed to the folder.

Any help would be appreciated, but if a different approach is better I'm happy to consider this. For reference, I have no access to the file production process so I can't step in earlier to resolve this.

Thanks

Re: Loop through files in folder and import if not empty

Tom — Mon, 20 May 2019 17:42:41 GMT

Just update your first step that is getting the filenames to also try to call the FINFO() function.

There is an example in the documentation.

*This example stores information items about an external file in a SAS data set;
data info;
   length infoname infoval $60;
   drop rc fid infonum i close;
   rc=filename('abc', 'physical-filename');
   fid=fopen('abc');
   infonum=foptnum(fid);
   do i=1 to infonum;
      infoname=foptname(fid, i);
      infoval=finfo(fid, infoname);
      output;
   end;
   close=fclose(fid);
run;

topic Re: Loop through files in folder and import if not empty in SAS Programming

Loop through files in folder and import if not empty

Re: Loop through files in folder and import if not empty