CSV Import Log 'Import unsuccessful. See SAS Log for details.'

[Sofie3]

Hi all!

I'm having a bit of trouble importing a CSV file. I don't even get as far as importing it into SAS!

Here's the code I used:

PROC IMPORT datafile = "C:\Users\foldername\Pulmonologists_Hung.csv"
DBMS = CSV
out = work.pulmon_hung;
guessingrows=MAX;
getnames=yes;
run;

The only message in the log is this: 'Import unsuccessful. See SAS Log for details.'

Can anyone help me with this? I don't know what the problem might be (for a beginner).

Thank you for your help!

[Tom]

Are you sure you have the right filename?

What happens if you try to read the file directly with a data step?

Try dumping the first 3 lines of the file to the SAS log.

data _null_;
  infile "C:\Users\foldername\Pulmonologists_Hung.csv" obs=3 ;
  input;
  list;
run;

[ChrisNZ]

1. Post the full log please.

2. Did you check the data? This message can simply mean that the data was imported but some unexpected values were found. Please check.

[Quentin]

Please post the log.  Often in the log there are multiple error messages which explain the problem, or even one one error messages and one note or warning.  

For example, when I run your code, because I don't have the csv files, I get the following in the log:

1    PROC IMPORT datafile = "C:\Users\foldername\Pulmonologists_Hung.csv"
2    DBMS = CSV
3    out = work.pulmon_hung;
4    guessingrows=MAX;
5    getnames=yes;
6    run;

ERROR: Physical file does not exist, C:\Users\foldername\Pulmonologists_Hung.csv.
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.

So the first error message explains the problem.

[Sofie3]

Hi all

To answer to the replies of Tom, ChrisNZ and Quentin:

- @Tom I checked the folder and filename and it is correct (i changed it here because there are some names in that cannot be shared)

- @Tom, when I used your statement, i don't receive an error, and I can read all the lines in the SAS log.

- @Quentin @ChrisNZ 

To be complete, these are the statement & SAS log:

PROC IMPORT DATAFILE= "C:\Users\foldername\HCP_English.csv"
DBMS=csv
OUT= HCP_Eng;
guessingrows=MAX;
getnames=yes;
RUN;

This is the SAS log:

PROC IMPORT DATAFILE= "C:\Users\foldername\HCP_English.csv"
1166 DBMS=csv
1167 OUT= HCP_Eng;
1168 guessingrows=MAX;
1169 getnames=yes;
1170 RUN;

ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

-@ChrisNZ What do you mean with 'did you check the data?' The import was not successful, so i don't have a database in SAS to check. The CSV file however seems normal.

[Tom]

Another thing to test is whether the data dataset exists already or not.  If it does you need to add the REPLACE option to the PROC IMPORT statement.

But that would yield a different message.

80   proc import file=csv dbms=csv out=class;
81   run;

NOTE: Import cancelled.  Output dataset WORK.CLASS already exists.  Specify REPLACE option to overwrite it.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Another possibility is that the file only has ONE line.  For example if you used EXCEL on a MAC to write the CSV file and accidentally picked the wrong format then it will make a file with only CR as the end of line marker. (Apparently EXCEL never realized that MacOS is now a flavor of Unix.)  In that case PROC IMPORT cannot run because there are no data lines.

Example:

212  proc import file=csv2 dbms=csv out=class replace;
213  run;

Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

You can fix that by defining a FILEREF so you can use the RECFM=CR option.

filename csv "C:\Users\foldername\HCP_English.csv" termstr=cr;
PROC IMPORT DATAFILE= csv DBMS=csv OUT=HCP_Eng replace;
  guessingrows=MAX;
RUN;

Note if that is the issue you should have seen it when running the code to dump the first three lines.  The data step would have shown there was only one line.

220  data _null_;
221    infile csv2 obs=3;
222    input;
223    list;
224  run;

NOTE: The infile CSV2 is:
      (system-specific pathname),
      (system-specific file attributes)

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

1   CHAR  Name,Sex,Age,Height,Weight.Alfred,M,14,69,112.5.Alice,F,13,56.5,84.Barbara,F,13,65.3,98.Carol,F,14,6
    ZONE  4666256724662466667256666704667662423323323332304666624233233232330467667624233233232330467662423323
    NUMR  E1D5C358C175C859784C759784D1C6254CDC14C69C112E5D1C935C6C13C56E5C84D2122121C6C13C65E3C98D312FCC6C14C6

     101  2.8,102.5.Henry,M,14,63.5,102.5.James,M,12,57.3,83.Jane,F,12,59.8,84.5.Janet,F,15,62.5,112.5.Jeffrey
    ZONE  3232333230466772423323323233323046667242332332323304666242332332323323046667242332332323332304666767
    NUMR  2E8C102E5D85E29CDC14C63E5C102E5DA1D53CDC12C57E3C83DA1E5C6C12C59E8C84E5DA1E54C6C15C62E5C112E5DA566259

     201  ,M,13,62.5,84.John,M,12,59,99.5.Joyce,F,11,51.3,50.5.Judy,F,14,64.3,90.Louise,F,12,56.3,77.Mary,F,15
    ZONE  2423323323233046662423323323323046766242332332323323047672423323323233046767624233233232330467724233
    NUMR  CDC13C62E5C84DAF8ECDC12C59C99E5DAF935C6C11C51E3C50E5DA549C6C14C64E3C90DCF5935C6C12C56E3C77DD129C6C15

     301  ,66.5,112.Philip,M,16,72,150.Robert,M,12,64.8,128.Ronald,M,15,67,133.Thomas,M,11,57.5,85.William,M,1
    ZONE  2332323330566667242332332333056667724233233232333056666624233233233305666672423323323233056666662423
    NUMR  C66E5C112D089C90CDC16C72C150D2F2524CDC12C64E8C128D2FE1C4CDC15C67C133D48FD13CDC11C57E5C85D79CC91DCDC1

     401  5,66.5,112. 411
    ZONE  32332323330
    NUMR  5C66E5C112D
