topic Re: MACRO functioning multiple time...wants to run it once for each file... in SAS Enterprise Guide

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

jimksas — Fri, 09 May 2014 23:50:24 GMT

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;

%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;

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

Reeza — Sat, 10 May 2014 00:14:18 GMT

Test your macro by generating some test cases and running it

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

jimksas — Sat, 10 May 2014 00:32:38 GMT

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;

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

jimksas — Mon, 12 May 2014 17:57:06 GMT

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!

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

Reeza — Mon, 12 May 2014 18:56:01 GMT

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;

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

jimksas — Mon, 12 May 2014 20:01:15 GMT

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.

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

Reeza — Mon, 12 May 2014 20:10:54 GMT

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.

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

jimksas — Mon, 12 May 2014 20:14:50 GMT

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;

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

jimksas — Mon, 12 May 2014 20:45:30 GMT

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 */

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

Reeza — Mon, 12 May 2014 20:55:16 GMT

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.

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

jimksas — Mon, 12 May 2014 21:39:19 GMT

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 ...

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

Reeza — Mon, 12 May 2014 21:49:57 GMT

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;

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

jimksas — Mon, 12 May 2014 23:22:01 GMT

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....

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!

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

Reeza — Tue, 13 May 2014 00:12:41 GMT

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.