Copy or Move the file based on file name

[David_Billa]

Everyday client will be placing files ('n' number of files) in one of the shared folder where SAS can access. Now I want to copy the files to '/var/nonlife'  if the file name contains 5601, 6010 or 6020 and rename the file name by adding a string 'copied' before .CSV (e.g. IFR_RIS_1_ACC_5601_1_20200118_copied.csv ) If the file name contains 0169, 1070 or 0417 it have to be moved to '/var/nonlife' otherwise it has to move to '/var/life'. If the file name has 'copied' then do not copy or move the file.

File name looks like,

IFR_RIS_1_ACC_1070_1_20200118.csv

IFR_RIS_1_ACC_5601_1_20200118.csv

IFR_RIS_1_ACC_1889_1_20200118.csv

Appreciate if someone of you help me understand to copy or move the file conditionally based on the file name.

[PeterClemmensen]

You don't want to do anything with the data in the CAV files, you just want to move them, correct?

[David_Billa]

Yes, I don't want to do anything with data in CSV files. I just want to copy/ move the file depends on the file name. 

[Ksharp]

Check function FCOPY() FDELETE() .

[David_Billa]

@Ksharp How to move files as fcopy is only for copy? Per initial post, I've to copy or move based on file names.

[PeterClemmensen]

@David_Billa try the approach given in the blog post How to use SAS DATA step to copy a file from anywhere 🙂

[Ksharp]

If you could use OS command ,that would be a easy task , just invoke OS command like MV CP .....
If not , after FCOPY() these files and using FDELETE() to delete the original files .
Write some data step code to pick up the filename you need to copy or move ,that is easy I think .

[David_Billa]

I was not allowed to use OS command to copy/move the files. I have to use
SAS functions or statements only.

[Tom]

If you cannot use OS commands then you will need to use SAS functions to both get the list of files and move the files.

There is no MOVE function, just FCOPY() and FDELETE(), so you will want to first use FCOPY() and if it works then use FDELETE() to remove the original file to simulate a MOVE operation.

To determine the target directory you can use the INDEXW() or FINDW() function to test if any of the strings exists between underscore delimiters in the filename.

To change the filename you might want to remove the extension and the append the new suffix and then the re-append the extension.

Let's start by getting the list of filenames to copy into a dataset.  That way we can look at the dataset to make sure the logic is working.

%let inpath=/var/source/ ;
%let path1=/var/life/;
%let path2=/var/nonlife/;

filename source "&inpath";

data files ;
  length id 8 msg filename source target $256 ;
  did=dopen(source);
  if did<=0 then do;
      msg=sysmsg(); 
      put msg; 
      stop;
  end;
  do id=1 to dnum(did);
    filename=dread(did,id);
    if scan(lowcase(filename),-1,'.')='csv' and not index(lowcase(filename),'_copied.csv ') then do;
      source="&inpath" || filename;
      if indexw(filename,'5601','_') or indexw(filename,'6010','_') or indexw(filename,'6020','_') then do;
        target="&path1"||substr(filename,1,length(filename)-4)||'_copied.csv';
      end;
      else if indexw(filename,'0169','_') or indexw(filename,'1070','_') or indexw(filename,'0417','_') then do;
        target="&path2"||filename ;
      end;
      else target="&path1"||filename;
    end;
    output;
  end;
  did=dclose(did);
  drop did msg;
run;

Once you have a list of SOURCE and TARGET filenames you can use that to do the "move".

data files_moved;
  set files ;
  length rc1-rc4 8 msg $256 ;
  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;
  rc1=filename('from');
  rc2=filename('to');
run;

[singhsahab]

@David_Billa i'm not sure how we can re-name the file automatically. But below code will help you to move and copy the files to new folder based on their names.you have to update the folder in below code.

