Error using FCOPY Function

[David_Billa]

I've the code as below to move the files from source to target. When I tried to execute the code below, I've got error as,

ERROR: Unable to copy. source=/var/sasdata/INPUT/GT/IFR_GH_MSTD_0069.xlsx
target=/var/sasdata/INPUT/LH/IFR_GH_MSTD_0069.xlsx rc3=-7440230
msg=WARNING: 3 records were truncated when the FCOPY function read from fileref FROM. 0 records were truncated when the FCOPY functi
on wrote to fileref TO. To prevent the truncation of records in future operations, you can increase the amount of space needed t
NOTE: There were 2 observations read from the data set WORK.FILES_TARGET_CHECK.

What would be likely cause for thsi error? After the execution of the program I want both .xlsx and .csv file to move to target folder but only .csv has moved. Program which I used is,

data files_moved;
  set files_target_check ;
  length rc1-rc4 8 msg $256 ;

  /*Copy and delete*/
  if not (scan(lowcase(filename),5,'_') in ('5601','6010','6020')) or index(lowcase(filename),'_copied.csv ')  then do;
  rc1=filename('from',source);
  rc2=filename('to',target);
  rc3=fcopy('from','to');
  if rc3 then do;
    msg=sysmsg(); 
    put 'ERROR: Unable to copy. ' source= target= rc3= msg=;
  end;
  else do;
    rc4=fdelete('from');
  end;
  output;
  end;
  rc1=filename('from');
  rc2=filename('to');
run;

Data from files_target_check is,

filename	source	target	flag
IFR_GH_MSTD_0069.xlsx	/var/sasdata/INPUT/GT/IFR_GH_MSTD_0069.xlsx	/var/sasdata/INPUT/LH/IFR_GH_MSTD_0069.xlsx	1
IFR_GH_TRND_1_0069_1_20201009T075212.csv	/var/sasdata/INPUT/GT/IFR_GH_TRND_1_0069_1_20201009T075212.csv	/var/sasdata/INPUT/LH/IFR_GH_TRND_1_0069_1_20201009T075212.csv	1

[ballardw]

I suspect the "what" is that the xlsx file format is actually 1) a zip file and 2) multiple compressed XML files.

The "why" might be the use of the wrong function. From the documentation:

FCOPY Function

Copies records from one fileref to another fileref, and returns a value that indicates whether the records were successfully copied

Fcopy copies records, which in a simple text file such as CSV, are lines of data. XML is not like that (open  an XML in a text editor and see the difference) so I am not sure what Fcopy may see as a "record" in such.

[David_Billa]

Can I understand that issue might be with the file and the program is
right?

[Peter_C]

.xlsx files are binary files, so treat them that way - see this example in the online doc at
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarg...

[David_Billa]

@Peter_C @Tom can't we move .xlsx and .csv file in one single data step as shown below?

data files_moved;
  set files_target_check ;
  length rc1-rc4 8 msg $256 ;

  /*Copy and delete*/
  if not (scan(lowcase(filename),5,'_') in ('5601','6010','6020')) or index(lowcase(filename),'_copied.csv ')  then do;
  rc1=filename('from',source);
  rc2=filename('to',target);
  rc3=fcopy('from','to');
  if rc3 then do;
    msg=sysmsg(); 
    put 'ERROR: Unable to copy. ' source= target= rc3= msg=;
  end;
  else do;
    rc4=fdelete('from');
  end;
  output;
  end;
  rc1=filename('from');
  rc2=filename('to');
run;

[Kurt_Bremser]

Maxim 1: Read the Documentation.

Example 2 of the FCOPY Function shows how to copy a binary file. Important: use RECFM=N for the file reference.

Edit: changed the RECFM to N, as it is in the documentation (typo, my bad)

[David_Billa]

@Kurt_Bremser Is it recfm=n or recfm=f? How to use RECFM in my program below as I do not have filename statement (used only filename function) due to the fact that filename is dynamic. Sometimes I receive .xlsx and sometime .csv otherewise both CSV and Excel.

data files_moved;
  set files_target_check ;
  length rc1-rc4 8 msg $256 ;

  /*Copy and delete*/
  if not (scan(lowcase(filename),5,'_') in ('5601','6010','6020')) or index(lowcase(filename),'_copied.csv ')  then do;
  rc1=filename('from',source);
  rc2=filename('to',target);
  rc3=fcopy('from','to');
  if rc3 then do;
    msg=sysmsg(); 
    put 'ERROR: Unable to copy. ' source= target= rc3= msg=;
  end;
  else do;
    rc4=fdelete('from');
  end;
  output;
  end;
  rc1=filename('from');
  rc2=filename('to');
