BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jdebru
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Patrick
Opal | Level 21

@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
Super User Tom
Super User

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
Opal | Level 21

@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

jdebru
Calcite | Level 5

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
Opal | Level 21

@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
SAS Employee
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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2907 views
  • 2 likes
  • 5 in conversation