topic Re: A Date Error when reading into SAS in SAS Programming

A Date Error when reading into SAS

monicazhou2013 — Tue, 30 Jan 2018 14:49:17 GMT

I have a program like this, every time I run it, it encountered several errors says,"NOTE: Invalid data for RatingDate in line 145(or other lines) 1-10." I am wondering where this program is wrong? Thank you in advance very much!

DATA CTL2015;
INFILE CARDS DLM='09'X;
INPUT Name :$24. Gender $ City :$10. State $ Rating $ RatingDate MMDDYY10. RatingType $;
CARDS;
Anderson, Stephen R. M Blythewood SC 4.0 12/31/2015 C
Bidwell, Stephen E. M Columbia SC 4.5 12/31/2015 C
Camps, Juan M Columbia SC 4.0 12/31/2015 C
Feuerstein, Eric M Blythewood SC 4.0 12/31/2015 C
Hitchcock, David M Columbia SC 4.0 12/31/2015 C
Houston, James Philip M Elgin SC 4.0 12/31/2015 C
Imholz, Mark M Blythewood SC 4.0 12/31/2015 C
Junis, Steven M Columbia SC 4.0 12/31/2015 C
Kuhn, Stanley M Blythewood SC 4.0 12/31/2015 C
Mantai, Michael M Lexington SC 4.0 12/31/2015 C
May, John M Columbia SC 4.5 12/31/2015 C
Mobley, John T. M Columbia SC 4.0 12/31/2015 C
Nguyen, Khue D. M Columbia SC 4.0 12/31/2015 C
Tanner, Mike D M Blythewood SC 4.0 12/31/2015 C

;

run;

Re: A Date Error when reading into SAS

Kurt_Bremser — Tue, 30 Jan 2018 14:55:28 GMT

You forgot a colon before the date informat.

Re: A Date Error when reading into SAS

monicazhou2013 — Tue, 30 Jan 2018 15:04:09 GMT

Thank you very much for your fast response. I tried to add this colon before date informat, but it even gave me more errors.

Re: A Date Error when reading into SAS

Astounding — Tue, 30 Jan 2018 15:24:02 GMT

Most likely, the problem is with the data not the program.

For those lines where SAS complains, what does the line of data look like? The date coming in probably does not look like a month/day/year date.

Re: A Date Error when reading into SAS

Tom — Tue, 30 Jan 2018 15:33:27 GMT

Looks like perhaps your tabs got converted to spaces when you pasted the lines into the editor?

It looks like you have spaces in your first field.

Anderson, Stephen R. M Blythewood SC 4.0 12/31/2015 C
Bidwell, Stephen E. M Columbia SC 4.5 12/31/2015 C

How do you know where the name ends and the gender code begins?

You might be able to try counting the words from the end of the line, then what happens if you have a city name like North Little Rock?

Your best solution is to use something other than space as the delimiter. And if the delimiter appears in the value for a field then add quotes around that field.

DATA CTL2015;
  INFILE CARDS DSD DLM='|' truncover;
  length Name $24 Gender $1 City $10 State $2 Rating 8 RatingDate 8 RatingType $1 ;
  informat RatingDate MMDDYY. ;
  format RatingDate yymmdd10. ;
  input name -- ratingtype;
CARDS;
Anderson, Stephen R.|M|Blythewood|SC|4.0|12/31/2015|C
Bidwell, Stephen E.|M|Columbia|SC|4.5|12/31/2015|C
;

Re: A Date Error when reading into SAS

monicazhou2013 — Tue, 30 Jan 2018 17:26:35 GMT

Thank you very much for your great and fast help! I tried your method and it works well!

Re: A Date Error when reading into SAS

monicazhou2013 — Tue, 30 Jan 2018 17:55:15 GMT

I have another question for you, if I have a million observations, it is impossible for me to do this manually. Do you have any other way to solve this problem? Thank you very much!

Re: A Date Error when reading into SAS

Tom — Tue, 30 Jan 2018 18:56:39 GMT

Yes and no. If the original file still has the tabs then you can just DLM='09'X like you had before, but point to the original file and don't try to copy the file contents to your program. That should avoid causing the tabs to turn into spaces.

infile 'my_original_file.txt' dsd dlm='09'x truncover ;

If the original file has spaces between the fields that it will take a lot of work to figure out whether each line has spaces in the person's name or the city name and if so where to place delimiters between the fields.

You might be able to make it more manageable by handling the obvious cases automatically and spitting the other case to another file to process by hand. So your file had 7 fields. The examples you posted had three values for the first field. So each line should have 9 words and the fourth word should be M or F.

data _null_;
   infile 'my_original_file.txt' truncover ;
   length w1-w10 $100 ;
   input w1-w10 ;
   if countw(_infile_,' ','q') = 9 and w4 in ('M','F') then do;
    file 'pipe_delimited.txt' dsd dlm='|' ;
    w10=catx(' ',w1,w2,w3);
    put w10 w4-w9;
   end;
   else do;
     file 'problem_records.txt' ;
     put _infile_;
   end;
run;

Re: A Date Error when reading into SAS

monicazhou2013 — Thu, 01 Feb 2018 14:30:27 GMT

Thank you for your detailed answer, Tom! I think the problem is from copy and paste. We do not have the original file, we can only copy it from the website.I learned a lot form your explanation, thank you again for helping me about this!