topic Re: Read all text files into one dataset in SAS Programming

Read all text files into one dataset

yashpande — Wed, 15 Apr 2015 12:46:57 GMT

I have got several txt files on unix platform. Names are different but structure is same. I need to create only one dataset of all these files and that should have data from all.

My below one datastep works fine but i am unable to somehow get all files together in one dataset. and I also want one column which says the name of the text file from which this data is coming

Data new;

infile "/gpfs1/ana_layer/file1.txt' ;

input @1 id $3 @5 age;

run;

this works correctly for one file but i am unable to generalise it for the entire directory and also unable to get the filename as one more column in the resultant dataset

Thanks a lot in advance

Re: Read all text files into one dataset

RW9 — Wed, 15 Apr 2015 13:03:38 GMT

Hi,

There are at least 30 separate topics using a variety of methods on these forums. Just search for "read multiple files from directory".

Example:

Re: Read all text files into one dataset

BrunoMueller — Wed, 15 Apr 2015 13:04:49 GMT

the INFILE statement supports wildcards, the FILENAME= option names a variable that will contain the name of the file that a records was read from. Please note, that the variable named by the FILENAME= option is dropped automatically, so you need to assign the value to some other variable. See also example 5 in the doc for more ideas on the list of files to read SAS(R) 9.4 Statements: Reference, Third Edition

data someData;
  length _xFilename $ 256; 
  infile "c:\temp\*.csv" filename=_xfilename;
  xFilename = _xFilename;
  input
    @1 line $10.
  ;
run;

bruno

Re: Read all text files into one dataset

Kurt_Bremser — Wed, 15 Apr 2015 13:13:47 GMT

Try this:

%let path=where_your_file_are_stored;

%let filnam=common_part_of_filename;

filename oscmd pipe "ls &path./&filnam.*";

data want;

infile oscmd;

length filnam myfilename filnamstore $ 300;

input filnam;

infile in filevar=filnam filename=myfilename end=done;

do while (not done);

*read external file as usual;

filnamstore=myfilename;

output;

end;

run;

Built upon the example in Statements: INFILE Statement - 9.2

Re: Read all text files into one dataset

rajneelram87 — Tue, 27 Jun 2017 23:10:20 GMT

Thank you @BrunoMueller , i tried your code, made very small changes and it works! I was able to pull in several flat files as i was hoping for.