topic Re: proc import csv file errors in SAS Programming

proc import csv file errors

xinyao2019 — Wed, 26 Jun 2019 16:28:05 GMT

Hi all,

i tried to read csv file to SAS.

codes are below:

proc import out=y.deathfull2019pre06242019csv
datafile= "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv"
dbms=csv replace;
guessingrows=MAX;
getnames=yes;
run;

The error is :

NOTE: Invalid data for Age_Type in line 1387 28-47.
NOTE: Invalid data for Date_of_Birth___Month in line 1387 55-55.

NOTE: Invalid data for Age_Type in line 5027 28-47.
NOTE: Invalid data for Date_of_Birth___Month in line 5027 55-55.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be
printed.

NOTE: 27840 records were read from the infile
'Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv'.
The minimum record length was 61.
The maximum record length was 2643.
NOTE: The data set Y.DEATHFULL2019PRE06242019CSV has 27840 observations and 375 variables.
NOTE: DATA statement used (Total process time):
real time 4.34 seconds
cpu time 2.28 seconds

Errors detected in submitted DATA step. Examine log.
27840 rows created in Y.DEATHFULL2019PRE06242019CSV from
Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv.

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 2:08.43
cpu time 1:57.31

Any clue please

Re: proc import csv file errors

Reeza — Wed, 26 Jun 2019 16:37:52 GMT

for some reason PROC IMPORT isn't a good idea for your data. Write a data step instead. I'm surprised you got that error with the MAX setting though, can you post the full log.

Re: proc import csv file errors

ballardw — Wed, 26 Jun 2019 16:47:20 GMT

The invalid data notes almost certainly mean that SAS has decided that the variables were intended to be one form of data, possibly SAS dates and applying a SAS informat or numeric to read the values as such. But some of your data is not actually matching that expected format. You can open the CSV file in a text editor and go to line 1387 and look at the text values. The error is

I suspect you will find something like NA or NULL where a number is expected. If the value is actually treated as a date by SAS then it may mean an incompatible value such as a month out of range 1 to 12 or day of month out of 1 to 28,29,30 or 31 depending on month and year.

If the data step code was successfully created it may mean that you can address the issue by copying the code to the editor and modifying the data step such as providing a "better" informat or adding code to address unexpected values.

Or fix the data issues in the CSV which is usually way more difficult.

It might help to post the generated data step code and all the messages from log. Copy from the log and paste into a code box opened with the forum's {I} or "running man" icons. I am afraid that you may have removed some of the critical diagnostic information.

Re: proc import csv file errors

Tom — Wed, 26 Jun 2019 16:56:06 GMT

I wouldn't be surprised if the cause was text values with embedded line breaks that is causing the columns to get messed up.

Especially since you have min line length of 61 and max line length of over 2000.

You might be able to fix it easily by using the TERMSTR=CRLF option. So if the line breaks inside the vlaues is just either single CR or single LF character and the real end of lines are marked with two character CR+LF combination that will let SAS parse the lines properly. You can try creating a fileref using a FILENAME statement with that option.

filename csv "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv"
  termstr=crlf
;

proc import
  out=y.deathfull2019pre06242019csv replace
  datafile= CSV dbms=csv 
;
  guessingrows=MAX; 
  getnames=yes;
run;

Otherwise if you have embedded line breaks and they cannot be told apart from the real line breaks you will need to pre-process the file to fix that. For just a few of them in a not too big file just open the file in a text editor and fix it. Otherwise there are ways that you can count either the number of fields per line or check for CR or LF inside of quotes to fix the file.

Update

Yup. You data has at least one embedded line break on line 1386 that is causing trouble when reading line 1387. It does look like the value with the line break does have quotes around it. See the closing quote in column 11 of line 1387.

NOTE: Invalid data for Age_Type in line 1387 28-47.
NOTE: Invalid data for Date_of_Birth___Month in line 1387 55-55.
RULE:  ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----
1387   DA19-00512",,,1,20190304,1,2019-01-16T02:06:20Z,0,1,N,N,N,N,Y 61

Re: proc import csv file errors

xinyao2019 — Wed, 26 Jun 2019 16:50:37 GMT

the log is too long exceed 200,000 characters.

i attached here.

thanks !

Re: proc import csv file errors

Reeza — Wed, 26 Jun 2019 16:52:31 GMT

