Import csv, SAS recognising as only 1 line of data?

[mattbnorris]

filename csvnew '\\mydrive\DATA.csv';

DATA mydata.; 
    LENGTH
		VAR1 	$ 4
		VAR2	$ 4
		VAR3	$ 1
		VAR4	$ 3
		VAR5	$ 1
		VAR6	$ 1
		VAR7	$ 6
		VAR8 	$ 6
		VAR9	$ 1;
    FORMAT
		VAR1	$4.
		VAR2	$4.
		VAR3	$1.
		VAR4	$3.
		VAR5	$1.
		VAR6	1.
		VAR7 	6.
		VAR8 	6.
		VAR9	1.;
    INFORMAT
		VAR1	$4.
		VAR2	$4.
		VAR3	$1.
		VAR4	$3.
		VAR5	$1.
		VAR6	1.
		VAR7 	6.
		VAR8 	6.
		VAR9	1.;
    INFILE &csvnew.
        LRECL=67108864
        DLM='2C0D'x
        DSD firstobs=1;
    INPUT
		VAR1	$
		VAR2	$
		VAR3	$
		VAR4	$
		VAR5	$
		VAR6
		VAR7
		VAR8
		VAR9
                @@;
RUN;

Hello, attached is the code I've been using to import a csv that I've been receiving for years now. I thought this was working properly until I noticed some rows were missing. After loading the csv into a data model in excel I've realised there are 2,091,600 rows whereas SAS is only getting 1,950,158 rows. I have opened the CSV in notepad (too big to view in excel) and there is nothing to suggest why SAS would stop at this row - no missing row or strange characters or spaces; it just carries on like the rest of the CSV. There does seem to be something strange going on though because upon running the data step / infile shown, I get a "lost card" note for the record after row 1,950,158 and also it's reading the CSV as 1 line, hence why I think the "@@" is needed in the input section of the data step. Furthermore, I have tried using a simple proc import  but it imports the CSV as one row only with thousands of columns as if it's not recognising that there are multiple rows in the csv.

Anyone know how to help?

Thanks,

Matt

[Kurt_Bremser]

Open the file with a good programming text editor (like Notepad++). It will let you see the exact nature of column delimiters and line terminators.

Use an appropriate TERMSTR= option for the line terminator

Mac: CR

UNIX/Linux: LF

Windows/DOS: CRLF

[mattbnorris]

Thanks for the help TERMSTR=CR worked!

[Tom · Accepted solution]

Talk to the creator of the file and ask them what they changed.

If the file looks like ONE line to SAS then it is not using the normal end of line characters.

Try making this change:

filename csvnew '\\mydrive\DATA.csv' termstr=cr;

Or just LOOK at the beginning of the file and figure out for yourself what character(s) it uses to mark the end of the lines and what character it uses between the fields.

data _null_;
  infile csvnew lrecl=100 recfm=f obs=5 ;
  input;
  list;
run;

[mattbnorris]

Thanks for the help TERMSTR=CR

[Tom]

---

@mattbnorris wrote:
Thanks for the help TERMSTR=CR

---

If the creator is using Excel on a Mac ask them to be more careful when saving the spreadsheet to a CSV file.  For some reason Excel never adapted correctly to Apple switching to Unix in 2001.  So the first choice if offers for a CSV file writes files with CR as the end of line character.  But if they hunt a little longer in the list of formats they can find one that uses normal end of line characters.