topic csv import fails to open all records in file in SAS Programming

csv import fails to open all records in file

sum_sand — Mon, 12 May 2025 19:22:29 GMT

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

Re: csv import fails to open all records in file

SASKiwi — Mon, 12 May 2025 20:05:21 GMT

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.

Re: csv import fails to open all records in file

sum_sand — Mon, 12 May 2025 22:25:26 GMT

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

Re: csv import fails to open all records in file

Amir — Mon, 12 May 2025 22:29:20 GMT

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.

Re: csv import fails to open all records in file

ballardw — Mon, 12 May 2025 22:41:13 GMT

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.

Re: csv import fails to open all records in file

Tom — Tue, 13 May 2025 01:59:51 GMT

@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-without/td-p/652791

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.

Re: csv import fails to open all records in file

sum_sand — Tue, 13 May 2025 20:37:12 GMT

Thank you, Tom!

IGNOREDOSEOF option infile statement helped to solve this issue!