topic Re: Copy File from WORK to Network drive in SAS Programming

Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 07:51:30 GMT

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;

Re: Copy File from WORK to Network drive

Kurt_Bremser — Sun, 05 Feb 2023 08:03:06 GMT

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.

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 08:15:42 GMT

@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?

Re: Copy File from WORK to Network drive

Kurt_Bremser — Sun, 05 Feb 2023 09:35:46 GMT

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.

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 09:43:57 GMT

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.

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 15:55:28 GMT

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.

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 17:46:08 GMT

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?

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 17:52:45 GMT

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

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 17:53:26 GMT

Note that XLSX files are BINARY files. You need to add RECFM=N to your FILENAME statements.

Example:

filename xx "%sysfunc(pathname(work))/test1.xlsx" recfm=n;
filename yy "c:\downloads\test2.xlsx" recfm=n;
ods excel file=xx ;
proc print data=sashelp.class; run;
ods excel close;
%let rc=%sysfunc(fcopy(xx,yy));
%put &=rc;

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 17:58:44 GMT

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

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 17:59:34 GMT

@Tom that's great. Is this the alternate solution when compared to @Kurt_Bremser program which he posted before. So recfm=n is not required when I consider to use the Kurt's approach?

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 18:03:02 GMT

@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?'

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 18:29:06 GMT

@David_Billa wrote:

@Tom that's great. Is this the alternate solution when compared to @Kurt_Bremser program which he posted before. So recfm=n is not required when I consider to use the Kurt's approach?

No sure what you mean. RECFM=N is needed if you want SAS to COPY the file for you using the FCOPY() function. Otherwise it will copy it as text and mangle it. If you are asking the operating system to copy the file then you don't need to specify RECFM=N as SAS will make the file properly without it.

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 18:40:11 GMT

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

Re: Copy File from WORK to Network drive

David_Billa — Sun, 05 Feb 2023 18:45:58 GMT

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.

Re: Copy File from WORK to Network drive

Tom — Sun, 05 Feb 2023 19:15:24 GMT

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

Re: Copy File from WORK to Network drive

Kurt_Bremser — Sun, 05 Feb 2023 21:58:35 GMT

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.

Re: Copy File from WORK to Network drive

David_Billa — Mon, 06 Feb 2023 05:51:47 GMT

@Tom Here are we copying the file from the xx folder to yy folder?

Re: Copy File from WORK to Network drive

David_Billa — Mon, 06 Feb 2023 06:58:26 GMT

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

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