topic Re: Move multiple files with different names between SAS server folders without using xcopy in SAS Programming

Move multiple files with different names between SAS server folders without using xcopy

Abelp9 — Wed, 02 Mar 2022 12:03:00 GMT

Hello everyone, I'm new to programming in SAS and I'm trying to create a macro that moves the 160 .xlsx files that are generated in one folder, to another, within the SAS server. The names of the files are different and it cannot be use the wildcard "*" to name all the files, that's why I had thought of a macro with macrovariables.

What I intend to do is the following, The file names always start with "XDF_" followed by a number (for example: 0001475) this number is a field called cod_SAP from another table, then the date would go (I have no problem with this ) then it would go again "_XDF_M2_"month in number format, type 202202".xlsx.

That is, if we pass this to macrovariables it would be something like this:

XDF_&column._&date._XDF_M2_&month..xlsx

And a file would be for example:
XDF_0001475_20220302_XDF_M2_202203.xlsx

They will all have the same value in the date and month macrovariable, what will change will be the codes. There are 160 different codes so I have 160 .xlsx files that I want to move.

Here is the code I have been testing:

 data tabletest;
	set origintable;
	Rank + 1;
	if first.test then Rank = 1;
run;
PROC SQL;
CREATE TABLE TEST1 as
SELECT DISTINCT cod_SAP, Rank
FROM TABLETEST;
QUIT;
data _null_;
	set TEST1;
	call symput ('codes',_n_);
run;
%macro testmove;
%global month;
%let month = 202112;
%global date;
%let date= 20220302;

%do i=1 %to &codes; 

data _null_;
	set tabletest(where=(rank=&i.));
	call symput ('column',compress(cod_SAP));
run;

filename in "/opt/sas/data/xxx/xx/xx/xxx/FinalFolder/XDF_&column._&date._XDF_M2_&month..xlsx";
filename out "/opt/sas/data/xx/xx/xx/xx/BridgeFolder/XDF_&column._&date._XDF_M2_&month..xlsx";

            * copy the file byte-for-byte;
            data _null_;
             length filein 8 fileid 8;
             filein = fopen('in','I',1,'B');
             fileid = fopen('out','O',1,'B');
             rec = '20'x;
             do while(fread(filein)=0);
                rc = fget(filein,rec,1);
                rc = fput(fileid, rec);
                rc =fwrite(fileid);
             end;
             rc = fclose(filein);
             rc = fclose(fileid);
            run;

            filename in clear;
            filename out clear;

%end;
%mend testmove;
%testmove;

What happens with this code is that it simply copies 5 files and sometimes it does it when they are empty, it only copies the empty excel and with the name that is in the source folder, could someone tell me where the error would be? or any other alternative to try to move 160 excel files with different names to another folder automatically?

I can't use xcopy, I have to do it with the noxcmd option enabled

Thank you very much in advance

Re: Move multiple files with different names between SAS server folders without using xcopy

Kurt_Bremser — Wed, 02 Mar 2022 12:49:06 GMT

Read the filenames from the directory, and use FCOPY:

data _null_;
length
  fref_in fref_out dref $8
  fname $200
;
rc = filename(dref,"/opt/sas/data/xxx/xx/xx/xxx/FinalFolder");
did = dopen(dref);
if did /* if did is not 0, indicating the dopen worked */
then do;
  do i = 1 to dnum(did);
    fname = dread(did,i);
    if /* insert filter condition for fname here */
    then do;
    rc = filename(fref_in,"/opt/sas/data/xxx/xx/xx/xxx/FinalFolder/" !! fname, "recfm=n");
    rc = filename(fref_out,"/opt/sas/data/xxx/xx/xx/xxx/BridgeFolder/" !! fname, "recfm=n");
    rc = fcopy(fref_in,fref_out);
    rc = filename(fref_in);
    rc = filename(fref_out);
  end;
  rc = dclose(did);
end;
rc = filename(dref);
run;

Re: Move multiple files with different names between SAS server folders without using xcopy

SASJedi — Wed, 02 Mar 2022 13:48:12 GMT

I have a pre-canned macro for this named filemove. You can get it like this:

/* Grab a copy of the filemove macro */
%let thisMacro=filemove;
filename mCode "c:\temp\%qlowcase(&thisMacro).sas";
proc http url="https://raw.githubusercontent.com/SASJedi/sas-macros/master/%qlowcase(&thisMacro).sas"
          out=mcode;
run;

%include mcode /source2;
filename mcode;
%&thismacro(?)

The macro call with the question mark writes documentation to the log:

NOTE: FILEMOVE documentation: Purpose: Move a file from one directory to another Syntax: %FILEMOVE(fileName, sourcePath,targetPath<,newFileName>) fileName: Name of the file to be copied sourcePath: path where the source file is located targetPath: path to which the file will be moved newFileName: OPTIONAL: New name for the copied file Examples: %FILEMOVE(abc.txt,c:\temp\source,c:\temp\target) %FILEMOVE(old.csv,c:\temp\source,c:\temp\target,new.csv) Use ? to print documentation to the SAS log.

To copy your file from one directory to another, something like this would do:

%fileMove(XDF_&column._&date._XDF_M2_&month..xlsx
         ,/opt/sas/data/xxx/xx/xx/xxx/FinalFolder
         ,/opt/sas/data/xx/xx/xx/xx/BridgeFolder);