importing issue: no data in first 5 records

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

importing issue: no data in first 5 records

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!


Accepted Solutions
Solution
‎04-18-2017 03:30 PM
Super User
Super User
Posts: 6,842

Re: importing issue: no data in first 5 records

[ Edited ]

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.

View solution in original post


All Replies
Super User
Super User
Posts: 6,842

Re: importing issue: no data in first 5 records

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;
Contributor
Posts: 54

Re: importing issue: no data in first 5 records

[ Edited ]

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

 

Super User
Posts: 11,105

Re: importing issue: no data in first 5 records

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?

Contributor
Posts: 54

Re: importing issue: no data in first 5 records

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? 

Solution
‎04-18-2017 03:30 PM
Super User
Super User
Posts: 6,842

Re: importing issue: no data in first 5 records

[ Edited ]

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.

Contributor
Posts: 54

Re: importing issue: no data in first 5 records

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

Contributor
Posts: 54

Re: importing issue: no data in first 5 records

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

Super User
Super User
Posts: 6,842

Re: importing issue: no data in first 5 records

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

 

Super User
Super User
Posts: 6,842

Re: importing issue: no data in first 5 records

[ Edited ]

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

 

 

Super User
Posts: 11,105

Re: importing issue: no data in first 5 records

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.

 

Contributor
Posts: 54

Re: importing issue: no data in first 5 records

[ Edited ]

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.

 

Super User
Posts: 19,033

Re: importing issue: no data in first 5 records

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

Super User
Posts: 9,856

Re: importing issue: no data in first 5 records

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 288 views
  • 0 likes
  • 5 in conversation