using proc contents variable

[cwilson · Accepted solution]

Ok, I set up a test folder with some test Access datafiles and executed my program.  I found a couple mistakes in my code, which I corrected in my post above.  The program "worked" to a point.  It did loop throught the files very nicely, but the creation date was always missing.  I put a Proc Print after the Proc Contents step, and it turns out that crdate and modate, although available in the Proc contents output, are always missing.  Here is an example of the results:

                                         The SAS System      18:58 Wednesday, April 23, 2014  58

            Obs    LIBNAME       MEMNAME                  CRDATE              MODATE

             26    THISACC    Failure List                     .                   .

             27    THISACC    Graph Examples                   .                   .

So, unfortunately, it looks like perhaps Proc Contents is not able to determine the crdate for an Access file.

So, I tried the code posted by RW9.  I did have to tweak it a bit to successfully read my piped dir data (varies with system settings).  I also found I needed to use the option /tw, instead of /tc to get the dates I wanted.  /tc gave me the date that I copied the files into my test folder not the original file date.

filename mypipe pipe 'dir "C:\LocalData\AccessTest\*.mdb" /tw';

data use_date;
  attrib  buffer format=$2000.
              file_last_written_date format=date9.
              file_last_written_time format=time5.
              am_pm format=$2.
              file_size format=$30.
              file_name format=$50.;
  infile mypipe truncover ;
  input buffer $2000.;
  if substr(buffer,1,5) in ("Volum","Direc") or index(buffer,"<DIR>")>0 or index(buffer,"Dir(s)")>0 or index(buffer,"File(s)")>0 then delete;  /* Remove some extra info */
  file_last_written_date=input(put(scan(compbl(buffer),1,' '),$10.),mmddyy10.);  /* note dependant on system settings */
  file_last_written_time=input(put(scan(compbl(buffer),2,' '),$5.),time5.);
  am_pm=scan(compbl(buffer),3,' ');
  file_size=scan(compbl(buffer),4,' ');
  file_name=trim(scan(compbl(buffer),5,' ')) ;
  if file_name = ' ' then delete ;
run ;

proc print data = use_date ;
var file_name file_last_written_date ;
run ;

So, I changed my CALL SYMPUT step to create a macro variable for the crdate as well as the file name, and then you would change the Driver macro and your own Macro program to have two parameters.

* read set of filenames that are now sorted and generate macro variables ;
data work.use_date;
  set work.use_date end=EOF ;
  CALL SYMPUT('file' || trim(left(_n_)), trim(file_name) ) ;
  CALL SYMPUT('crdate' || trim(left(_n_)) , PUT(file_last_written_date,date7.)) ;
  if EOF then CALL SYMPUT('filecount', _n_) ;
run;

* this will give you macros file1, file2, etc. ;
%put file count = &filecount ;
%put file1 = &file1, crdate1 = &crdate1 ;

** NEW: Driver macro that will loop through list of files ;
%macro LoopFiles ;
  %if &filecount > 0 %then %do ;
    %do i = 1 %to &filecount ;
       %check_new_date(&&file&i, &&crdate&i);  *notice recursive macro syntax - resolve &i then resolve &file1, etc..;
    %end ;
  %end ;
%mend LoopFiles ;

Your macro:

%macro check_new_date(thisfile, thiscrdate);

  %if "&thiscrdate"d = %sysfunc(today()) %then %do;

          data _null_;

             abort return;

          run;

  %else %do;

         data check1;

        .....

%mend ;

** call driver macro ;

%LoopFiles;

Notice that I have used double-quotes around the date macro variable, and convert it from a text string to a SAS date with the "d' syntax.

I hope that you will consider trying my driver macro approach, with the parsing of the piped filelist outside of (and above) your macro.

[jimksas]

Thanks a lot cwilson - it worked great...i just used proc sql to get "crdate" as macro variable after your first data step and proc print step. And then my macros....

Thanks all...

[jimksas]

hi cwilson - however, i am still not sure how you explain below steps in your above code...

* this will give you macros file1, file2, etc. ;

%put file count = &filecount ;

%put file1 = &file1, crdate1 = &crdate1 ;

[cwilson]

After you produce the sas dataset, work.use_date, that contains one row for each file in your file list, the next data step uses CALL SYMPUT to create macro variables.  It uses the _n_ variable as a suffix on the macro variable name.  I am creating a macro variable for the name of the file, and another macro variable for the create date.  Finally, at the end of the file (flagged by end=EOF), I output a macro variable with the total number of files, which I then use to loop through the list.

So if you have 5 files, you will have the following set of macro variables:

&file1  &crdate1

&file2  &crdate2, etc.