run;

[PaulCanals]

Hi, for copying files I generally use the byte-for-byte technique including setting the chunksize to speed up the copying process

   %*-------------------------------------------------------------------------;
   %* Set source and target (copy) path and file name values:                 ;
   %*-------------------------------------------------------------------------;

   %let infile = %str();
   %let outfile = %str();

   %*-------------------------------------------------------------------------;
   %* Set copy mode and chunksize variables:                                  ;
   %*-------------------------------------------------------------------------;

   %let mode = S ; /* I (chunksize is 1) / S (use chunksize value) */
   %let chunksize = 32767 ; /* 1 - 32767 */

   %*-------------------------------------------------------------------------;
   %* Copy INFILE to OUTFILE using the byte-for-byte method:                  ;
   %*-------------------------------------------------------------------------;

   filename in "&infile.";
   filename out "&outfile.";

   data _null_;

      length 
         ifile  8 
         ofile  8
         fmtlen 8
         bytes  8
         outfmt $ 32
         rec    $ &chunksize.
         ;

      ifile = fopen('in',"&mode.",&chunksize.,'B');
      ofile = fopen('out','O',&chunksize.,'B');
      bytes = 0;
      rec   = '20'x;

      do while(fread(ifile) eq 0);

         call missing(outfmt, rec);
         rc = fget(ifile,rec,&chunksize.);

         if "%upcase(&mode.)" eq "I" then do;
            fmtlen = 1;
            outfmt = 1;
         end;
         else do;
            fcolin = fcol(ifile);
            if (fcolin - &chunksize.) eq 1 then do;
              fmtlen = &chunksize.;
            end;
            else do;
              fmtlen = fcolin - 1;
            end;
            outfmt = cats("$char", fmtlen, ".");
         end;

         bytes + fmtlen;

         rc = fput(ofile,putc(rec,outfmt));
         rc = fwrite(ofile);

      end;

      call symputx('size', bytes);

      rc = fclose(ifile);
      rc = fclose(ofile);

   run;

   filename in clear;
   filename out clear;

   %put File %scan(&infile.,-1,/\) copied to %scan(&outfile.,-1,/\).;
   %put Transferred &size. Bytes.;

Best regards,

Paul

[Kurt_Bremser]

Sorry about the confusion, the RECFM has to be N (when there's a contradiction between me and the documentation, the documentation is most probably correct).

You need to supply the options in a separate parameter of the FILENAME function, and you can copy text files as binary without problems:

proc export
  data=sashelp.class
  file="/folders/myfolders/class.xlsx"
  dbms=xlsx
  replace
;
run;

proc export
  data=sashelp.class
  file="/folders/myfolders/class.csv"
  dbms=csv
  replace
;
run;

data _null_;
rc = filename("from","/folders/myfolders/class.xlsx","","recfm=n");
put rc=;
rc = filename("to","/folders/myfolders/test/class.xlsx","","recfm=n");
put rc=;
rc = fcopy("from","to");
put rc=;
rc = filename("from","/folders/myfolders/class.csv","","recfm=n");
put rc=;
rc = filename("to","/folders/myfolders/test/class.csv","","recfm=n");
put rc=;
rc = fcopy("from","to");
put rc=;
run;

proc import
  datafile="/folders/myfolders/test/class.xlsx"
  dbms=xlsx
  out=work.class1
  replace
;
run;

proc import
  datafile="/folders/myfolders/test/class.csv"
  dbms=csv
  out=work.class2
  replace
;
run;

This code runs without ERRORs or WARNINGs, and creates two (nearly) identical datasets in WORK.

The values are identical, but some attributes differ (formats, informats) because of PROC IMPORT.

[David_Billa]

@Kurt_Bremser what will happen if I use recfm=n for CSV files as I shown below? Because in real life I may transfer CSV or Excel file or both  as files are dynamic.

data _null_;
rc = filename("from","/folders/myfolders/class.csv","","recfm=n");
run;

[Kurt_Bremser]

The answer is in my previous post. Please read the code closely, you will find that I used the "binary" method for a csv file without problems.

[AllanBowe]

For reference we recently published a macro that will (binary) copy ALL files in a particular directory, and recursively so:  https://core.sasjs.io/mp__copyfolder_8sas.html