DATA Step, Macro, Functions and more

A Date Error when reading into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

A Date Error when reading into SAS

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;


Accepted Solutions
Solution
‎01-30-2018 12:24 PM
Super User
Super User
Posts: 8,289

Re: A Date Error when reading into SAS

Posted in reply to monicazhou2013

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
;

View solution in original post


All Replies
Super User
Posts: 10,623

Re: A Date Error when reading into SAS

Posted in reply to monicazhou2013

You forgot a colon before the date informat.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 10

Re: A Date Error when reading into SAS

Posted in reply to KurtBremser

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

Super User
Posts: 6,939

Re: A Date Error when reading into SAS

Posted in reply to monicazhou2013

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.

Solution
‎01-30-2018 12:24 PM
Super User
Super User
Posts: 8,289

Re: A Date Error when reading into SAS

Posted in reply to monicazhou2013

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
;
Occasional Contributor
Posts: 10

Re: A Date Error when reading into SAS

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

Occasional Contributor
Posts: 10

Re: A Date Error when reading into SAS

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!

Super User
Super User
Posts: 8,289

Re: A Date Error when reading into SAS

Posted in reply to monicazhou2013

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;

 

Occasional Contributor
Posts: 10

Re: A Date Error when reading into SAS

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 138 views
  • 0 likes
  • 4 in conversation