Desktop productivity for business analysts and programmers

MACRO functioning multiple time...wants to run it once for each file...

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

MACRO functioning multiple time...wants to run it once for each file...

Hello friends, i am running below code (one of community user helped to build this code) and everything running fine, just wants to check couple of things....

Goal: goal to run this code to transfer files from one folder to another folder but only if files in target directory doesn't have today's date stamp.

Good thing: is code running fine without any error

need to check: is this code check date stamp for each file and then transfer all files to target directory correctly and then code check date stamp for next file and transferring all files again and doing something for every time...

what i wants: is check date stamp for all files in target directory and if date is not today's date for any particular file then transfer that particular file then go for next file and then check for created date and if created date is not today then transfer that particular file only.....and same thing for every other file...

please help...

please let me know if any part is not clear...

Thanks a lot...!!!

/*in this filename statement, mentioned path has all NEW mdb files created which needs to be transfer*/

/*still checking created date for all these NEW mdb files*/


filename new_mdb pipe 'dir "j:\jim\db\*.mdb" /tw';

data new_files;

   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 new_mdb truncover ;

   input buffer $2000.;

   /* Remove some extra info */

   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;

   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,' ')) ;

   Username=scan(file_name,1,'_');

   if file_name = ' ' then delete ;

run ;

proc print data=new_files;

var file_name file_last_written_date;

run;

proc sort data=work.new_files;

by Username;

run;

/*in this filename statement, mentioned path is where files need to be transfered to

BUT only those files WHICH HAS NOT TODAY'S TIME STAMP*/

/*check for created date*/

filename abc_ex pipe 'dir "z:\SHARES\db\USA\*.mdb" /tw';

data exist_files;

  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 abc_ex truncover ;

  input buffer $2000.;


  /* Remove some extra info */

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;

  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,' ')) ;

  Username=scan(file_name,1,'_');

  if file_name = ' ' then delete ;

run ;

proc print data=exist_files;

var file_name file_last_written_date;

run;

proc sort data=work.exist_files;

by Username;

run;

/*creating macro variables*/


data work.chk_date;

  set work.exist_files 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_;

/*using control table to get "username" and "location"*/

/*tbl_user has all the user's name as "Username" Location as "USA" or "BRAZIL" or "INDIA"*  /

libname test 'g:\SHARES\jim';

data work.control_tbl;

set test.tbl_user;

run;

    proc sort data=control_tbl;

       by Username;

    run;


/*Filter for USA users*/

data work.usa_users;

set work.control_tbl;

  if Location="USA";

run;

    proc sort data=work.usa_users;

       by Username;

    run;


/*PROC FORMAT for USA sharepoint*/

proc format;

   value $usa_location

                       "USA"= "z:\SHARES\db\USA";

run;

        /*check output for PROC FORMAT*/

    proc format library=work.formats fmtlib;

        select $usa_location;

    run;

    quit;

/*one newly created variable "targetdir"*/

%let path=j:\jim\db;

data transfer;

