Copy File from WORK to Network drive

[David_Billa]

I was asked to tweak the following code and idea is to create file in WORK folder first and then copy that file to the output folder. Because our client observed that the users are accessing the file from the output folder before it is fully being generated. In order to prevent this issue, we came up with the above said approach. Is it good? If yes, may I request someone to confirm whether the below code conversion is correct?

Current Version: 

%macro clc_report;
ods excel file="&output_location./report_&reportDate._&reportTime..xlsx";
proc report data=appdata.test1; options missing=0;
column therapy_type;
define therapy_type / group "Therapy Type";
run;
ods excel close;

proc export data=appdata.test2
  outfile="&output_location./details_report_&reportDate._&reportTime..xlsx"
   dbms=xlsx
replace;
   sheet=All_CLCs;
run;

%mend;

Proposed Version:

%let work_path=%sysfunc(pathname(WORK));
 %put &=work_path;
%macro clc_report;
ods excel file="&work_path./report_&reportDate._&reportTime..xlsx";
proc report data=appdata.test1; options missing=0;
column therapy_type;
run;

* Copy the Excel  Report file from &work_path to &report_path *;
* include fcopy messages in the log *;
  options msglevel=i; 

  filename _SRC "&work_path./report_&reportDate._&reportTime..xlsx" recfm=n;
  filename _DEST "%unquote(&report_path)/report_&reportDate._&reportTime..xlsx" recfm=n;

  data _null_;
    rc=fcopy('_SRC', '_DEST');
    if rc=0 then
    put "Successful copy to &report_path/report_&reportDate._&reportTime..xlsx !";
    else do;
    put "Copy to &report_path/report_&reportDate._&reportTime..xlsx failed!";
    end;
  run;

  %g_catchErr(
        abortOnError = 0,
        appExceptionText = File copy to &report_path/report_&reportDate._&reportTime..xlsx failed!
        )

    filename _SRC clear;
    filename _DEST clear;

ods excel close;

proc export data=appdata.test2
  outfile="&work_path./details_report_&reportDate._&reportTime..xlsx"
   dbms=xlsx
replace;
    sheet=All_CLCs;
run;

* Copy the Excel Details Report file from &work_path to &report_path *;
* include fcopy messages in the log *;
  options msglevel=i; 

  filename _SRC "&work_path./details_report_&reportDate._&reportTime..xlsx" recfm=n;
  filename _DEST "%unquote(&report_path)/details_report_&reportDate._&reportTime..xlsx" recfm=n;

  data _null_;
    rc=fcopy('_SRC', '_DEST');
    if rc=0 then
    put "Successful copy to &report_path/details_report_&reportDate._&reportTime..xlsx !";
    else do;
    put "Copy to details_report_&report_path/details_report_&reportDate._&reportTime..xlsx failed!";
    end;
  run;

  %g_catchErr(
        abortOnError = 0,
        appExceptionText = File copy to &report_path/details_report_&reportDate._&reportTime..xlsx failed!
        )
    filename _SRC clear;
    filename _DEST clear;

%mend;

[Kurt_Bremser]

If the target destination (which means: your SAS server and the NAS) runs on UNIX, you can remove the file even when it is in use (as you do not remove the file itself, but only the directory entry).

Run the external command rm -f before copying the file.

[David_Billa]

@Kurt_Bremser Thanks for your reply. Two questions.

A) Whether my proposed solution is correct even though it is not efficient?

B) I have not used UNIX command  in my SAS program yet. It would be nice if you show me how and where to place rm -f command from my program?

[Kurt_Bremser]

data _null_;
infile pipe "rm -f &reportpath./report_&reportdate._&reporttime..xlsx 2>&1";
input;
put _infile_;
run;

ods excel file="&report_path./report_&reportDate._&reportTime..xlsx";

proc report data=appdata.test1; options missing=0;
column therapy_type;
run;

ods excel close;

data _null_;
infile pipe "rm -f &report_path./details_report_&reportDate._&reportTime..xlsx 2>&1";
input;
put _infile_;
run;

proc export
  data=appdata.test2
  outfile="&report_path./details_report_&reportDate._&reportTime..xlsx"
  dbms=xlsx
  replace
;
sheet=All_CLCs;
run;

This will work around any locked files, but only when all involved systems have filesystems with separate inode tables. Windows can't do this, as it stores file metadata in the directory entries.

