topic Re: Don't know what to put as title in SAS Programming

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

sks521 — Tue, 20 Aug 2019 15:04:34 GMT

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

Re: Don't know what to put as title

PeterClemmensen — Tue, 20 Aug 2019 11:04:49 GMT

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

Re: Don't know what to put as title

sks521 — Tue, 20 Aug 2019 11:17:17 GMT

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

Re: Don't know what to put as title

r_behata — Tue, 20 Aug 2019 11:25:32 GMT

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.

Re: Don't know what to put as title

sks521 — Tue, 20 Aug 2019 11:29:32 GMT

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

Re: Don't know what to put as title

Kurt_Bremser — Tue, 20 Aug 2019 11:43:52 GMT

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.

Re: Don't know what to put as title

Kurt_Bremser — Tue, 20 Aug 2019 11:45:36 GMT

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

Re: Don't know what to put as title

Patrick — Tue, 20 Aug 2019 11:47:29 GMT

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

Re: Don't know what to put as title

ballardw — Tue, 20 Aug 2019 14:40:12 GMT

@Kurt_Bremser wrote:

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

Or "Losing lines when editing CSV with Excel"