Importing CSV file error

[mj8000]

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.

[art297]

Post both the code you ran and your log.

Art, CEO, AnalystFinder.com

[shahparth260]

*  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 

[mj8000]

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;

72

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 */

77

78 %web_drop_table(WORK.IMPORT);

79

80

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

82

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

88

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.

90

91

92 %web_open_table(WORK.IMPORT);

93

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

107

[shahparth260]

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

[Kurt_Bremser]

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

[mj8000]

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?

[art297 · Accepted solution]

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

[mj8000]

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

[Tom]

---

@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;

[shahparth260]

Wow! You're Life saver 

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