&file5  &crdate5

&filecount (which = 5)

The %PUT statement is just to show the values for a few variables as a QC that it's working as desired.

[jimksas]

okay i understand that part but is there any other way to loop every .mdb files (lets's say we have 5 files to check date and then condition) with crdate...

------------------------------------------------------------------------------------

** NEW: Driver macro that will loop through list of files ;
%macro LoopFiles ;
  %if &filecount > 0 %then %do ;
    %do i = 1 %to &filecount ;
       %check_new_date(&&file&i, &&crdate&i);  *notice recursive macro syntax - resolve &i then resolve &file1, etc..;
    %end ;
  %end ;
%mend LoopFiles ;

*Your macro;

%macro check_new_date(thisfile, thiscrdate);

  %if "&thiscrdate"d = %sysfunc(today()) %then %do;

.....

-----------------------------------------------------------------------------------------------------

*if i alter your above piece of code like this; /*this is from log*/

-----------------------------------------------------------------------------------------------------

%macro LoopFiles_and_trans (thisfile, thiscrdate);

  %if &filecount > 0 %then %do ;

    %do i = 1 %to &filecount ;

       %check_new_date(&&file&i, &&crdate&i);  *notice recursive macro syntax - resolve &i then resolve &file1, etc..;

        -

        180

Warning: Apparent invocation of macro CHECK_NEW_DATE not resolved...

    %end ;

  %end ;

  %if "&thiscrdate"d = %sysfunc(today()) %then

       %do;

---------------------------------------------------------------------------------------------------------------------------------------------

/*i am getting error*/

ERROR: 180-322: statement is not valid or it is used out of proper order...

- Thanks!

[cwilson]

So, "check_new_date" needs to be changed to whatever your real macro name is.  (in the second of your posts, this is what you called it.)

However, you need to change your macro statement to include two parameters:

Your macro:

%macro check_new_date(thisfile, thiscrdate);

  ** use the parameter name in your if statement ;

  %if "&thiscrdate"d = %sysfunc(today()) %then %do;

. . .

   ** then in the data step where you want to USE the access file, use the file parameter variable ;

   ** you did not show that code, but you probably need a proc import ;

PROC IMPORT table="name-of-the-table-you-want" OUT= work.data1  DBMS=Access REPLACE ;

    DATABASE= "f:\blahblahblah\&thisfile" ;

run;

      . . .

[jimksas]

okay but we are not defining value for "&thiscrdate" and also for "&thisfile" - how and where it is defining?

in call symput we have value for "file" and "crdate" not for "&thisfile" and "&thiscrdate".....

/*your code*/

data work.use_date;

  set work.use_date end=EOF ;

  CALL SYMPUT('file' || trim(left(_n_)), trim(file_name) ) ;

  CALL SYMPUT('crdate' || trim(left(_n_)) , PUT(file_last_written_date,date7.)) ;

  if EOF then CALL SYMPUT('filecount', _n_) ;

run;

[cwilson]

Yes, they are defined as the parameters in your macro statement (this is why I said you had to add that):

%macro check_new_date(thisfile, thiscrdate);

[jimksas]

two last question...

- what would be the value for these two parameters: thisfile, thiscrdate?

- does this code will work like this?

data work.chk_date;

  set work.gln_exist end=EOF;

  call symput('file'||trim(left(_n_)), trim(file_name));

  call symput('crdate'||trim(left(_n_)), put(file_last_written_date,date9.));

  if eof then call symput('filecount',_n_);

run;

%put _all_;;

options mprint mlogic symbolgen;

%macro loop_files;

   %if &filecount>0 %then

     %do;

        %do i=1 %to &filecount;

           %check_new_date(&&file&i, &&crdate&i);

        %end;

     %end;

%mend loop_files;

%macro check_new_date(thisfile, thiscrdate);

      %if "&thisfile"d =%sysfunc(today()) %then

         %do;

                 ...........

         %end;

      %else

         %do;

                 .........

         %end;

%mend check_new_date;

%loop_files;

[cwilson]

The values for thisfile and thiscrdate are populated in the loop_files macro in the statement:

           %check_new_date(&&file&i, &&crdate&i);

as it loops through the number of files you have and the macro variables created with the CALL SYMPUT.

Yes, I think the baisc flow should work.  Obviously, you need to fill out your macro check_new_date to do what you want.

You might start small by just putting some %put statements inside your macro to show the value of &thisfile and &thiscrdate, and then running the full program to get a feel for how it works.

Then add your program code.

Please see my comment in post 20 about how you might need proc import to get the data that you want from the Access data file.

[jimksas]

Thanks a lot cwilson for your time and work through this out...

- All done...