topic Re: Copy or Move the file based on file name in SAS Programming

Copy or Move the file based on file name

David_Billa — Mon, 18 Jan 2021 11:29:06 GMT

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.

Re: Copy or Move the file based on file name

PeterClemmensen — Mon, 18 Jan 2021 10:58:59 GMT

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

Re: Copy or Move the file based on file name

David_Billa — Mon, 18 Jan 2021 11:05:18 GMT

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.

Re: Copy or Move the file based on file name

Ksharp — Mon, 18 Jan 2021 11:13:03 GMT

Check function FCOPY() FDELETE() .

Re: Copy or Move the file based on file name

David_Billa — Mon, 18 Jan 2021 11:32:08 GMT

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

Re: Copy or Move the file based on file name

PeterClemmensen — Mon, 18 Jan 2021 11:53:55 GMT

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

Re: Copy or Move the file based on file name

Ksharp — Mon, 18 Jan 2021 12:03:20 GMT

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 .

Re: Copy or Move the file based on file name

David_Billa — Mon, 18 Jan 2021 17:11:13 GMT

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

Re: Copy or Move the file based on file name

singhsahab — Mon, 18 Jan 2021 18:24:26 GMT

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

Re: Copy or Move the file based on file name

Tom — Mon, 18 Jan 2021 18:27:16 GMT

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;

Re: Copy or Move the file based on file name

David_Billa — Tue, 19 Jan 2021 07:05:55 GMT

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

ERROR: No logical assign for filename

Re: Copy or Move the file based on file name

Kurt_Bremser — Tue, 19 Jan 2021 09:07:25 GMT

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

Re: Copy or Move the file based on file name

David_Billa — Tue, 19 Jan 2021 09:18:04 GMT

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

Re: Copy or Move the file based on file name

Kurt_Bremser — Tue, 19 Jan 2021 09:27:46 GMT

Ah, I see. This statement needs quotes:

  did=dopen("source");

Re: Copy or Move the file based on file name

Babloo — Tue, 19 Jan 2021 10:23:31 GMT

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

Re: Copy or Move the file based on file name

Kurt_Bremser — Tue, 19 Jan 2021 11:29:02 GMT

Run this:

filename source "&inpath";

data files ;
  length msg filename $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);
    output;
  end;
run;

and look at the contents of dataset FILES. You will find that the value is not caught by any of your conditions, so SOURCE and TARGET stay empty.

Re: Copy or Move the file based on file name

David_Billa — Tue, 19 Jan 2021 12:06:49 GMT

@Kurt_Bremser - I've corrected the condition in 'else if' statements but still source and target value is empty in 'files' dataset. Any idea why still it's empty as the filename and macro variable value is statisfying the below else if?

File Name is IFR_ITM_TRN_2_6010_1_20201009T075212_copied.csv

Macro variable value which I passed in the program is,

%let function=NL;

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

Re: Copy or Move the file based on file name

Kurt_Bremser — Tue, 19 Jan 2021 12:46:32 GMT

If your filename is actually that, source and target CANNOT be empty:

%let function=NL;

%let inpath=/var/sasdata/GTM;
%let path1=/var/sasdata/LH;
%let path2=/var/sasdata/NL_GTM;

data test;
filename = "IFR_ITM_TRN_2_6010_1_20201009T075212_copied.csv";
    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;
      /*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;
      else target="&path1"||"/"||filename;
    end;
    output;
put source= target=;
run;

Log excerpt:

 105        put source= target=;
 106        run;
 
 source=/var/sasdata/GTM/IFR_ITM_TRN_2_6010_1_20201009T075212_copied.csv
 target=/var/sasdata/LHIFR_ITM_TRN_2_6010_1_20201009T075212_copied_copied.csv

Run the data step I gave you earlier. Then take the dataset, and apply your conditions. If that does not do it, create your dataset as a data step with datalines (you can use the macro from my footnotes for that), and rerun the code. If that still does result in empty variables, post both steps here.

Re: Copy or Move the file based on file name

David_Billa — Tue, 19 Jan 2021 14:28:48 GMT

@Kurt_Bremser @Tom What will be best idea to remove the string '_copied' from the filename in the target folder?

Re: Copy or Move the file based on file name

David_Billa — Tue, 19 Jan 2021 14:42:08 GMT

@Kurt_Bremser Still SOURCE and TARGET value is empty even after simplying the last 'else if'. My code is,

%let function=NL;   

%let inpath=/var/sasdata/GTM;
%let path1=/var/sasdata/LH;
%let path2=/var/sasdata/NL_GTM;

filename source "&inpath";

data files ;
  length msg filename $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);
    output;
  end;
  did=dclose(did);
run;

data files_;
  set files;
    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;
      /*added*/
      else if  index(lowcase(filename),'_copied.csv ') ne 0 then do;
      source="&inpath" ||"/"|| filename;
      target="&path2"||"/"||filename;
      end;

      else target="&path1"||"/"||filename;
    end;
    output;
    put source= target=;
run;

Last else if should have executed for my file as it has the string '_copied'.

Log:

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         
35         
36         
37         data files ;
38           length msg filename $256 ;
39           did=dopen("source");
40           if did<=0 then do;
41               msg=sysmsg();
42               put msg;
43               stop;
44           end;
45           do id=1 to dnum(did);
46             filename=dread(did,id);
47             output;
48           end;
49           did=dclose(did);
50         run;

NOTE: The data set WORK.FILES has 1 observations and 4 variables.
51         
52         data files_;
53           set files;
54             if scan(lowcase(filename),-1,'.')='csv' and not index(lowcase(filename),'_copied.csv ') then do;
55               source="&inpath" ||"/"|| filename;
56               if indexw(filename,'5601','_') or indexw(filename,'6010','_') or indexw(filename,'6020','_') then do;
57                 target="&path1"||substr(filename,1,length(filename)-4)||'_copied.csv';
58               end;
59               else if indexw(filename,'0169','_') or indexw(filename,'1070','_') or indexw(filename,'0417','_') then do;
60                 target="&path2"||"/"||filename;
61               end;
62               /*added*/
63               else if  index(lowcase(filename),'_copied.csv ') ne 0 then do;
64               source="&inpath" ||"/"|| filename;
65               target="&path2"||"/"||filename;
66               end;
67               /*added*/
68         /*      else if  index(lowcase(filename),'_copied.csv ')=0  then do;*/
69         /*      source="&inpath" ||"/"|| filename;*/
70         /*      target="&path1"||"/"||filename;*/
71         /*      end;*/
72               else target="&path1"||"/"||filename;
73             end;
74             output;
75             put source= target=;
76         /*  end;*/
77         /*  did=dclose(did);*/
78         /*  drop did msg;*/
79         run;

SOURCE=  TARGET= 
NOTE: There were 1 observations read from the data set WORK.FILES.
NOTE: The data set WORK.FILES_ has 1 observations and 6 variables.