FILENAME PATH=LOCATION OF ORIGINAL FILE FOLDER ;
data have;
 rc=filename("fileref","&PATH");
  my_dir=dopen("fileref");
  if my_dir > 0 then do;
   f_num=dnum(my_dir);
    do i=1 to f_num;
	  filename_=dread(my_dir,i);
     output;
	end;
  end;
  rc=dclose(my_dir);
run;

data w_1;
set have;
Fother_other=scan(scan(filename_,1,'.'),-3,'_');
o_path="ORIGINAL LOCATION";
m_path="MOVE FOLDER LOCATION";
C_path="COPY FOLDER LOCATION";
o_path1=cats(o_path,'\',filename_);
m_path1=cats(m_path,'\',filename_);
C_path1=cats(C_path,'\',filename_);
run;

proc sql noprint;
select  count(*)  into :Cc  from w_1 where Fother_other in ('5601','6010','6020');
select o_path1 into :CO_file1 - :CO_file%LEFT(%TRIM(&Cc)) from w_1 where Fother_other in ('5601','6010','6020');
select C_path1 into :CC_file1 - :CC_file%LEFT(%TRIM(&Cc)) from w_1 where Fother_other in ('5601','6010','6020');

select  count(*)  into :MC  from w_1 where Fother_other in ('0169','1070','0417');
select o_path1 into :MO_file1 - :MO_file%LEFT(%TRIM(&MC)) from w_1 where Fother_other in ('0169','1070','0417');
select m_path1 into :MM_file1 - :MM_file%LEFT(%TRIM(&MC)) from w_1 where Fother_other in ('0169','1070','0417');
quit;


%macro move;
%do i=1 %to &MC;
X "MOVE &&MO_file&I &&MM_file&I";
%END;
%do i=1 %to &CC;
X "COPY &&CO_file&I &&CC_file&I";
%END;
%MEND;

%MOVE;

[David_Billa]

@Tom I've got the error after running your program. Are we missing something?

ERROR: No logical assign for filename

[Kurt_Bremser]

As always, please post the complete log and not just the ERROR message. The message needs to be seen in its context.

[David_Billa]

@Kurt_Bremser  Here you go.

26         %let inpath=/var/sasdata/GTM;
27         %let path1=/var/sasdata/LH;
28         %let path2=/var/sasdata/NL_GTM;
29         
30         filename source "&inpath";
SYMBOLGEN:  Macro variable INPATH resolves to /var/sasdata/GTM
31         
32         data files ;
33           length id 8 msg filename source target $256 ;
34           did=dopen(source);
35           if did<=0 then do;
36               msg=sysmsg();
37               put msg;
38               stop;
39           end;
40           do id=1 to dnum(did);
41             filename=dread(did,id);
42             if scan(lowcase(filename),-1,'.')='csv' and not index(lowcase(filename),'_copied.csv ') then do;
43               source="&inpath" || filename;
SYMBOLGEN:  Macro variable INPATH resolves to /var/sasdata/GTM
44               if indexw(filename,'5601','_') or indexw(filename,'6010','_') or indexw(filename,'6020','_') then do;
45                 target="&path1"||substr(filename,1,length(filename)-4)||'_copied.csv';
SYMBOLGEN:  Macro variable PATH1 resolves to /var/sasdata/LH
46               end;
47               else if indexw(filename,'0169','_') or indexw(filename,'1070','_') or indexw(filename,'0417','_') then do;
48                 target="&path2"||filename ;
SYMBOLGEN:  Macro variable PATH2 resolves to /var/sasdata/NL_GTM
49               end;
50               else target="&path1"||filename;
SYMBOLGEN:  Macro variable PATH1 resolves to /var/sasdata/LH
51             end;
52             output;
53           end;
54           did=dclose(did);
55           drop did msg;
56         run;

ERROR: No logical assign for filename .

[Kurt_Bremser]

Ah, I see. This statement needs quotes:

  did=dopen("source");

[Babloo]

@Kurt_Bremser Your fix has worked, thank you.I do have one more question. I tried to add two more 'else if' condition in the program and also added one macro variable but it's not working. Objective is to move the file  to the destination folder based on the value of macro variable and if file name has string '_copied'. After copying it I've to remove the string '_copied' in the target folder. I yet to add this logic as I couldn't find any so far.

Source folder is always same for any files. Macro variable and else if which I added in the @Tom program are,

%let function=NL; 
 /*added*/
      else if scan(lowcase(filename),-1,'.')='csv' and  index(lowcase(filename),'_copied.csv ') and "&function" EQ 'NL' then do;
      source="&inpath" ||"/"|| filename;
      target="&path2"||"/"||filename;
      end;
      /*added*/
      else if scan(lowcase(filename),-1,'.')='csv' and  index(lowcase(filename),'_copied.csv ') and "&function" EQ 'LH' then do;
      source="&inpath" ||"/"|| filename;
      target="&path1"||"/"||filename;
      end;

Error message:

26         %let function=NL;
27 
28         %let inpath=/var/sasdata/GTM;
29         %let path1=/var/sasdata/LH;
30         %let path2=/var/sasdata/NL_GTM;
31         
32         filename source "&inpath";
33         
34         data files ;
35           length id 8 msg filename source target $256 ;
36           did=dopen("source");
37           if did<=0 then do;
38               msg=sysmsg();
39               put msg;
40               stop;
41           end;
42           do id=1 to dnum(did);
43             filename=dread(did,id);
44             if scan(lowcase(filename),-1,'.')='csv' and not index(lowcase(filename),'_copied.csv ') then do;
45               source="&inpath" ||"/"|| filename;
46               if indexw(filename,'5601','_') or indexw(filename,'6010','_') or indexw(filename,'6020','_') then do;
47                 target="&path1"||substr(filename,1,length(filename)-4)||'_copied.csv';
48               end;
49               else if indexw(filename,'0169','_') or indexw(filename,'1070','_') or indexw(filename,'0417','_') then do;
50                 target="&path2"||"/"||filename;
51               end;
52               /*added*/
53               else if scan(lowcase(filename),-1,'.')='csv' and  index(lowcase(filename),'_copied.csv ') and "&function" EQ 'NL'
53       ! then do;
54               source="&inpath" ||"/"|| filename;
55               target="&path2"||"/"||filename;
56               end;
2                                                         
57               /*added*/
58               else if scan(lowcase(filename),-1,'.')='csv' and  index(lowcase(filename),'_copied.csv ') and "&function" EQ 'LH'
58       ! then do;
59               source="&inpath" ||"/"|| filename;
60               target="&path1"||"/"||filename;
61               end;
62               else target="&path1"||"/"||filename;
63             end;
64             output;
65           end;
66           did=dclose(did);
67           drop did msg;
68         run;

NOTE: The data set WORK.FILES has 1 observations and 4 variables.
NOTE: Compressing data set WORK.FILES increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

69         
70         data files_moved;
71           set files ;
72           length rc1-rc4 8 msg $256 ;
73           rc1=filename('from',source);
74           rc2=filename('to',target);
75           rc3=fcopy('from','to');
76           if rc3 then do;
77             msg=sysmsg();
78             put 'ERROR: Unable to copy. ' source= target= rc3= msg=;
79           end;
80           else do;
81             rc4=fdelete('from');
82           end;
83           output;
84           rc1=filename('from');
85           rc2=filename('to');
86         run;

ERROR: Unable to copy. SOURCE=  TARGET=  RC3=20004 MSG=ERROR: No logical assign for filename FROM.
NOTE: There were 1 observations read from the data set WORK.FILES.
NOTE: The data set WORK.FILES_MOVED has 1 observations and 9 variables.
NOTE: Compressing data set WORK.FILES_MOVED increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

Any leads to help resolve this error and the logic to remove the string '_copied' from the filename in the target folder?