BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
monicazhou2013
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
monicazhou2013
Calcite | Level 5

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

Astounding
PROC Star

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.

Tom
Super User Tom
Super User

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
;
monicazhou2013
Calcite | Level 5

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

monicazhou2013
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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;

 

monicazhou2013
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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