DATA Step, Macro, Functions and more

How to delete observation with invalid date (shown as asterisks)?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to delete observation with invalid date (shown as asterisks)?

[ Edited ]

Hello, 

 

I have a large dataset with one or few observations that has date shown as asterisks (as in the attached image). I wonder how should I remove them? The date format is YYMMDD8.

 

I've read this thread,  Why are my dates showing up as asterisks?, which recommend transforming date into Excel date format before importing. But my dataset is too large for Excel. 

 

Edit:

Based on suggestions of answerers, I print out the date with BEST32 and it shows following results. Apparently it is way too large for date, so I managed to remove them by setting a threshold. It's not a good idea to "hard code" a range because I have no idea on future ranges, so I'm still open to any other solutions available. Thank you for your help!

 

Capture.PNG


Accepted Solutions
Solution
Wednesday
Super User
Posts: 6,751

Re: How to delete observation with invalid date (shown as asterisks)?

[ Edited ]

Almost certainly .....

 

The asterisks are showing up because the values for these variables are too large.  The values won't fit into the YYMMDD8. format.

 

Can't tell from this vantage point what is actually in these variables or how it got there.  Can only tell that it's bad data.

 

If you switch the format to something wider, you might be able to get an idea of what is there.  You could try a format like date10. or yymmdd11. 

 

But you would only discover what the values actually look like and might not solve the riddle of how the bad data got there.

View solution in original post


All Replies
Solution
Wednesday
Super User
Posts: 6,751

Re: How to delete observation with invalid date (shown as asterisks)?

[ Edited ]

Almost certainly .....

 

The asterisks are showing up because the values for these variables are too large.  The values won't fit into the YYMMDD8. format.

 

Can't tell from this vantage point what is actually in these variables or how it got there.  Can only tell that it's bad data.

 

If you switch the format to something wider, you might be able to get an idea of what is there.  You could try a format like date10. or yymmdd11. 

 

But you would only discover what the values actually look like and might not solve the riddle of how the bad data got there.

Occasional Contributor
Posts: 7

Re: How to delete observation with invalid date (shown as asterisks)?

Posted in reply to Astounding
Yes, it seems to be the problem. Probably need to specify wider format when importing the data. Thank you for answering!
Super User
Posts: 13,498

Re: How to delete observation with invalid date (shown as asterisks)?

Can you print those values with a best32. format and show us the result?

Or change the format temporarily in the table and show use what a best32 reveals.

 

Occasional Contributor
Posts: 7

Re: How to delete observation with invalid date (shown as asterisks)?

Thank you! I did identify the problem by print BEST32. format. Removed them manually afterwards.
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 79 views
  • 1 like
  • 3 in conversation