Word will mess up the formatting and not everyone can download attachments. Paste the code into a code box.

Re: proc import csv file errors

xinyao2019 — Wed, 26 Jun 2019 16:59:48 GMT

i am a beginner here. where is the code box?

Re: proc import csv file errors

koyelghosh — Wed, 26 Jun 2019 17:03:31 GMT

Something strange starts to happen at line 1387. You need to inspect what is unusual over there.

Change your code slightly and paste the output.

DATA _NULL_;
	INFILE  "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" DELIMITER=",";
	IF _N_=1387 THEN PUTLOG _ALL_;
RUN;

Re: proc import csv file errors

Tom — Wed, 26 Jun 2019 17:04:40 GMT

@xinyao2019 wrote:

i am a beginner here. where is the code box?

The first one looks like {i} and is the right one to use for logs.

The second one looks like the SAS run command icon. It is good for SAS code as it will try to highlight the code using its best guess at what SAS syntax you have provided.

Re: proc import csv file errors

koyelghosh — Wed, 26 Jun 2019 17:08:13 GMT

@Tom Thank you. I did not know this. Nice to know.

Re: proc import csv file errors

koyelghosh — Wed, 26 Jun 2019 17:11:38 GMT

Great observation. I think you are right. it is expecting a closing double quotes and that is making it fail from 1387 onwards.

Re: proc import csv file errors

Tom — Wed, 26 Jun 2019 17:14:03 GMT

@koyelghosh wrote:
Great observation. I think you are right. it is expecting a closing double quotes and that is making it fail from 1387 onwards.

No it is that SAS does not check if end of line characters are in quotes or not. So values with embedded end of line characters are treated as two lines. You need to either use the single CR or single LF in the values and CRLF to mark the ends of the real lines. Or you have to fix the file to remove those extra line breaks.

Re: proc import csv file errors

koyelghosh — Wed, 26 Jun 2019 17:15:33 GMT

I think it is a typing mistake. Comma and double quotes share the same key (on a US style keyboard). There is a possibility that somebody may have pressed shift + comma ..making it a double quote, instead of comma... Just a guess! Replace line 1387 double quote with a comma and try again.

Re: proc import csv file errors

Reeza — Wed, 26 Jun 2019 17:19:00 GMT

Double quotes are standard on CSV's to enclose text fields in case they may have a comma in the field.

Re: proc import csv file errors

xinyao2019 — Wed, 26 Jun 2019 17:27:11 GMT

i cannot paste the log into log box either: exceeding 200,000 characters .

Re: proc import csv file errors

koyelghosh — Wed, 26 Jun 2019 17:29:01 GMT

Try to run the following and paste the output

DATA _NULL_;
	INFILE  "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" DELIMITER=",";
	IF _N_ IN (1386,1387,1388) THEN PUTLOG _ALL_;
RUN;

Re: proc import csv file errors

Reeza — Wed, 26 Jun 2019 17:30:39 GMT

We don't need the full log, just the first 10 error or so. BUT - look at the log first because it can contain your data so if your data is confidential you'll have issues.

Re: proc import csv file errors

Tom — Wed, 26 Jun 2019 17:33:09 GMT

@koyelghosh wrote:

Try to run the following and paste the output

DATA _NULL_;
	INFILE  "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" DELIMITER=",";
	IF _N_ IN (1386,1387,1388) THEN PUTLOG _ALL_;
RUN;

Simpler version:

DATA _NULL_;
  INFILE  "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" 
    firstobs=1386 obs=1388
  ;
  input;
  list;
RUN;

Re: proc import csv file errors

xinyao2019 — Wed, 26 Jun 2019 17:43:13 GMT

i got errors like this after i ran :

filename csv "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv"
termstr=crlf
;

proc import out=y.deathfull2019pre06242019csv replace
datafile= csv
dbms=csv ;
guessingrows=MAX; *the maximum number of the rows in the file;
getnames=yes;

run;

