csv import fails to open all records in file

[sum_sand]

Any way to find out why few records are failing to open with SAS.

[SASKiwi]

Open the CSV file in a text editor like Notepad++ and look for malformed rows. Embedded carriage returns in text columns is one reason why this can occur. If the CSV is small and there are only a few rows to fix then manually editing it then reimporting it is the quickest approach. 

[sum_sand]

it's a large csv. Is there any way to find these embedded carriage returns - other than manual.

[Amir]

Hi,

Please provide more context as to what is happening, by "failing to open" do you mean some csv file records are not appearing in an output data set?

As well as what has already been said, what code have you used and what does your log show? Please use the "Insert SAS Code" and "Insert Code" icons, respectively.

Also how many records were you expecting and how many did you get?

If appropriate, are you able to provide a small example with data?

Notepad++ can be used to show non-printable characters in files, including end-of-line (EOL) characters, via pulldown menu View > Show Symbol > Show End of Line.

Thanks & kind regards,

Amir.

[ballardw]

You should also show the log with the generated data step created by Proc Import and ALL of the related messages or notes. Several of the conditions that can cause a behavior of incomplete import will provide clues in the LOG. Copy the text from the log and on the forum open a text box using the </> icon above the message box and paste the entire log.

Also, you should make sure that the GUESSINGROWS option is set to MAX. Otherwise the properties for all the variables and records are set by examining only  a few observations. If your data file is "large" it may well be that some of the observations are not included because of incomplete examination of the file by the procedure.

Note, if you have a description of the contents of the file and all of the fields you may want to share that with us as well so we can compare the Import generated data step for accuracy. Or write your own data step.

[Tom · Accepted solution]

---

@sum_sand wrote:

Any way to find out why few records are failing to open with SAS.

---

Not sure what that means.  If the file is being truncated that is usually caused by data step assuming it should stop when it sees the "DOS end of file" character.  There is an IGNOREDOSEOF option you can add to the INFILE statement to stop that.

https://communities.sas.com/t5/SAS-Programming/importing-data-stops-SAS-in-the-middle-of-a-textfile-...

But if your file has that character in it then you might have bigger issue since it is not something that would normally appear in a TEXT file, which CSV files are.

[sum_sand]

Thank you, Tom! 

IGNOREDOSEOF option infile statement helped to solve this issue!