topic Re: import multiple files in multiple folders with the same data structure into one file in SAS Programming

import multiple files in multiple folders with the same data structure into one file

fengyuwuzu — Wed, 24 Feb 2016 20:25:01 GMT

I have 637 folders, which contain 12,256 csv files. each folder may have different mumber of csv files.

each file is for one person. They all have the same header columns in all csv files.

I can important one file correctly. so based on that, I may be able to import and append all of them into one sungle file.

Can anyone kindly give me a template how to set up the loops to loop through all the folders and all the files in each folder?

Thank you very much

Re: import multiple files in multiple folders with the same data structure into one file

Reeza — Wed, 24 Feb 2016 20:33:56 GMT

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

Re: import multiple files in multiple folders with the same data structure into one file

fengyuwuzu — Wed, 24 Feb 2016 20:37:15 GMT

I read that post recently. Actually I used it successully when reading files in the same folder.

Now my task is more difficult, because I need to loop the folders as well.

Re: import multiple files in multiple folders with the same data structure into one file

Reeza — Wed, 24 Feb 2016 20:39:24 GMT

You'll need some modifications to the macro I linked to.

1. Get a list of files into a dataset, use the OS to list files. There's quite a few samples on here so a short search should pop up something.

2. Create a reference to a file - dummy file not used.

file my_file 'myfile.txt';

3. SET the data set from #1 into the data set

4. Modify the infile statement to use the variable from #1 that contains the file path using the filevar option.

infile my_file eov=eov filevar=myfile filename=filename truncover;

Re: import multiple files in multiple folders with the same data structure into one file

Reeza — Wed, 24 Feb 2016 21:19:27 GMT

I updated the answer to account for this. If you have issues, post your full code and error.

Re: import multiple files in multiple folders with the same data structure into one file

fengyuwuzu — Wed, 24 Feb 2016 21:21:04 GMT

I willl give it a try and see how it goes.

Thank you very much!!

Re: import multiple files in multiple folders with the same data structure into one file

data_null__ — Wed, 24 Feb 2016 21:49:08 GMT

How about a combination of FILEVAR and wildcard? These folder names are UNIX. I forget your OS. You can use programming (not shown) to create your 600 or so folder paths.


data folders;
   input folder $80.;
   cards;
~/folder01
~/folder02
;;;;
   run;
data _null_;
   set folders;
   length filename filevar $128;
   filevar=catx('/',folder,'*.csv');
   put filevar=;
   infile dummy filevar=filevar filename=filename eov=eov end=eof;
   do _n_ = 1 by 1 while(not eof);
      input @;
      if _n_ eq 1 or eof then do;
         put 'NOTE: ' filename= eov=;
         end;
      input;
      end;
   run;

Re: import multiple files in multiple folders with the same data structure into one file

fengyuwuzu — Thu, 25 Feb 2016 06:03:54 GMT

I found a good macro to loop all folders http://support.sas.com/kb/45/805.html