topic Converting Excel spreadsheet cells into x commands to copy files in SAS Programming

Converting Excel spreadsheet cells into x commands to copy files

Walternate — Fri, 12 Nov 2021 18:45:50 GMT

Hi all,

I have an Excel spreadsheet with 4 columns. Each of the last 3 columns is formatted like this:

Column 1 Column 2 Column 3 Column 4

Section 1 file_path different_path third path

1 file_name 1 different_fname 1 new_fname1

2 second_file_name 2 another_fname 2 new_fname2

3 more_fname 3 new_fname3

Section 2 etc.

Each file name (and extension) listed under a file path is located within that file path. What I want to do is use these values to build a series of x commands which will copy each file from its original location to a new location.

In a similar situation, I imported the Excel file using proc import and used a data _null_ step to set up a bunch of macro variables for each directory and file name using call symputx. However, in that case it was a bit simpler because there was a directory column and a corresponding file name column, so it was easy to just create macro variables for dir1 and fname1, dir2 and fname2, etc. This one is a bit more complex so I'm hoping to get some good ideas. What I need to end up with is:

x " copy &file_path.\&file_name. new_directory_not_in_spreadsheet\&fname1."

x " copy &file_path.\&second_file_name. new_directory_not_in_spreadsheet\&second_file_name."

etc. for each file path/file name combo in columns 2-4.

Thanks in advance!

Re: Converting Excel spreadsheet cells into x commands to copy files

ChrisNZ — Sat, 13 Nov 2021 02:34:20 GMT

Like this?

data _null_;
  set EXCL_IMPORT;
  call system('copy ' ||  catx('\',PATH,NAME) || ' ' || catx('\',PATH2,NAME2) );
run;

Tweak to suit.

Re: Converting Excel spreadsheet cells into x commands to copy files

Tom — Sat, 13 Nov 2021 04:21:24 GMT

It is hard to tell some your example what your data is, but it looks like the first observation per SECTION value has the directory names and then the subsequent observations have filenames.

So perhaps your dataset looks like this:

data have;
  length section $10 source dest1 dest2 $256 ;
  infile cards dsd dlm='|' truncover ;
  input section source dest1 dest2 ;
cards;
Section 1|c:\project1|d:\copy1|d:\copy2
Section 1|file1.sas|file2.sas|file3.sas
Section 1|file4.sas|file5.sas|file6.sas
Section 2|c:\project2|d:\copy3|d:\copy4
Section 2|file7.sas|file8.sas|file9.sas
;

In which case you can use a data step like this to generate and execute two copy commands for every secondary observation.

data commands;
  set have;
  by section;
  length cmd command1 command2 $600;
  if first.section then do;
     source_dir=source;
     dest_dir1=dest1;
     dest_dir2=dest2;
     retain source_dir dest_dir1 dest_dir2 ;
     delete;
  end;
  command1=catx(' ','copy',quote(catx('\',source_dir,source)),quote(catx('\',dest_dir1,dest1)));
  command2=catx(' ','copy',quote(catx('\',source_dir,source)),quote(catx('\',dest_dir2,dest2)));
  do cmd=command1,command2 ;
    infile cmd pipe filevar=cmd end=eof;
    do while (not eof);
      input;
      put _infile_;
    end;
  end;
run;

So for that example input here are the commands it would run.

Obs     section                         command1                                             command2

 1     Section 1    copy "c:\project1\file1.sas" "d:\copy1\file2.sas"    copy "c:\project1\file1.sas" "d:\copy2\file3.sas"
 2     Section 1    copy "c:\project1\file4.sas" "d:\copy1\file5.sas"    copy "c:\project1\file4.sas" "d:\copy2\file6.sas"
 3     Section 2    copy "c:\project2\file7.sas" "d:\copy3\file8.sas"    copy "c:\project2\file7.sas" "d:\copy4\file9.sas"