File Reader Skipping Rows

[jdebru]

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?

[Patrick]

@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.

[Tom · Accepted solution]

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.

[Patrick]

@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

[jdebru]

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;

[Patrick]

@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).

[AngusLooney]

The way to supply a filename to a file reader is in the external file definition. If it varies, use a parameter on the file object.

There is hardly ever a good reason to override code in a node in DIS.

[Kurt_Bremser]

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.