Importing CSV issue - number of records read does not match input file

[sks521]

Hi,

I've come a cross a very strange problem. I have a data set with 1536914 observations but when I delete a few unnecessary variables in the original 'csv' data file and import it into SAS, it only reads  1048575 observations. Can someone guess what the problem might be please?

Thanks

S

[PeterClemmensen]

How do you import it into SAS? The number 1048575 sounds remarkably close to the maximum number of rows in an Excel sheet 🙂

[sks521]

Tried both Proc import and infile statements before and after deleting variables but can't get to the bottom of the problem.

[r_behata]

Hope you have a copy of the csv file saved . Instead of deleting columns from the source try not to read those unnecessary columns into sas .

Were you modifying the file from excel or text editor ? the text editor could be wrapping single row into multiple.

[sks521]

Yes, I have the original csv saved and will try to not read those variables in SAS. I was deleting them from excel editor.

But can I check in if its possible that the data file I have received is csv which can only accommodate a maximum of 1,048,576 rows but when I import the same file into SAS, it gives me 1536914 observations?

thanks

[Kurt_Bremser]

DO NOT (never ever, not even when hell freezes over) inspect (or edit) a csv file (which is text) with MS Excel. NEVER. Excel has severe limitations because of its spreadsheet nature.

Use a proper text editor (like Notepad++), which has no limits on lines (until it runs out of RAM, of course, but it will dutifully report that).

And as others have said, read the original file with SAS and just drop the unwanted columns in the data step.

[Patrick]

@sks521 

A csv is a text file and it can have more than 1,048,576 rows. It's your Excel version which can't read more than 1,048,576 rows from the csv while SAS can read them all. That likely explains the difference you observe.

[Kurt_Bremser]

PS How about "Losing lines when importing CSV" as a title?

[ballardw]

---

@Kurt_Bremser wrote:

PS How about "Losing lines when importing CSV" as a title?

---

Or "Losing lines when editing CSV with Excel"