SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reading Multiple Files with File Reader

Reply
Frequent Contributor
Posts: 85

Reading Multiple Files with File Reader

Hi Guys,

Is there anyway to import many files with File Reader... ??? We have several CSV files which we want to club together and then use File Reader .... Has anyone done something like this ???  Any ideas would be really helpful....

Respected Advisor
Posts: 3,156

Reading Multiple Files with File Reader

Posted in reply to forumsguy

I have tried using the following code to input multiple files:

data all;

infile csv1 end=end1;

do until (end1);

    input id $ score;

    output;

end;

infile csv2 end=end;

do until (end2);

    input id $ score;

    output;

end;

infile csv3 end=end3;

do until (end3);

    input id $ score;

    output;

end;

run;

or you can input them seperately , then 'union, set or append' them together.

I have seen a Macro that does scan your folder first, then feed one file at one time until it is all done.

HTH,

Haikuo

PROC Star
Posts: 7,467

Reading Multiple Files with File Reader

Posted in reply to forumsguy

While I haven't used the DI studio, I know that you can substitute user written code when using File Reader.  Take a look, e.g., at http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/viewer.htm#p1h7xnr4n6dmown11cey...

That part of the documentation talks about doing just such a thing using the infile statement.  And, since the infile statement can contain wild cards, if all of your files are in the same directory, you should be able to read them all in one run.

Super Contributor
Posts: 1,636

Reading Multiple Files with File Reader

Posted in reply to forumsguy

Is this helpful?

filename qtr1 (’c:\sasuser\month1.dat’’c:\sasuser\month2.dat’

’c:\sasuser\month3.dat’);

data work.firstqtr;

infile qtr1;

input Flight $ Origin $ Dest $

Date : date9. RevCargo : comma15.2;

run;

Linlin

Respected Advisor
Posts: 4,173

Re: Reading Multiple Files with File Reader

Posted in reply to forumsguy

Wouldn't have thought about using a wildcard in the infile statement (thanks Art) - but if your external files follow a naming pattern where this can be done then there is an easy solution:

Create 1 external file metadata and in tab "File location" give it a name with a wildcard.

Eg: if all your files are in folder /myfiles with names like test1.csv, test2.csv.... then give it in the external file object under "File location" the name /myfiles/test*.csv

After that just register this one external file metadata against a file reader (tried it and it works).

Frequent Contributor
Posts: 85

Reading Multiple Files with File Reader

Thank you Patrick for the reply.. I made a little variation and was able to produce output

Super User
Posts: 5,424

Reading Multiple Files with File Reader

Posted in reply to forumsguy

Other solutions is to specify an aggregate file location, similar to Linlins suggestion. Thios will lead to reading all files in the the specified directory.

Another way is to use the loop transform and input the desired file names to a File Reader job that appends data into a target table.

/Linus

Data never sleeps
SAS Employee RMP
SAS Employee
Posts: 52

Reading Multiple Files with File Reader

Posted in reply to forumsguy

advice from @LinusH would be the preferred method in DI Studio. Loop Transform.

Ask a Question
Discussion stats
  • 7 replies
  • 2279 views
  • 3 likes
  • 7 in conversation