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

File Reader Skipping Rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

File Reader Skipping Rows

I am sure it is in front of my face, but for some reason my file reader is only reading every other row. I am reading a tab delimeted data source into one column and loading it into a DB. If there are 10 columns of tab delimeted data, I am loading it into 1. If there are 1000 rows in the file, I should have 1000 rows in the DB. It works perfectly except it is only for every other row. I dropped a flat file and it is identical to what is loaded into the DB. My job is nothing more than the file->file read -> table loader -> table. Am I missing something obvious?


Accepted Solutions
Solution
‎04-23-2018 11:49 AM
Super User
Super User
Posts: 7,932

Re: File Reader Skipping Rows

Are you reading past the end of the line?  Perhaps you told it to read 5 values from each line but the data file only has 4 values on each line.

 

If you are running a data step to read from a text file and try to read more data than is on the line SAS will hunt to the next line to find data.  You can prevent this using the TRUNCOVER option on the INFILE statement.

 

Not sure if the File Reader task you are doing lets you control that.

View solution in original post


All Replies
Respected Advisor
Posts: 4,672

Re: File Reader Skipping Rows

[ Edited ]

@jdebru

You must be missing something fundamental but without seeing code or log or source data it's simply impossible for us to tell you what unless someone experienced in the past exactly the same and remembers what the issue was.

 

Have you checked the SAS log already? Is there anything unusual in it that could give you a hint what's missing?

 

I'd check first that LRECL is long enough and that I'm using the right delimiter.

I'd probably also check the source file for special characters and for end of line indicators used - is it LF or CRLF and is it consistent.

Solution
‎04-23-2018 11:49 AM
Super User
Super User
Posts: 7,932

Re: File Reader Skipping Rows

Are you reading past the end of the line?  Perhaps you told it to read 5 values from each line but the data file only has 4 values on each line.

 

If you are running a data step to read from a text file and try to read more data than is on the line SAS will hunt to the next line to find data.  You can prevent this using the TRUNCOVER option on the INFILE statement.

 

Not sure if the File Reader task you are doing lets you control that.

Respected Advisor
Posts: 4,672

Re: File Reader Skipping Rows

@jdebru

What @Tom mentions could be the reason for what you observe. The SAS log should tell you if SAS "jumped" to a new line when it reached the end of the current line so if you see some message like that in the log then you know what's happening.

 

To avoid such behavior you need a MISSOVER or TRUNCOVER generated as part of your INFILE statement (default is FLOWOVER).

You get SAS DIS to generate a MISSOVER by defining the following for your External File Metadata object

Capture.JPG

Occasional Contributor
Posts: 6

Re: File Reader Skipping Rows

That worked! I was already having to override the INFILE creation in DI studio so that I could pull the filename in. Popped truncover in there and it worked fine. Thank you all for your help!

   length _infilename FilenameUsed $500;
   infile 'G:\blahblahblah\BaseTableLoads\*.txt'
          lrecl = 4000
          firstobs = 1
          truncover
          filename=_infilename;
   FilenameUsed=_infilename;
Respected Advisor
Posts: 4,672

Re: File Reader Skipping Rows

@jdebru

The filename you've posted is something you could also define in your External File Metadata so you don't have to overwrite the generated code.

The pathname doesn't have to be a fully qualified path but can be anything even a SAS macro variable (just make sure you then also select double quoting of the filename).

Super User
Posts: 9,886

Re: File Reader Skipping Rows

Look at the log (Maxim 2). You'll see a "SAS went to a new line" note, and probably also a LOST CARD. Use the truncover option, as already advised.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 291 views
  • 2 likes
  • 4 in conversation