[David_Billa]

What's the use of 2>&1 in your infile statement? 

On a separate note, whether my proposed solution will work for windows? Sorry to ask this question again.

[Kurt_Bremser]

2>&1 has already been explained.

Your code looks good, but it can only report that the file could not be replaced. That's the best you can get on Windows.

[David_Billa]

What's the use of 2>&1 in your infile statement?  On a separate note, whether my proposed solution will work for windows? Sorry to ask this question again.

[Tom]

2>&1 will redirect the standard error output (file handle 2) to the standard output (file handle 1).  So both the normal output message and the error output messages will be streamed as the output of the PIPE.

You can use FCOPY to copy files on Windows. So as long as the paths you have constructed work on the machine where the SAS code is running then your program should run.  Did you try it?

[David_Billa]

@Tom Thank you for the explanation. I yet to try the proposed solution per my initial post. So using 'rm -f' is the only efficient solution for my problem? It looks that the SAS runs on UNIX in our platform.

[Tom]

---

@David_Billa wrote:

@Tom Thank you for the explanation. I yet to try the proposed solution per my initial post. So using 'rm -f' is the only efficient solution for my problem? It looks that the SAS runs on UNIX in our platform.

---

The command "rm -f" is to remove a file and force the removal.  Not sure why you would need to remove the file?  If you wrote it into the WORK directory it should be removed when your session ends.   But anyway you can use the FDELETE() function to delete the file with SAS code.

Also not sure why you would need the -f option. You wrote the file you should be able to remove it.  Some sites do tricks like make aliases named rm that include an option force the shell to always prompt you to confirm when deleting a file.  You can avoid that by either using the full name of the command (instead of letting the shell "find" it for you). You can also add \ in front of the command and it will not match an alias.

[David_Billa]

@Tom Please see my initial post. Background of the problem is 'I was asked to tweak the following code (see my initial post) and the idea is to create file in WORK folder first and then copy that file to the output folder. Because our client observed that the users are accessing the file from the output folder before it is fully being generated. In order to prevent this issue, we came up with the above said approach. Is it good?'

[Tom]

---

@David_Billa wrote:

@Tom Please see my initial post. Background of the problem is 'I was asked to tweak the following code (see my initial post) and the idea is to create file in WORK folder first and then copy that file to the output folder. Because our client observed that the users are accessing the file from the output folder before it is fully being generated. In order to prevent this issue, we came up with the above said approach. Is it good?'

---

Copying the file should be faster than building originally. So that would reduce the amount of time where the file might be visible to be found but incomplete.

If you want to reduce the time then use the move (or rename) command instead of the copy command.  Then the change is almost instantaneous as only the directory entry is being changed.  You will need to use operating system commands for this as I don't think SAS has provided an FRENAME() function to move/rename a file. On Unix the command is mv and Windows the command is rename.  So write the original file to the target directory but use a name that the users will not try to open. For example change the extension to something else.  SAS won't mind when it creates the file, but users will not try to open it.

Example (Windows system):

filename original 'c:\downloads\test_bad.junk_file' ;
ods excel file=original;
proc print data=sashelp.class; run;
ods excel close;

data _null_;
  infile "rename ""c:\downloads\test_bad.junk_file"" ""test_good.xlsx"" 2>&1" pipe ;
  input;
  put _infile_;
run;

For Unix the mv command will take full paths for both names.  And on Unix the file can be in a different subdirectory as long that directory is on the same physical disk.

data _null_;
  infile "mv ""/usr/local/downloads/test_bad.junk_file"" ""/usr/local/downloads/test_good.xlsx"" 2>&1" pipe ;
  input;
  put _infile_;
run;

[David_Billa]

This option sounds good. How to rename the .jung_file to .xlsx while in
proc export?

Because our programs are using both ODS EXCEL and proc export to generate
output.xlsx files.

[Tom]

---

@David_Billa wrote:
This option sounds good. How to rename the .jung_file to .xlsx while in
proc export?

Because our programs are using both ODS EXCEL and proc export to generate
output.xlsx files.

---

?? To change the name you use to create the file you just have to change the name you use in the code that creates the file.

[David_Billa]

@Tom Why two double quotes for every single folder in infile statement?

""c:\downloads\test_bad.junk_file""