topic Importing CSV file error in SAS Studio

Importing CSV file error

mj8000 — Mon, 14 May 2018 00:04:55 GMT

I'm seeing the following error message when I try to import a csv file that exists under "myfolders". I followed the basic instructions for importing a CSV file. Am I missing something here?

ERROR: File WORK.IMPORT.DATA does not exist.

Re: Importing CSV file error

art297 — Mon, 14 May 2018 01:07:20 GMT

Post both the code you ran and your log.

Art, CEO, AnalystFinder.com

Re: Importing CSV file error

shahparth260 — Mon, 14 May 2018 02:00:57 GMT

* I am strongly agree that individual need to see what you are getting errors so please share with code and log screen shot at first place.

* for your reference here's code which may helps you out....

proc import datafile="location where your file present / filename.csv"

out=dsn

dbms=csv

replace;

run;

Hope this helps !!

Regards

SHAH

Re: Importing CSV file error

mj8000 — Mon, 14 May 2018 06:18:58 GMT

Code:

/* Generated Code (IMPORT) */
/* Source File: tl1_mock.csv */
/* Source Path: /folders/myfolders */
/* Code generated on: 5/14/18, 1:17 AM */

%web_drop_table(WORK.IMPORT);

FILENAME REFFILE '/folders/myfolders/tl1_mock.csv';

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

PROC CONTENTS DATA=WORK.IMPORT; RUN;

%web_open_table(WORK.IMPORT);

Log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 /* Generated Code (IMPORT) */

74 /* Source File: tl1_mock.csv */

75 /* Source Path: /folders/myfolders */

76 /* Code generated on: 5/14/18, 1:17 AM */

78 %web_drop_table(WORK.IMPORT);

81 FILENAME REFFILE '/folders/myfolders/tl1_mock.csv';

83 PROC IMPORT DATAFILE=REFFILE

84 DBMS=CSV

85 OUT=WORK.IMPORT;

86 GETNAMES=YES;

87 RUN;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

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

cpu time 0.21 seconds

89 PROC CONTENTS DATA=WORK.IMPORT; RUN;

ERROR: File WORK.IMPORT.DATA does not exist.

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

92 %web_open_table(WORK.IMPORT);

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

107

Re: Importing CSV file error

shahparth260 — Mon, 14 May 2018 06:28:05 GMT

check your csv file once again make sure you dont have data in first few records

Re: Importing CSV file error

Kurt_Bremser — Mon, 14 May 2018 08:05:51 GMT

When something unexpected happens, each and every line of the log is important. The most important one here is this:

Unable to sample external file, no data in first 5 records.

Inspect your csv file for where data starts. If the csv files you get exhibit this kind of behaviour regularly, you should write your custom data step so that it takes this into account (proc import won't get you consistent results).

Re: Importing CSV file error

mj8000 — Mon, 14 May 2018 15:37:53 GMT

Thank you for the reply. If I test with a sample CSV file generated from the SASHELP -> CARS library, I still see the same error. Is my CSV file supposed to have data in the first 5 lines?

Re: Importing CSV file error

art297 — Mon, 14 May 2018 16:02:19 GMT

You're getting the odd message because your csv file's records end with CR rather than CRLF .. at least with the cars.csv file you posted. The following would correct for that:

FILENAME REFFILE '/folders/myfolders/cars.csv';
filename csv temp;

data _null_;
  infile reffile termstr=cr;
  file csv recfm=n;
  input;
  put _infile_ '0A'x;
run;

PROC IMPORT DATAFILE=csv
 DBMS=CSV
 OUT=WORK.IMPORT
 replace;
 GETNAMES=YES;
RUN;

Art, CEO, AnalystFinder.com

Re: Importing CSV file error

mj8000 — Mon, 14 May 2018 16:24:33 GMT

For my own understanding and for future reference:

CR and LF are control characters, respectively coded 0x0D (13 decimal) and 0x0A (10 decimal).

CRLF (Windows) was expected, but my csv file was using CR (as I'm on a Macintosh). As @art297 suggested, appending the "LF" part did the trick.

Thanks to everyone for the help and support.

Re: Importing CSV file error

shahparth260 — Mon, 14 May 2018 16:36:23 GMT

Wow! You're Life saver

I am also on learning curve. Thanks to having you in SAS community.

Re: Importing CSV file error

Tom — Tue, 15 May 2018 14:49:43 GMT

@mj8000 wrote:

For my own understanding and for future reference:

CR and LF are control characters, respectively coded 0x0D (13 decimal) and 0x0A (10 decimal).

CRLF (Windows) was expected, but my csv file was using CR (as I'm on a Macintosh). As @art297 suggested, appending the "LF" part did the trick.

Thanks to everyone for the help and support.

Related link: https://stackoverflow.com/questions/1552749/difference-between-cr-lf-lf-and-cr-line-break-types

A couple of points that can help make this easier.

First as far as I know it is only Excel on Mac that is still confused and thinks that Mac's should use CR only as end of line markers. Most other programs understand that MAC OS is now a unix variant and the lines should end with LF instead. If you check the options when saving a spreadsheet to a delimited file there are options that create the file using a more universal end of line setting.

Second you can use the TERMSTR option in SAS to tell it that your file is using only CR as the end of line marker. To use it with PROC IMPORT you will need to use a FILENAME statement and point PROC IMPORT at the fileref you have defined by the FILENAME statement.

FILENAME csv '/folders/myfolders/cars.csv' termstr=cr;
PROC IMPORT DATAFILE=csv
 DBMS=CSV
 OUT=WORK.IMPORT
 replace;
 GETNAMES=YES;
RUN;