Converting non-homogenous dates to SAS dates

[ballardw]

If you only have to process this one time I would suggest the Open in Excel, highlight and change text to numeric approach.

If you have to do this frequently I would start documenting how much time this takes as eventually what ever approach you take that works with this file is likely to fail, hopefully no date at all rather than a date that causes more issues later, and will need additional time to identify and correct.

At some point you may have documented enough time=man hours=Co$t to the organization that the process at least gets reviewed.

It may be there is no formal data "agreement" for combining the multiple data sources as to what goes in which column, what format and what file type. A lack of this type is common cause of "weird" data issues such as you are experiencing. There may be other people upstream of you that are also having issues around combining this data.

I decided long ago that I would avoid using actual Excel files for interchange because of the file size limits. Once upon a time 64K rows was the limit and I routinely dealt with orders of magnitude above that. Having someone try to tell me to process 10 or 100 files after spitting the data apart just to use Excel (and why? they couldn't actually do anything in Excel without the whole data set) was a waste of everyones time.