Trying to get file properties using macros and put them in a data set?

Occasional Contributor
Posts: 17

Trying to get file properties using macros and put them in a data set?

The goal of this macro was to take a file directory and file extension as parameters, and add all the files in the directory with that extension to a SAS data set to be processed later.

I found this code segment online: 40934 - Retrieve file size, create time, and last modified date of an external file

But it is only for one file. The properties are retrieved no problem in the code below, but the next iteration of the loop changes all the existing values in the data set. I'm guessing this is because SAS is only storing a reference to the actual value, which then changes. Is there anyway to force it to copy the actual value.

I'd really like this to be a macro but is that impossible?

Code (messy) - displays info in log and prints data set. Data set info incorrect for reasons mentioned above:

%global tfilename Bytes;

%macro fileAttribs(ofilename, dir);

  %let filename=&dir\&ofilename;

  %let tfilename=&ofilename;

  %local rc fid fidc;                                                                                                                  

  %local CreateDT ModifyDT;                                                                                                      

   %let rc=%sysfunc(filename(onefile,&filename));                                                                                      

   %let fid=%sysfunc(fopen(&onefile));

   %let Bytes=%sysfunc(finfo(&fid,File Size (bytes)));

   %let CreateDT=%sysfunc(finfo(&fid,Create Time));                                                                                    

   %let ModifyDT=%sysfunc(finfo(&fid,Last Modified));                                                                                  

   %let fidc=%sysfunc(fclose(&fid));                                                                                                   

   %let rc=%sysfunc(filename(onefile));                                                                                                

    %put NOTE: File size of &ofilename is &Bytes bytes;                                                                                 

    %put NOTE: Created &CreateDT;                

    %put NOTE: Last modified &ModifyDT;

%mend fileAttribs;                                                                                                                     

%macro drive(dir,ext);

  %let filrf=mydir;

  /* Assigns the fileref of mydir to the directory and opens the directory */

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

  %let did=%sysfunc(dopen(&filrf));

  /* Returns the number of members in the directory */

  %let memcnt=%sysfunc(dnum(&did));

  /* Loops through entire directory */

  data filespec;   /* Create data set to capture file name and size */

  length ffname$ 30 ffsize$ 10;

  %do i = 1 %to &memcnt;

  /* Returns the extension from each file */

  %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

  /* Checks to see if file contains an extension */

  %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then


  /* Checks to see if the extension matches the parameter value */

  /* If condition is true prints the full name to the log */

  %if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or

  (%superq(ext) = and %superq(name) ne) %then


  %fileAttribs(%qsysfunc(dread(&did,&i)), &dir)







  run;   /* Close local dataset filespec */

  /* Closes the directory */

  %let rc=%sysfunc(dclose(&did));

%mend drive;

%drive( <DIRECTORY>, xls )

proc print data = filespec;

title 'Created data set filespec';


Respected Advisor
Posts: 4,992

Re: Trying to get file properties using macros and put them in a data set?

You'll need to get the list of files before you start retrieving their properties.

What operating system are you working on?

What command would you give that operating system to get a list of all files in a folder?

Are you running the program in batch mode or in some sort of interactive mode?

That should be enough to get things started.

Valued Guide
Posts: 3,206

Re: Trying to get file properties using macros and put them in a data set?

Please explain why you do wat to have it as SAS-macro.

I am missing the reason for that. As it could be having learned Word-macros. Thinking SAS programming is macros?

You are describing the goal of a dataset having the filenames

The easiest way is using the filename option and wildcarding with the infile statement. SAS(R) 9.4 Companion for Windows (infile wildcars)

SAS(R) 9.4 Statements: Reference (infile filename). Having the filename you can use the datastepfunctions (no %sysfunc) delivering the additional fileinformation. 

The datastep is able to read all files and creating the SAS-dataset and the SAS-dataset containing just the filenames in one pass.

---->-- ja karman --<-----
Super User
Super User
Posts: 6,364

Re: Trying to get file properties using macros and put them in a data set?

You probably want to use

DOPEN(), DNUM() and DREAD() to get the list of filenames.

Then you could nest calls to FOPEN(), FOPTNUM() etc to get the attributes for each file.

Read the manual pages to get started.

SAS(R) 9.3 Functions and CALL Routines: Reference

Occasional Contributor
Posts: 17

Re: Trying to get file properties using macros and put them in a data set?

I was able to find macro code to do exactly what I wanted.  Here it is :

%macro getFileSizes (directory, extension, dataSet);                                                                                                                   

data &dataSet(drop=fid ff filrfb rc); 

%let bb=%sysfunc(filename(filrf,&directory));

%let did=%sysfunc(dopen(&filrf));

%let flname=;

%let memcount=%sysfunc(dnum(&did));

%if &memcount > 0 %then %do i=1 %to &memcount;

    %let flname&i=%qsysfunc(dread(&did,&i));

  %if %scan(&&flname&i,-1,.) = &extension or &extension = all %then %do;




     dte=finfo(fid,'Last Modified');  

     size=finfo(fid,'File Size (bytes)');   






%let rc=%sysfunc(dclose(&did));  



Addendum :  I wanted to use macro code because I took the SAS course 'SAS Macro Language 2' three years ago and we did some very similar programming in that course.  That course was very useful in enhancing my SAS programming skills.

Thanks to all who gave me suggestions.

Barry Walton

Super User
Super User
Posts: 6,364

Re: Trying to get file properties using macros and put them in a data set?

You seem to have mixed macro and data step code. 

All you need the macro to do is supply the values of the DIRECTORY, EXTENSION and DATASET parameters.

The rest of the logic will be clearer if you call dopen, dread, etc. functions directly in the data step code.

%macro getfilesizes(directory,extension,dataset) ;

data &dataSet ;

   length file $256 dte 8 size 8 directory $256;

   format dte datetime.;

   keep file dte size directory ;

   length filrf filrfb $8 ;





   do i=1 to memcount;

     file = dread(did,i);

     if scan(file,-1,'.') = "&extension" or "&extension" = "all" then do;



       dte=input(finfo(fid,'Last Modified'),datetime.);

       size=input(finfo(fid,'File Size (bytes)'),32.);








%mend getfilesizes;


proc print width=min;


Occasional Contributor
Posts: 8

Re: Trying to get file properties using macros and put them in a data set?

Hello Tom,


im seeing blanks in 'size' and 'dte' columns for the .xlsx files. Please guide me on this



Ask a Question
Discussion stats
  • 6 replies
  • 5 in conversation