NOTE: 1 record was read from the infile (system-specific pathname).
      The minimum record length was 411.
      The maximum record length was 411.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Sofie3]

Hi Tom

I added the replace option, however 'REPLACE' has not the typical blue color (like datafile) and this is the log. Again, it does not work 😞

1576 PROC IMPORT DATAFILE= "C:\foldername\HCP_English.csv"
1577 DBMS=csv
1578 OUT= HCP_Eng
1579 REPLACE;
1580 guessingrows=MAX;
1581 getnames=yes;
1582 RUN;

ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

For the second option, I'm not sure whether it is a file from a mac (it's an export from a website that collects my data). 

When I open the CSV in excel, it shows multiple lines.

But, I also tested your option in SAS, but I receive the same result:

1593 filename csv "C:\foldername\HCP_English.csv" termstr=cr;
15941595 PROC IMPORT DATAFILE=csv DBMS=csv OUT=HCP_Eng REPLACE;
1596 guessingrows=MAX;
1597 RUN;

ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

Thank you for your help already!

[Sofie3]

Maybe it's important to mention: when opening my CSV file, I receive the message, 'Possible data loss. Some features might be lost if you save this workbook in CSV format. To preserve these features, save it in Excel file format.'

When I save it as an Excel file and import it into SAS, it works, and I get my database with multiple rows.

Could this be the problem? How can I address the possible data loss? It is easier for me to upload all CSV files, as I have many that are updated every day.

[Tom]

---

@Sofie3 wrote:

Maybe it's important to mention: when opening my CSV file, I receive the message, 'Possible data loss. Some features might be lost if you save this workbook in CSV format. To preserve these features, save it in Excel file format.'

When I save it as an Excel file and import it into SAS, it works, and I get my database with multiple rows.

Could this be the problem? How can I address the possible data loss? It is easier for me to upload all CSV files, as I have many that are updated every day.

---

That message is something EXCEL will normally give you when you try to CREATE a CSV file.  Since you already have the data in an Excel worksheet where you can change the colors and other stuff it is just telling you that information will not make it into the CSV file since a CSV file is just TEXT.

If you have multiple CSV files that all have the same format (same columns in the same order) then you definitely do NOT want to use PROC IMPORT.  That will GUESS how to define the variables based on the values it sees in the ONE file you ask it to read.  So you could end up with variables defined differently when you read tomorrow's file and all of your old analysis steps will need to be rewritten.  If you write your own data step to read the file you will have full control over how the variables are named, typed, read from the CSV file, displayed.  You can even write a data step that will read multiple CSV files that share the same structure into a single data step.

[Tom · Accepted solution]

I cannot recreate an issue where the PROC IMPORT generates that ERROR and NOTHING ELSE.

Since you seem to be able to read the file then just SKIP THE PROC IMPORT.  It is not needed (and frequently will cause problems).  Just write the step to READ the file instead of asking PROC IMPORT to GUESS how to read the file.

The data step to read a CSV file is trivial.  You just need DATA, INFILE, LENGTH and INPUT statements. If there are any variables that need special instructions for how to convert from text into values you can add an INFORMAT statement.  If there are variables you need displayed in a special way you can add a FORMAT statement. If there are variables you want to attach a descriptive label to then add a LABEL statement.

For example let's assume your file has 4 variables, 2 are character strings and 2 are numbers. One of the numbers is a date which is written in the CSV file with values like 01JAN1980 that the DATE informat will recognize as a DATE value.  Then your program to read the file might look like this:

data HCP_Eng;
  infile "C:\foldername\HCP_English.csv" dsd firstobs=2 truncover ;
  length charvar $20 numvar 8 datevar 8 lastvar $4 ;
  input charvar -- lastvar;
  informat datevar date.;
  format datevar date9.;
run;

If you don't know what the variable names are just copy and paste the first line of the CSV file into you program and convert it into the LENGTH statement.  If you are not sure what is in the variables then just make them character strings to begin with and then LOOK at the values and decide what they are.  You can then change the data step and run it again.

[Sofie3]

I did not know this about the data & infile statement. Now I can continue. Thank you for your time to help me!