Extract file creation date to write to variable

[rpk7]

Good day folks,

Full apologies for being an utter noob.  My SAS training is ongoing, and primarily on-the-job.  That said, I've tried to crack the following nut for awhile now.

Essentially, one of my tasks involves comparing multiple sas files output from iterative runs of a custom application. En masse. I've managed to Frankenstein together something that works for me. What would make it even better would be if I could retrieve the creation dates of the files to pass on as variables for later analysis, verification and reporting. In each case, I have an old file and a new file. The majority of my variables involve different parameters for the proc compare and to specify the path of the files to be compared.  My data is large sas7bdat files.

I'm running my program in SASEG 7.1 to make full use of the prompts (for frequent variable changes) and point-and-click interface (cause I'm new).  To connect to my data, I have to use a connection profile to a remote machine (our data grid).  Some of the solutions I've tried will work with local (c:/) files or when run locally on SAS 9.4.  But, nothing I've tried will work with my current set-up.

Here's a snippet of my main macro:

%macro checkcomp(oldlib, newlib, file, ID, dropvars, methcrit, prefix);
	%if %sysfunc(exist(&oldlib..&file)) and %sysfunc(exist(&newlib..&file)) %then
		%do;
			proc compare base=&oldlib..&file &dropvars compare=&newlib..&file &dropvars &methcrit listvar maxprint=(50,500);
				attrib _all_ label= ' ';
					informat _all_;
					&ID;
					format _all_;
					title1 BOLD color=red height=24pt "&prefix&file";				
			run;

			%fillsheet(&prefix&file);
		%end;
	%else
		%do;
			proc sql;
				update InstanceCompare
					set &prefix&file="NA";
		%end;
%mend checkcomp;

After everything is defined, I specify my LIBNAME just before calling the macros.  For example:

LIBNAME DIBase "/home/&user/&env1/&oldcycle/Iteration_&olditer";
LIBNAME DIComp "/home/&user/&env2/&newcycle/Iteration_&newiter";

%checkcomp (OLDLIB=DIBase, NEWLIB=DIComp, FILE=core_val, ID=ID 4, DROPVARS=, METHCRIT=, PREFIX=);

Ideally, I'd like to retrieve the specific (to that macro call) file creation dates just before the proc compare.  And then write those values to a set of variables, like &oldcrdate and &newcrdate.  Ideally for use (in this example) in my report header.  Something like this:

title2 BOLD color=red height=18pt "&oldcrdate to &newcrdate";

Bear with me.  I started very blindly, by dropping the following into my program:

%let oldcrdate=%sysfunc(finfo(&oldlib..&file,Creation));
%let newcrdate=%sysfunc(finfo(&newlib..&file,Creation));

			proc compare base=&oldlib..&file &dropvars compare=&newlib..&file &dropvars &methcrit listvar maxprint=(50,500);
				attrib _all_ label= ' ';
					informat _all_;
					&ID;
					format _all_;
					title1 BOLD color=red height=24pt "&prefix&file";
					title2 BOLD color=red height=18pt "&oldcrdate to &newcrdate";
			run;

The result in my log was:

ERROR: Argument 1 to function FINFO referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.

Then I tried a new macro from the SAS helpfiles, just to try to see the data I wanted:

%macro FileAttribs(lib, file);           
  %local rc fid fidc Bytes CreateDT ModifyDT; 
  %let rc=%sysfunc(filename(onefile,&lib..&file)); 
  %let fid=%sysfunc(fopen(&onefile));
     %if &fid ne 0 %then %do;  
  %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 &lib..&file is &bytes bytes;    
  %put NOTE- Created &createdt;       
  %put NOTE- Last modified &modifydt;
     %end;
        %else %put &lib..&file could not be open.;
%mend FileAttribs;
                      
%FileAttribs(LIB=DIBase, FILE=core_val)

But my result was always the %else statement: &lib..&file could not be open.

I'd really appreciate any help. Please "dumb it down a shade" for me, if you could.  No step is so obvious that I wouldn't overlook it.  Thanks for your time. 

[Shmuel]

%sysfunc(filename...) requires a full name of the file including it suffix - i/e/ file type.

You had run the macro as:  

%FileAttribs(LIB=DIBase, FILE=core_val);

I believe there is no file named DIBase.core_val. If this is a sas data set then you need supply

%FileAttribs(LIB=DIBase, FILE=core_val.sas7bdat);

 or some other suffix.

[ballardw · Accepted solution]

First off, are you looking for information about SAS data sets or external files like reports?

If you want data about SAS data sets you might try something similar to:

proc sql;
   create table work.properties as
   select * 
   from dictionary.tables
   where libname='WORK'
   ;
quit;

which will collect all the information about all of the data sets in the stated library. The libname and memname (data set name for example) in the source are stored as uppercase hence 'WORK' .

This will have things like the creation datetime, modified datetime, number of variables, number of observations and lots of other stuff.

So you would have the pertinent stuff for multiple sets.

The where clause above could be modified to list specific sets such as

proc sql;
   create table junk as
   select * 
   from dictionary.tables
   where libname='WORK'
      and memname in ('ABC' 'PDQ' 'XYZ')
      and memtype = 'DATA'
   ;
quit;

or use a LIKE or CONTAINS condition with the MEMNAME to select similar named sets.

The FOPEN and such are actually intended to open external non-SAS files like text files. So they don't actually use the libname.dataset to find things they really want the entire physical file path and name:

"C:\folder\subfolder\thisfile.txt" . Or use the DOPEN to start with a directory and then indentify the files and open them.

[rpk7]

Thanks, ballardw. You pointed me in exactly the right direction. Much appreciated.