topic Re: importing issue: no data in first 5 records in SAS Programming

importing issue: no data in first 5 records

michan22 — Mon, 10 Apr 2017 21:55:27 GMT

Hi guys!

I am trying to import a csv file and here is my import code:

proc import out=qiao.pepfar

datafile='D:\nosocomial\27-mar-2017.csv

dbms=csv replace;

getnames=yes;

guessingrows=700;

run;

log says: unable to sample external file. no data in first 5 records.

I searched previous post for similar problems and seems like the response from most people is to check the csv file for missing data in the first 5 rows. I did check and there are no empty rows anywhere in my csv file.

Does anyone know what's the problem here?

Thanks!

Re: importing issue: no data in first 5 records

Tom — Mon, 10 Apr 2017 22:02:06 GMT

Your posted code is missing a closing quote. Is it possible that is your problem?

What is in the first five rows of the file? Here is simple program you can use to look.

data _null_;
  infile 'D:\nosocomial\27-mar-2017.csv' obs=5 ;
  input;
  list;
run;

Re: importing issue: no data in first 5 records

ballardw — Mon, 10 Apr 2017 22:03:37 GMT

Missing ' in the csv file name perhaps.

That is if the code shown was copied correctly.

Or other misspelling in the file name.

Also when checking CSV files do not use Excel, use a text editor. Excel will often hide some format issues and make you believe there is no problem when there is.

Use notepad, Wordpad or even the SAS program editor to look at the file.

Re: importing issue: no data in first 5 records

michan22 — Mon, 10 Apr 2017 22:33:22 GMT

Oops I copy and pasted wrong, I do have the double quote around the file location.

data _null_;
  infile 'D:\nosocomial\27-mar-2017.csv' obs=5 ;
  input;
  list;
run;

I ran this and log displays:

NOTE: The infile "D:\Nosocomial\27-Mar-2017 Screening Data.csv" is:
Filename=D:\Nosocomial\27-Mar-2017 Screening Data.csv,
RECFM=V,LRECL=32767,File Size (bytes)=123016,
Last Modified=10Apr2017:13:19:40,
Create Time=10Apr2017:13:19:41

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--

EDIT: Data stripped due to suspected confidentiality issues (@reeza)

NOTE: 1 record was read from the infile "D:\Nosocomial\27-Mar-2017 Screening Data.csv".
The minimum record length was 32767.
The maximum record length was 32767.
One or more lines were truncated.
NOTE: DATA statement used (Total process time):
real time 0.65 seconds
cpu time 0.43 seconds

Re: importing issue: no data in first 5 records

michan22 — Mon, 10 Apr 2017 23:38:44 GMT

Sorry that was a copy paste error, I did have both quote around the file location.

I checked my file in notepad and it does not seem like any row is empty.

Re: importing issue: no data in first 5 records

Reeza — Mon, 10 Apr 2017 22:49:26 GMT

@michan22 Is this public information? If it's private I would highly suggest you delete it.

Re: importing issue: no data in first 5 records

ballardw — Mon, 10 Apr 2017 23:00:43 GMT

This bit from your log

The minimum record length was 32767.
The maximum record length was 32767.
One or more lines were truncated.

May be a big clue. Do you expect any single line to be more than 32767 characters long? There may not be an end of line character that your program is recognizing.

Do you know if this file may have every crossed operating systems such as began on Unix or Apple and you are reading it in Windows?

Re: importing issue: no data in first 5 records

Tom — Mon, 10 Apr 2017 23:07:56 GMT

Looks like your end of lines are not being recognized by SAS. So it thinks that there is only one line. That is why it does not see any data.

Look at the HEX CODE for the character right after the last character of the last variable name.

If should be either '0D'x, '0A'x or possible both '0D0A'x.

Since SAS should automatically recognize the last two (even if some times it thinks the CR is part of the line instead of part of end of line marker) I suspect that you have just CR between the lines.

Try using the TERMSTR=CR option on your INFILE statement.

data _null_;
  infile 'D:\nosocomial\27-mar-2017.csv' obs=2 termstr=cr ;
  input;
  list;
run;

This is usually caused by writing an Excel file to CSV format when running Excel on a Macintosh. Excel never got the message that Apple switched to Unix log ago and they should use LF as the end of line.

To get it to work with PROC IMPORT you will need to use a FILENAME statement.

filename mycsv 'D:\nosocomial\27-mar-2017.csv' termstr=cr ;
proc import datafile=mycsv ....

Re: importing issue: no data in first 5 records

michan22 — Mon, 10 Apr 2017 23:44:03 GMT

There is definitely no variable with more than 32767 characters long.

However, it was an excel file and I saved as csv file on a mac. Then I transfered the file to my windows laptop and imported in sas. Do you think that's the issue?

Re: importing issue: no data in first 5 records

Tom — Mon, 10 Apr 2017 23:47:29 GMT

Yes. Excel on Mac is stupid. When you save to CSV make sure you pick the right file type. The default CSV file type uses carriage return for end of line. Nobody uses CR for end of line since Apple switch MAC OS to being a flavor of Unix many years ago. Make sure to pick a CSV file type hat will use a normal end of line character when saving to CSV using Excel on a Mac.

Re: importing issue: no data in first 5 records

Ksharp — Tue, 11 Apr 2017 13:07:14 GMT

That is really weird . Did you try datarow=5 option ?

Re: importing issue: no data in first 5 records

michan22 — Tue, 11 Apr 2017 17:26:21 GMT

Thank you! I will try to save as csv file on the PC and let you know if the problem is fixed!

Re: importing issue: no data in first 5 records

michan22 — Tue, 18 Apr 2017 19:30:53 GMT

Thank you! I tried to save the file as csv on the PC and it imported fine.

Re: importing issue: no data in first 5 records

Tom — Tue, 18 Apr 2017 19:34:44 GMT

Good to know. But you could have read the file with the CR at the end of the line by using the TERMSTR option on INFILE or FILENAME statement.

http://support.sas.com/kb/14/178.html