BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michan22
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

13 REPLIES 13
Tom
Super User Tom
Super User

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;
michan22
Quartz | Level 8

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

 

ballardw
Super User

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?

michan22
Quartz | Level 8

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? 

Tom
Super User Tom
Super User

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.

michan22
Quartz | Level 8

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

michan22
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

 

Tom
Super User Tom
Super User

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

 

 

ballardw
Super User

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.

 

michan22
Quartz | Level 8

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.

 

Reeza
Super User

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 11717 views
  • 3 likes
  • 5 in conversation