I am running PROC IMPORT on a CSV file. My CSV file opens fine in MS Excel. However, the newline characters inside the quoted cells seem to cause PROC IMPORT to do the wrong thing. PROC IMPORT is seeing an end of line and assuming "next record".
Here is the code I am running
PROC IMPORT OUT= WORK.PAPER3
DATAFILE= "C:\Documents and Settings\jleveille\Desktop\paperauthor.csv"
Uh, I think you have misunderstood me. The newlines are part of the data. They should be in there. No reason to hate Microsoft here. PROC IMPORT is doing the wrong thing. The quoted string does not end, but the record does because of the newline. The quoted field carrys over to the next record in the file. When I open the CSV in Microsoft it opens properly.
Here is an example of the CSV file
"Bob", "123 Sycamore Ln"
"Fred", "200 Main St
Notice that the second address contains a line break. PROC IMPORT chokes on this.
As for the solution from Mike, I realize that I can do what you describe to open and save, however, this presents another problem. MS Excel assumes that the CSV is ASCII data which it isn't. It is a UTF-8 file. So opening CSV and saving to XLS causes transcoding of characters such as the registered trademark symbol. Ouch.
it would be nice if the TERMSTR infile option were to take affect within the CSV file import.
Just by it's name the option value TERMSTR=CRLF implies that within a cell, the embedded newline character (just the LF i.e. 0Ax on its own) should not be treated as the TERMSTR. I understand that a defect has been recognised for SAS9.2.
Perhaps it will become easier to deal with this problem once the defect is fixed.
For once, don't blame Microsoft -- the CSV file may not have been created by a Microsoft product! ;-)
And, since Excel can read the CSV file correctly, it actually makes it easy to get it into SAS. Just open the CSV file in Excel and save it as an Excel file (XLS). Then run PROC IMPORT on the converted file.