File Modified Date in Bulk Import

[dmutolo]

I am trying to get the name and modified date of each file when doing a bulk CSV import, but I am only getting the correct date for the first file in the directory.  All of the other files are shown to have the same modified date and name as the first file in the directory alphabetically.  (I can get the correct file names using the DREAD system function, so I am primarily concerned with the date).

I have modified the bulk CSV import from the SAS example code here to use the FINFO system function to get the file info within the do loop and put the values to the log.   I can see in the log that the file ID passed to the FINFO function changes with each file processed, but the output of the FINFO function is the same for each iteration through the loop.

%macro drive(dir,ext); 

   %local cnt filrf rc did memcnt name fid ModifyDT rc1;
   %let cnt=0;
   %let filrf=mydir;

   %let rc=%sysfunc(filename(filrf,&dir));
   %let did=%sysfunc(dopen(&filrf));


   %if &did ne 0 %then %do;
      %let memcnt=%sysfunc(dnum(&did));
      %do i=1 %to &memcnt;
	  	 
	  	 %let scanfilename=%qsysfunc(dread(&did,&i));	 
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let cnt=%eval(&cnt+1);
               %put %qsysfunc(dread(&did,&i));

			   /* File Info */
			   %let scanfilepath = &dir\%qsysfunc(dread(&did,&i));
		 	   %let rc1=%qsysfunc(filename(onefile,&scanfilepath)); 
			   %let fid=%qsysfunc(fopen(&onefile)); 
			   %let ModifyDT=%qsysfunc(finfo(&fid,Last Modified));
			   %let File_Name=%qsysfunc(finfo(&fid,Filename));
			   %put NOTE: FID: &fid;
			   %put NOTE: Path: &scanfilepath;
			   %put NOTE: Name: &File_Name;
			   %put NOTE: Date: &ModifyDT;
			   %put NOTE: &rc1;		   
			
            %end;
         %end;
       %end;	
    %end;
   
%else %put &dir cannot be opened.;
  %let rc=%sysfunc(dclose(&did));
%mend drive;

%drive(D:\TestFiles,csv)

From the Log:

File (1).csv
NOTE: FID: 130
NOTE: Path: D:\TestFiles\File (1).csv
NOTE: Name: D:\TestFiles\File (1).csv
NOTE: Date: 14Aug2024:15:50:57
NOTE: 0

File (2).csv
NOTE: FID: 131
NOTE: Path: D:\TestFiles\File (2).csv
NOTE: Name: D:\TestFiles\File (1).csv
NOTE: Date: 14Aug2024:15:50:57
NOTE: 20036

File (3).csv
NOTE: FID: 132
NOTE: Path: D:\TestFiles\File (3).csv
NOTE: Name: D:\TestFiles\File (1).csv
NOTE: Date: 14Aug2024:15:50:57
NOTE: 20036

File (4).csv
NOTE: FID: 133
NOTE: Path: D:\TestFiles\File (4).csv
NOTE: Name: D:\TestFiles\File (1).csv
NOTE: Date: 14Aug2024:15:50:57
NOTE: 20036

[ballardw · Accepted solution]

I think that you need to FCLOSE the file.

%macro drive(dir,ext); 

   %local cnt filrf rc did memcnt name fid ModifyDT rc1;
   %let cnt=0;
   %let filrf=mydir;

   %let rc=%sysfunc(filename(filrf,&dir));
   %let did=%sysfunc(dopen(&filrf));


   %if &did ne 0 %then %do;
      %let memcnt=%sysfunc(dnum(&did));
      %do i=1 %to &memcnt;
	  	 
	  	 %let scanfilename=%qsysfunc(dread(&did,&i));	 
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let cnt=%eval(&cnt+1);
               %put %qsysfunc(dread(&did,&i));

			   /* File Info */
			   %let scanfilepath = &dir\%qsysfunc(dread(&did,&i));
		 	   %let rc1=%qsysfunc(filename(onefile,&scanfilepath)); 
			   %let fid=%qsysfunc(fopen(&onefile)); 
			   %let ModifyDT=%qsysfunc(finfo(&fid,Last Modified));
			   %let File_Name=%qsysfunc(finfo(&fid,Filename));
			   %put NOTE: FID: &fid;
			   %put NOTE: Path: &scanfilepath;
			   %put NOTE: Name: &File_Name;
			   %put NOTE: Date: &ModifyDT;
			   %put NOTE: &rc1;		   
			   
            %end;
            %let rc2=%sysfunc(fclose(&fid.));
         %end;
       %end;	
    %end;
   
%else %put &dir cannot be opened.;
  %let rc=%sysfunc(dclose(&did));
%mend drive;

Which does report different dates on one of my folders full of CSV files.

[dmutolo]

That did the trick.  Much appreciated!

[Kurt_Bremser]

Make it a habit to always "clean up" after your code. So if you use FOPEN, also use FCLOSE if FOPEN returned a non-zero value.

Particularly when using FOPEN in macro code, the file handles will pile up, and with a large number of files you can easily exceed the OS limit for open handles per process.

When using FOPEN in a DATA step, the handles will be automatically closed when the step terminates, but you might still run out of resources within the step.

The same is true, of course, for OPEN and DOPEN, and other similar stuff.