NOTE: Invalid data for Res_Geo_Match_Score in line 3 678-679.
NOTE: Invalid data for Record_Axis_Code_14 in line 3 1152-1154.
NOTE: Invalid data for VAR666 in line 3 2760-2763.
NOTE: Invalid data for VAR709 in line 3 2911-2911.
NOTE: Invalid data for VAR903 in line 3 3668-3669.
NOTE: Invalid data for VAR966 in line 3 4025-4025.
NOTE: Invalid data for VAR1277 in line 3 5656-5657.
NOTE: Invalid data for _2108_W__ENTIAT_AVENUE in line 3 5810-5818.
NOTE: Invalid data for VAR1414 in line 3 6169-6171.
NOTE: Invalid data for VAR1434 in line 3 6209-6211.
NOTE: Invalid data for VAR1651 in line 3 7282-7283.
NOTE: Invalid data for _9245_RAINIER_AVE_S in line 3 7402-7410.
NOTE: Invalid data for VAR1788 in line 3 7744-7747.
NOTE: Invalid data for _2_YEARS in line 3 7782-7785.
NOTE: Invalid data for VAR1831 in line 3 7971-7971.
NOTE: Invalid data for VAR2025 in line 3 8785-8786.
NOTE: Invalid data for _5400_MACARTHUR_BLVD in line 3 8916-8924.
NOTE: Invalid data for VAR2162 in line 3 9316-9318.
NOTE: Invalid data for VAR2182 in line 3 9367-9369.
NOTE: Invalid data for _208_N_EUCLID_AVE in line 3 10597-10605.
NOTE: Invalid data for VAR2579 in line 3 11209-11209.
NOTE: Invalid data for VAR2773 in line 3 11997-11998.
NOTE: Invalid data for VAR2910 in line 3 12490-12493.
NOTE: Invalid data for VAR3147 in line 3 13620-13621.
NOTE: Invalid data for VAR3284 in line 3 14095-14098.
NOTE: Invalid data for VAR3658 in line 3 15514-15517.
NOTE: Invalid data for _2701_NW_VAUGHN_ST in line 3 16596-16604.
NOTE: Invalid data for VAR4032 in line 3 16920-16923.
NOTE: Invalid data for VAR4406 in line 3 18423-18426.
NOTE: Invalid data for VAR4823 in line 3 20307-20307.
NOTE: Invalid data for VAR5154 in line 3 21619-21622.
NOTE: Invalid data for VAR5419 in line 3 22793-22801.
NOTE: Invalid data for VAR5765 in line 3 24329-24330.
NOTE: Invalid data for VAR5902 in line 3 24827-24830.
NOTE: Invalid data for VAR6139 in line 3 25882-25883.
NOTE: Invalid data for VAR6276 in line 3 26398-26400.
NOTE: Invalid data for VAR6319 in line 3 26552-26552.
NOTE: Invalid data for VAR6481 in line 3 27336-27338.
NOTE: Invalid data for VAR6513 in line 3 27468-27469.
NOTE: Invalid data for VAR6541 in line 3 27588-27593.
NOTE: Invalid data for VAR6650 in line 3 27961-27964.
NOTE: Invalid data for VAR6887 in line 3 29114-29115.
NOTE: Invalid data for VAR7252 in line 3 30720-30723.
NOTE: Invalid data for VAR7261 in line 3 30771-30772.
NOTE: Invalid data for VAR7309 in line 3 31087-31092.
NOTE: Invalid data for VAR7398 in line 3 31263-31265.
NOTE: Invalid data for VAR7525 in line 3 31957-31969.
NOTE: Invalid data for VAR7617 in line 3 32297-32297.
NOTE: Invalid data for VAR7625 in line 3 32343-32343.
NOTE: Invalid data for VAR7626 in line 3 32345-32353.
NOTE: Invalid data for VAR7654 in line 3 32435-32441.
NOTE: Invalid data for VAR7658 in line 3 32472-32495.
NOTE: Invalid data for VAR7661 in line 3 32517-32521.

....

NOTE: 62 records were read from the infile CSV.
The minimum record length was 2947.
The maximum record length was 32767.
One or more lines were truncated.
NOTE: The data set Y.DEATHFULL2019PRE06242019CSV has 62 observations and 7867 variables.
NOTE: DATA statement used (Total process time):
real time 52.79 seconds
cpu time 49.37 seconds

Errors detected in submitted DATA step. Examine log.
62 rows created in Y.DEATHFULL2019PRE06242019CSV from CSV.

Re: proc import csv file errors

Tom — Wed, 26 Jun 2019 17:47:29 GMT

Too bad. Looks like your lines do not end with CR+LF since it caused the file to have way too few lines.
Also it looks like there are SOME instances where CR+LF does occur since it didn't cause the file to have just one line.