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

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

enginemane44 — Mon, 05 Aug 2013 18:58:11 GMT

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
  %do;
  /* 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
  %do;
  %fileAttribs(%qsysfunc(dread(&did,&i)), &dir)
  ffname=symget('tfilename');
  ffsize=symget('Bytes');
  output;
  %end;
  %end;
  %end;
  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';
run;

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

Astounding — Mon, 05 Aug 2013 19:36:09 GMT

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.

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

jakarman — Mon, 05 Aug 2013 19:36:53 GMT

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.

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

Tom — Mon, 05 Aug 2013 20:23:51 GMT

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

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

enginemane44 — Wed, 14 Aug 2013 19:35:21 GMT

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;

filrfb='temp';

ff=filename(filrfb,"&directory\&&flname&i");

fid=fopen(filrfb);

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

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

file=symget("flname&i");

output;

rc=fclose(fid);

%end;

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

run;

%mend;

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

Barry.Walton@millersville.edu

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

Tom — Thu, 15 Aug 2013 01:34:08 GMT

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 ;

directory="&directory";

rc1=filename(filrf,directory);

did=dopen(filrf);

memcount=dnum(did);

do i=1 to memcount;

file = dread(did,i);

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

rc2=filename(filrfb,catx('\',directory,file));

fid=fopen(filrfb);

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

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

output;

rc3=fclose(fid);

rc4=filename(filrfb);

end;

rc5=dclose(did);

run;

%mend getfilesizes;

%getfilesizes(directory=c:\downloads,extension=sas,dataset=xxx)

proc print width=min;

run;

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

pavan1 — Tue, 21 Mar 2017 09:06:31 GMT

Hello Tom,

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

Thanks.

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

scott_rowley — Fri, 09 Mar 2018 17:25:43 GMT

I had the same issue and was able to resolve it by changing the \ to a / on the following line:

rc2=filename(filrfb,catx('/',directory,file));

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

derekx — Wed, 22 Aug 2018 09:42:44 GMT

didn't work.

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

derekx — Wed, 22 Aug 2018 09:43:42 GMT

didn't work. Can you help? SAS 9.4

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

derekx — Tue, 28 Aug 2018 00:48:38 GMT

Hello Tom, run the same code,same sas 9.4 , I get the blanks in dte and size and a friend get the right date. Do you think what is the possibel cause for this? Thank you very much

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

derekx — Wed, 29 Aug 2018 00:53:19 GMT

ello Tom,

run the same code,same sas 9.4 , I get the blanks in dte and size in my computer while my friend gets the right dte.

Do you think what is the possibel cause for this?

Thank you very much