DATA Step, Macro, Functions and more

Read all text files into one dataset

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Read all text files into one dataset

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


Accepted Solutions
Solution
‎04-15-2015 09:13 AM
Super User
Posts: 6,928

Re: Read all text files into one dataset

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: Read all text files into one dataset

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:

SAS Super FREQ
Posts: 682

Re: Read all text files into one dataset

Hi

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
Senior User
Posts: 1

Re: Read all text files into one dataset

Thank you @Bruno_SAS , 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. 

Solution
‎04-15-2015 09:13 AM
Super User
Posts: 6,928

Re: Read all text files into one dataset

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1411 views
  • 3 likes
  • 5 in conversation