error message for proc import CSV file

[HitmonTran]

Hello,

I get an error message importing CSV file, I couldn't find a solution in this forum, please help, thank you!

code:

%web_drop_table(WORK.IMPORT1);


FILENAME REFFILE "&analydir/survey.csv"    ;

PROC IMPORT DATAFILE=REFFILE
	DBMS=csv
	OUT=WORK.IMPORT1;
	GETNAMES=YES;
RUN;

PROC CONTENTS DATA=WORK.IMPORT1; RUN;


%web_open_table(WORK.IMPORT1);

log message:

exel file:

when opened with Notepad ++:

[Kurt_Bremser]

What does the documentation of the file say about the columns?

I see two possible issues:

- the header is in line 2 instead of line 1

- you have unexpected end-of-line characters (e.g. CR instead of CRLF or LF)

Look at your file with a programmer's text editor (I recommend Notepad++) to see more.

[HitmonTran]

Thanks for the response Kurt
1. the header is in Line 1
2. i'm not familiar with this. How do I check unexpected end-of-line characters? Do I copy and paste the excel values onto Notepad++? If so, what do I look for ?

thank you very much

[Kurt_Bremser]

This is a csv file, which means it's text. TEXT. Opening it with Excel will only show you what Excel thinks is in the file, not it's real content.

A good editor can show you how the lines are terminated (CR character for MacOS, LF character for UNIX, CRLF combination for Windows).

Once again, what does the documentation of the file say?

To inspect it, right-click on it in Explorer, select "Open with", and then the editor program you have installed.

[HitmonTran]

Thanks Kurt,

I believe it says UNIX(LF) UTF-8.

I also updated my post and uploaded the screenshot of Notepad ++

[Kurt_Bremser · Accepted solution]

So you have stuff in there which overwhelms PROC IMPORT.

Start with an INFILE statement like this:

infile "path_to_file" termstr=LF dlm="," truncover firstobs=2;

Create usable (letters, digits, underlines, max length 32, can't start with a digit) names for your variables to use in the INPUT statement.

In your INPUT statement, use appropriate(*) informats with a colon modifier so that the informats do not override the delimiter.

For variables which need it, assign proper(*) formats with a FORMAT statement.

*) the information for this has to come from the documentation of the file.

[Tom]

So from your PHOTOGRAPH of the TEXT in the CSV file it looks like the header line has been split into 3 lines.  That will mess up PROC IMPORT's ability to make reasonable guesses about what type of data is in each column.  You will probably want to set GETNAMES to NO instead of YES and tell PROC IMPORT to start reading on line 4.

Also the file seems to have more than 20 lines, so you will want to tell PROC IMPORT to use a larger value for the GUESSINGROWS option.  If you set it to MAX then it will check the whole file before deciding how to define and read each variable.

If you want to really see what is in the file the way SAS sees it just run some SAS code.  For example this program will dump the first 10 lines of the file to the SAS log.

data _null_;
  infile reffile obs=10;
  input;
  list;
run;