BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jimksas
Calcite | Level 5

Hello Friends,

i am using below code and once i got "work.date" after end of the proc contents step, i want to use "create date" and "last modified date" variable in %macro step for multiple if statement.

Can someone please tell me how i can use those two variable in next %macro step?

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

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

  infile filelist truncover lrecl=256;

  input filename $256.;

  name=scan(filename,1,'_');

run;

proc sort data=work.use_date;

  by name;

run;

proc contents data=work.use_date out=work.date;

run;

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

/*i want to use  "create date" and "last modified date" from work.date here */

%macro xyz;

%if create_date=today() %then %do;

        data....

                 .....

                  ....

                 ....

        run;

%else %do;

       data....

              ....

                 ...

       run;

           data....

                ....

                     ...

           run;

data....

                ....

                     ...

           run;

%end;

%mend;

%xyz;

1 ACCEPTED SOLUTION

Accepted Solutions
cwilson
Calcite | Level 5

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.

View solution in original post

25 REPLIES 25
TomKari
Onyx | Level 15

I'm not sure that your code is meeting your needs.

The  "create date" and "last modified date" variables in "work.date" are the date that work.use_date are created, which is the date that you are running the project.

What are you trying to do with this code?

Tom

jimksas
Calcite | Level 5

I want to use those two variable like this;

%macro check_new_date;

%if datepart(create_date) = today() %then

    %do;

          data _null_;

             abort return;

          run;

%else %do;

        data check1;

        .....

        .....

        run;


       proc sort data=check1;

        by....

        run;


        data check2;

       ....

       run;

       proc sort data=check2;

       by ...

       run;


      data check;

       merge check1 (in=a) check2 (in=b)

        if a=1;

       run;


     and more data setps;

     run;

%end;

%mend;

%check_new_date;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am afraid that TomKari is correct here.  Your logic doesn't make sense as is.  The date created and date modified will be the date time that this part of the code is run:

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

Now what I think you want is the date time of the file as it is from the operating system.  I.e. if I create a file in Windows on the 13MAR2013, then I run your code on the 26JAN2014, the creation date would be 26JAN2014, whereas what you want to know is 13MAR2013.  To do this you need to modify how you get the directory listing.  Doing the below gets the directory listing, then post processes the info to get creation date time, size and filename.  These can then be symputted into macro variables or otherwise processed.  Note the /tc in the pipe, this means the date/time is the creation date/time.  If you want accessed then /ta, for last written use /tw.

filename mypipe pipe 'dir "s:\temp\rob" /tc';

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

SKK
Calcite | Level 5 SKK
Calcite | Level 5


Maybe this will help you:


proc sql noprint;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date from work.date;

quit;


%macro check_new_date;
%if  &create_date = %sysfunc(today()) %then

    %do;

.........

ballardw
Super User

What does Proc contents have to do with this question?

jimksas
Calcite | Level 5

Hi Ballardw - goal is to use "create date" and or "last modified date" from "proc contents" for further "if then else" condition....

jimksas
Calcite | Level 5

I am sorry - I am trying to get "created date" for .mdb files exist on that folder ""f\shares\jim\finance\*.mdb"...

and then based on that (created date) - my next logic will be...

How can i get it...please?

Thanks.....

cwilson
Calcite | Level 5

I see about 4 things that need to be done.  I do something similar where I use Proc Contents to check a variable list of incoming Access files, to look for specific tables, and then load data from those tables.

1.  Get Create_date from Proc Contents on the Access data file

2. You need to add parameters to your Macro for the filename and the creation date to pass into the Macro

3. Because your macro is doing full DATA steps, you can't call the macro from inside a DATA step.  You need to have what I call a "driver" macro, to loop through the list of files.

4. In preparation for the driver macro, I use CALL SYMPUT to write each filename to a macro variable with a counter for the suffix, and loop through those in the driver macro.

I have program code, but I would need to first edit it heavily.  I have been swamped this morning, so I have not yet done that.

jimksas
Calcite | Level 5

thanks CWILSON - i am using "proc contents" to get date information, and SK K solutions for macro variable (you are saying call symput)...i think it will work...but if you share code with your logic - would be grt...

thanks!

cwilson
Calcite | Level 5

There are a couple things that are not clear to me:

1. When you get your file list, I assume you are expecting multiple files, right?

2. I don't understand why you scan up to the first "_" when parsing the file name, because to process the file, you will want the full file name.

3. In your second post, you indicate that if the file being processed was created today, then you want to abort return?  Does that mean you want to stop all processing?  Or you just don't want to process that particular file? (I normally would expect to move on to the next file, so I would change the %if logic slightly.)