merge new_files (in=in1) usa_users (in=in2);

   by Username;

   if in1 and Location="USA";

       targetdir=put(Location,$usa_location.);

       copycmd=catx(' ', 'copy'

                       ,quote(cats("&path\", file_name))

                  ,quote(cats(targetdir))

           );

run;

    proc sort data=work.transfer;

       by file_name;

    run;

options mprint mlogic symbolgen;

%macro loop_files;

   %if &filecount>0 %then

     %do;

        %do i=1 %to &filecount;

           %check_new_date(&&file&i, &&crdate&i); /*recursive macro syntax - &i resolve first and then &file1*/

        %end;

     %end;

%mend loop_files;

%macro check_new_date(thisfile, thiscrdate);

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

         %do;

              data _null_;

                put "file got transferred by first process";

              run;

         %end;

      %else

         %do;

             data _null_;

                 set transfer;

                 infile cmd pipe filevar=copycmd;

                 input @;

             run;

         %end;

%mend check_new_date;

%loop_files;


Accepted Solutions
Solution
‎05-12-2014 05:49 PM
Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

That looks like you didn't merge properly.

I'd expect to see something as follows:

proc sort data=file_list1; by file_name;

proc sort data=file_list2; by file_name;

run;

data want;

merge file_list1 file_list2;

by file_name;

copycmd = same as shown in a few steps above.

* now add a clause that only selects files where the dates don't match or one isn't what you expect;

if  file_last_written_date_old < file_last_written_date_new ;

run;

View solution in original post


All Replies
Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

Test your macro by generating some test cases and running it Smiley Happy

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

i did and code runs fine without any error but the thing is all files getting transfer to target directory every time when code checks the condition...and that is why code is running longer too...so this code is not optimized...

  %else

         %do;

             data _null_;

                 set transfer;

                 infile cmd pipe filevar=copycmd;

                 input @;

             run;

         %end;

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

this is complicate actually...let me put this in a different way.

assume that i have 10 same name files in two folders

j:\shares\USA

                         username1_finance.mdb

                         username2_finance.mdb

                         .

                         .

                         username10_finance.mdb

j:\jim\db\

                         username1_finance.mdb

                         username2_finance.mdb

                         .

                         .

                         username10_finance.mdb

Goal: i want to check the date stamp for all files which are at j:\shares\USA and if file date stamp is not today() then transfer that file from "j:\jim\db\" to "j:\shares\USA\" and if file date stamp is today() then do not transfer that file...


Thanks!

Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

Add in the relevant where statement to your data step, example below that will give you an idea.

data _null_;

                 set transfer;

where date ne today();

                 infile cmd pipe filevar=copycmd;

                 input @;

             run;

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

no then "transfer" dataset is not giving any file to the cmd command to transfer and it will return 0 observation....

data _null_;

                 set transfer;

where date ne today();

                 infile cmd pipe filevar=copycmd;

                 input @;

             run;

NOTE: There were 0 observations read from the data set WORK.TRANSFER.

Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

That's because the condition isn't correct, you'll need to change it to match your variable name and conditions.

This will allow you to limit what files to copy.

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

so i think i need to modified below command (because filevar=copycmd has all the files which needs to be transfer to target folder...) to only one individual file at the same time, not all files like below...

data _null_;

                 set transfer;

where date ne today();

                 infile cmd pipe filevar=copycmd;

                 input @;

             run;

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

let me show transfer dataset, its looks like this,

       file_last_written_date       file_name                                   targetdir                      copycmd                     

1     05may2014                        username1_finance.mdb                j:\shares\USA               copy "j:\jim\db\username1_finance.mdb

                                                                                                                                               j:\shares\USA\username1_finance.mdb"

2     05may2014                        username2_finance.mdb                j:\shares\USA               copy "j:\jim\db\username2_finance.mdb

                                                                                                                                               j:\shares\USA\username2_finance.mdb"

.

.

.

10   05may2014                       username10_finance.mdb               j:\shares\USA                copy "j:\jim\db\username10_finance.mdb

                                                                                                                                                j:\shares\USA\username1_finance.mdb"

/*so basically transfer all files which are under "file_name" variable (in "transfer" dataset) after checking date stamp at "j:\shares\USA" one by one...becasue same files would be there with today's date, if file is there with today's date, i am not transferring it */

Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

1. Get the list of files and last modified date from directory 1

2. Get the list of files and last modified date from directory 2

3. Merge those two files together - make sure to use different variable names for the last modified dates

4. Then decide which ones need to be copied via a COPY command.

You have 1/2 in first part above. Step 3 is what I think you're missing and I assumed you where there.

4 is the step you were showing.

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

I think i am not clear yet, my main problem is how copycmd would look like...let't say if i got this using you above steps from 1 to 3...

       file_last_written_date_new       file_name                                   targetdir                    file_last_written_date_old               

1     05may2014                                 username1_finance.mdb                j:\shares\USA             .

                                                                                                                                             30APR2014

2     05may2014                                 username2_finance.mdb                j:\shares\USA             .

                                                                                                                                             30APR2014

.

.

.

10   05may2014                                  username10_finance.mdb               j:\shares\USA            .

                                                                                                                                              30APR2014



how my copycmd wold look like then?   i am still there Smiley Happy...

Solution
‎05-12-2014 05:49 PM
Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

That looks like you didn't merge properly.

I'd expect to see something as follows:

proc sort data=file_list1; by file_name;

proc sort data=file_list2; by file_name;

run;

data want;

merge file_list1 file_list2;

by file_name;

copycmd = same as shown in a few steps above.

* now add a clause that only selects files where the dates don't match or one isn't what you expect;

if  file_last_written_date_old < file_last_written_date_new ;

run;

Contributor
Posts: 55

Re: MACRO functioning multiple time...wants to run it once for each file...

Okay Reeza, i got ONLY those files which are needs to transfer in "want" dataset and then i used below macro code and it worked fine...

/*get some macro variables*/

data _null_;

set want end=eof;

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

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

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

%macro transfer;

        %if &filecount>0 %then

                %do;

                    data _null_;

                    set want;

                    infile cmd pipe filevar=copycmd;

                    input @;

                    run;

                 %end;

                  %else

                           %do;

                                 data _null_;

                                 put "no files to transfer";

                                 run;

                           %end;

%mend;

%transfer;

Thanks a lot Reeza.... Smiley HappySmiley HappySmiley Happy

BUT just to know, why above macro transfer all files every time it check condition for any individual file...? IF YOU CAN TELL ME PLEASE...what went wrong...? do you see any solution in above code if we want to do more macronize work....

Thanks again...

Thanks!

Grand Advisor
Posts: 17,338

Re: MACRO functioning multiple time...wants to run it once for each file...

Your data _null_ step isn't used in your macro.

Unfortunately I don't have time to go through your code, my guess is you weren't filtering out the data sets as I suggested.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 474 views
  • 0 likes
  • 2 in conversation