DATA Step, Macro, Functions and more

Identify first and last record in a file when reading a list of text files

Accepted Solution Solved
Reply
PROC Star
Posts: 1,433
Accepted Solution

Identify first and last record in a file when reading a list of text files

[ Edited ]

Hi All,

 

I'm  reading a list of text files, and would like a way to identify whether a record I am reading is the first record of a file or not, and whether it is the last record of a file or not.  I read the options for the infile statement, but can't seem to get what I want.

 

Sample have three files like:

file1.txt
1
2
3

file2.txt
40
50
60
70

file3.txt
800
900
1000

WANT data like:

 

  id    first   last

   1      1       0
   2      0       0
   3      0       1
  40      1       0
  50      0       0
  60      0       0
  70      0       1
 800      1       0
 900      0       0
1000      0       1

 

 

I can get FIRST by comparing each filename to the lag of filename:

filename myfiles("d:\junk\file1.txt" "d:\junk\file2.txt" "d:\junk\file3.txt" );


data want; length filename $20; infile myfiles filename=filename; input id; first=filename ne lag(filename); * last= ??? ; run;

 

 

But how can I get LAST?  Do I need to do some sort of lookahead?

 

Big picture, before I start reading from a new file, I want to do some setup stuff.  After I have read the last record from a file I want to do some post processing stuff.


Accepted Solutions
Solution
‎10-25-2017 04:02 PM
Respected Advisor
Posts: 3,832

Re: Identify first and last record in a file when reading a list of text files


Quentin wrote:

Thanks @data_null__,

 

That gives it a nice DoW loop structure to allow preprocessing and postprocessing of each file.  Should be cleaner than all the RETAINING I have been doing.

 

Something like:

data want;
   length myfile $ 300;
   input myfile $ ;
   infile dummy filevar=myfile end=done; 

   put "Pre-processing " myfile=; 
   do while(not done);
     input id ;
     put (id myfile)(=);
     output;
   end;
   put "Post-processing " myfile=;

   datalines;
d:\junk\file1.txt
d:\junk\file2.txt
d:\junk\file3.txt
;

Yes a lot less fiddly. :-)   You could even use an INFILE DUMMY PIPE FILEVAR='command to return file names like DIR' in place of the names you are reading from CARDS.   Perhaps more useful if you have many file to read.

View solution in original post


All Replies
Respected Advisor
Posts: 3,832

Re: Identify first and last record in a file when reading a list of text files

Consider using INFILE statement option FILEVAR where you can use END to detect EOF.
PROC Star
Posts: 1,433

Re: Identify first and last record in a file when reading a list of text files

Posted in reply to data_null__

Thanks @data_null__,

 

That gives it a nice DoW loop structure to allow preprocessing and postprocessing of each file.  Should be cleaner than all the RETAINING I have been doing.

 

Something like:

data want;
   length myfile $ 300;
   input myfile $ ;
   infile dummy filevar=myfile end=done; 

   put "Pre-processing " myfile=; 
   do while(not done);
     input id ;
     put (id myfile)(=);
     output;
   end;
   put "Post-processing " myfile=;

   datalines;
d:\junk\file1.txt
d:\junk\file2.txt
d:\junk\file3.txt
;
Solution
‎10-25-2017 04:02 PM
Respected Advisor
Posts: 3,832

Re: Identify first and last record in a file when reading a list of text files


Quentin wrote:

Thanks @data_null__,

 

That gives it a nice DoW loop structure to allow preprocessing and postprocessing of each file.  Should be cleaner than all the RETAINING I have been doing.

 

Something like:

data want;
   length myfile $ 300;
   input myfile $ ;
   infile dummy filevar=myfile end=done; 

   put "Pre-processing " myfile=; 
   do while(not done);
     input id ;
     put (id myfile)(=);
     output;
   end;
   put "Post-processing " myfile=;

   datalines;
d:\junk\file1.txt
d:\junk\file2.txt
d:\junk\file3.txt
;

Yes a lot less fiddly. :-)   You could even use an INFILE DUMMY PIPE FILEVAR='command to return file names like DIR' in place of the names you are reading from CARDS.   Perhaps more useful if you have many file to read.

Trusted Advisor
Posts: 1,288

Re: Identify first and last record in a file when reading a list of text files

Although @data_null__'s reply is an elegant use of the FILEVAR and EOF options, there is a relatively simple way to look-ahead for an incoming file change.

 

Just add a second FILENAME statement with the same input as the primary filename statement.  Then, in the data step, use it in a 2nd INFILE statement with a FIRSTOBS=2 option, and followed by a dummy INPUT statement:

 

filename myfiles ('c:\temp\file1.txt','c:\temp\file2.txt','c:\temp\file3.txt');
filename myfiles2 (myfiles);

data want ;
  length _fvar _fvar2 $24;
  infile myfiles filename=_fvar end=_end;
  input x  ;

  infile myfiles2 filename=_fvar2 firstobs=2;
  if _end=0 then input ;
  else _fvar2=' ';

  begin=(_fvar^=lag(_fvar));
  end=(_fvar^=_fvar2);
run;
PROC Star
Posts: 1,433

Re: Identify first and last record in a file when reading a list of text files

Thanks @mkeintz

 

I was thinking there must be an infile option that does what I had hoped.  That not being there, your approach is what I was trying to come up with.  Looks like the same as one of the  "standard" look-ahead approaches for SAS datasets, I just failed to make it work with infile.

 

Sadly I missed your leads and lags talk at BASUG earlier this year.  If I had seen I'd, I'm sure I would have learned this and more.  : )

 

-Q.

Trusted Advisor
Posts: 1,288

Re: Identify first and last record in a file when reading a list of text files

@Quentin:  I'm giving the Lags and Leads talk again at SESUG next month.  If you're coming please find me.

 

PROC Star
Posts: 1,433

Re: Identify first and last record in a file when reading a list of text files

@mkeintz Excellent! I'll be there, and will definitely track you down.
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 200 views
  • 7 likes
  • 3 in conversation