topic Re: Move external files in SAS Programming

Move external files

Patrick — Tue, 21 Aug 2018 12:59:15 GMT

Hi experts,

I want to move external files programmatically from one folder to another and I want to capture the OS log messages in the SAS log.

I know how this works for a single file using code as below.

filename moveit pipe 'mv -f /sourcedir/myfile.txt /targetdir';
data _null_;
  infile moveit;
  input;
  put _infile_;
run;

What I have is a SAS dataset from a directory listing with a list of files to be moved. Something like below:

data files_to_move;
  file_name='something.txt          ';
  file_path='/sourcedir';
  output;
  file_name='something_else.txt';
  file_path='/sourcedir';
  output;
run;

I'm having a "brain freeze" and just can't figure out how to move these files with a single data step while also capturing the log messages. I feel I better ask you guys for help before I start with some macro code generating separate filenames/data steps per file.

What I'm after is something like below:

%let target_path=/targetdir;
data _null_;
  set files_to_move;
  /*  ????? move files */
run;

I believe to remember that I've seen code from @Tom doing what I want but I just can't find it. Help - please!

Thanks,

Patrick

Re: Move external files

RW9 — Tue, 21 Aug 2018 13:04:37 GMT

data _null_;
  set files_to_move;
  call execute(cats('filename tmp pipe ',"'",'copy ",filepath,"/",filename,'" "',target_path,'"; data log;  infile tmp; put _input_; run;')); 
run;

So this creates for each row in input dataset, a filename pipe of the copy command, then this is read in as infile and put out to log.

I really would however advise against using SAS - which is for analysing data - to do operating system operations. If you need to move files its only a matter of cntrl+a and then move them across in Windows explorer. What benefit is there in programming this?

Re: Move external files

Kurt_Bremser — Tue, 21 Aug 2018 13:08:12 GMT

Make use of the fact that mv allows to move multiple files into one target:

data files_to_move;
  file_name='something.txt          ';
  file_path='/sourcedir';
  output;
  file_name='something_else.txt';
  file_path='/sourcedir';
  output;
run;

proc sort data=files_to_move;
by source_dir;
run;

data _null_;
set files_to_move;
by sourcedir;
length filenames $4096;
retain filenames;
if first.sourcedir
then filenames = '';
filenames = catx(' ',filenames,file_name);
if last.sourcedir
then do;
  call execute("filename oscmd pipe 'mv " !! trim(filenames) !! " " !! "&target_path. 2>&1';");
  call execute("data _null_; infile oscmd; input; put _infile_; run;");
end;
run;

Re: Move external files

Patrick — Tue, 21 Aug 2018 13:10:22 GMT

@RW9

"If you need to move files its only a matter of cntrl+a and then move them across in Windows explorer"

This will be part of an ETL process run in batch on a RHEL server where I don't know the file names in advance.

Thank you for the proposed solution but that's not really what I'm after as it still executes a separate data step per file.

What I'm after is a data _null_ step for all the files at once - but allowing me to write the log messages to the SAS log (the put _infile_; bit in the code sample I've posted).

Re: Move external files

Tom — Tue, 21 Aug 2018 13:10:58 GMT

Use PIPE engine with FILEVAR option on an INFILE statement.

%let target_path=/targetdir;
data _null_;
  set files_to_move;
  length cmd $300 ;
  cmd=catx(' ','mv -f',catx('/',file_path,file_name),"&target_path");
  infile mv pipe filevar=cmd ;
  input ;
  put _infile_;
run;

You can examine the input buffer for error messages if you want.

Re: Move external files

Patrick — Tue, 21 Aug 2018 13:45:00 GMT

@Tom

Thanks! And the code you've posted is certainly heading into the right direction.

For some reason I can't explain the code is only moving one file per run.

Below is real code which I've run in my environment - with two files in sourcedir (file1.txt and file2.txt). For some reason I don't understand the first code execution only moved file1.txt, the 2nd then also moved file2.txt.

I've also change the input statement to input @@; but this didn't change a thing.

Any ideas?


%let target_path=/tmp/targetdir;
data dirlist;
  path_name='/tmp/sourcedir';
  file_name='file1.txt';
  output;
  file_name='file2.txt';
  output;
run;

data test;
  set dirlist;
  length cmd $1000 ;
  cmd=catx(' ','mv -f',catx('/',path_name,file_name),"&target_path");
  infile mv pipe filevar=cmd ;
  input ;
  put _infile_;
run;

And here the log from the first run which moved file1.txt

36         data test;
37           set dirlist;
38           length cmd $1000 ;
39           cmd=catx(' ','mv -f',catx('/',path_name,file_name),"&target_path");
40           infile mv pipe filevar=cmd ;
41           input ;
42           put _infile_;
43         run;

NOTE: The infile MV is:
      Pipe command="mv -f /tmp/sourcedir/file1.txt /tmp/targetdir"

NOTE: 0 records were read from the infile MV.
NOTE: There were 1 observations read from the data set WORK.DIRLIST.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.

Re: Move external files

Tom — Tue, 21 Aug 2018 13:44:14 GMT

Possible the INPUT is reading past the end of file.

One trick I use sometimes is to use the END= option. This should also let you capture multi-line messages.

infile mv pipe filevar=cmd end=eof;
while (not eof);
  input ;
  put _infile_;
end;

Re: Move external files

Patrick — Tue, 21 Aug 2018 13:53:30 GMT

@Tom

That's done the trick. Thanks tons for your help!

Re: Move external files

Ksharp — Tue, 21 Aug 2018 14:33:14 GMT

You can make a SHELL file ,and execute it once for all.

Make a shell file which contain

mv -f /sourcedir/A.txt /targetdir
mv -f /sourcedir/B.txt /targetdir
mv -f /sourcedir/C.txt /targetdir
mv -f /sourcedir/D.txt /targetdir

Then execute this shell file.

filename moveit pipe './home/xxxx.sh'; data _null_; infile moveit; input; put _infile_; run;

Re: Move external files

Patrick — Tue, 21 Aug 2018 14:41:40 GMT

@Ksharp

Thanks @Ksharp

The solution Tom posted is what I need.

The real use case is:

I'm getting files delivered from an upstream process (dumped into a folder) and I'm also getting metadata for these files inserted into an Oracle table. This process is on-going 24/7.

I need to create a directory listing of the files, inner join it with the metadata and then only process the files which exist both in metadata and on the file system.

My process will execute in mini-batches and I can't be sure that metadata and files in the directory are always in sync (that's why I need the inner join). If I find information in both places then I can process the files (move them to a process folder for the next step). And I want some info in the SAS log if/how moving the files worked.