Caveats:

1.  I have not tested this code.

2.  I have never tried to get the create date for an Access file using the output from the Proc Contents.  In order to get the list of tables inside the Access file, I had to use ODS trace on and ODS output statemtns, which I have excluded from the code below, in the hopes that the Proc Contents output gives you what you want.

3.  If nothing else, this should move you forward to what you want.

Starting with your code:

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

filename filelist pipe 'dir "f:\shares\jim\finance\*.mdb" /b';

data work.use_date;

  infile filelist truncover lrecl=256;

  input filename $256.;

  name=scan(filename,1,'_');

run;

proc sort data=work.use_date;

  by name;

run;

/* this step won't give you what you want...

proc contents data=work.use_date out=work.date;

run;

*/

* 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(filename) ) ;   ** correction ;

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

run;

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

%put file count = &filecount ;

** NEW: Driver macro that will loop through list of files ;

%macro LoopFiles ;

  %if &filecount > 0 %then %do ;

    %do i = 1 %to &filecount ;              ** correction ;

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

    %end ;

  %end ;

%mend LoopFiles ;

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

** add file parameter to your macro ;

%macro check_new_date(thisfile);

  * check the date of the Access datafile inside your macro (combination of SKK and my Access file code) ;

  %LET accfile1="f\shares\jim\finance\&thisfile" ;

  LIBNAME thisacc ="f:\shares\jim\finance\&thisfile";    ** correction ;

  proc contents data=thisacc._all_ out=work.date;  ** or replace _all_ with a specific table name, if you know what it is ;

  run;

** check values in work.date ;

proc print data = work.date ;

   var libname memname crdate modate ;

run ;

  proc sql noprint;

  select distinct datepart(crdate) into :create_date from work.date;

  quit;

%PUT Create_date for &thisfile = &create_date ;

  %if &create_date = %sysfunc(today()) %then

    %do;

          data _null_;

             abort return;

          run;

  %else %do;

        data check1;

        .....

        .....

        run;

       proc sort data=check1;

        by....

        run;

        data check2;

       ....

       run;

       proc sort data=check2;

       by ...

       run;

      data check;

       merge check1 (in=a) check2 (in=b)

        if a=1;

       run;

     and more data setps;

     run;

  %end;

%mend check_new_date;

** call driver macro ;

%LoopFiles;

Message was edited by: Carla Wilson A few corrections to my program code, flagged with ** correction ;

jimksas
Calcite | Level 5

I am thinking to use this rather...almost similar i guess...

options mprint mlogic symbolgen;

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';


data work.use_date;

  infile filelist truncover lrecl=256;

  input filename $256.;

  name=scan (filename,1,'_');

run;


proc sort data=work.date;

  by name;

run;


proc contents data=work.use_date out=work.chk_date;

run;

proc sql;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date 

from work.chk_date;

quit;

%put &create_date;

%put &modf_date;

  %if  &create_date = %sysfunc(today()) %then

     %do;

jimksas
Calcite | Level 5

even after all trials i am not getting what i want...I agree with TomKari's statement...

"The  "create date" and "last modified date" variables in "work.date" are the date that work.use_date are created, which is the date that you are running the project."

No i don't know how i will get that...created date on existing file...(let's say that it's not today or day when i want to run code...

Please help...!!!

cwilson
Calcite | Level 5

Yes, I also indicated in the code I posted that the proc contents on work.use_date was not going to work.

I did have some questions for you at the top of my post.  Please reply.

Furthermore, I don't believe you can use %if macro code in open SAS code.  I think it needs to be in a defined macro.  (but I could be wrong.)

I will have to set up a test to test the ability to read the create_date from an Access file, and this will have to be after-hours.

jimksas
Calcite | Level 5

I am embedding everything under macro only, so let's say we can go with this i guess...big question for me is, how to use "create_date" variable Smiley Happy?

%macro jimk;

options mprint mlogic symbolgen;

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';


data work.use_date;

  infile filelist truncover lrecl=256;

  input filename $256.;

  name=scan (filename,1,'_');

run;


proc sort data=work.date;

  by name;

run;


proc contents data=work.use_date out=work.chk_date;

run;

proc sql;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date

from work.chk_date;

quit;

%put &create_date;

%put &modf_date;

  %if  &create_date = %sysfunc(today()) %then

     %do;

....

....

...

%mend;

jimk;

%jimk;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 25 replies
  • 3476 views
  • 6 likes
  • 6 in conversation