This is a nagging problem when converting excel data to SAS datasets especially with the date fields.
I have this field ship_date where some values are of fomat 02/21/2007 and some are of 2/2/2007.
As first step, I create a new column in excel convert all ship_date values to character and then import to SAS. Even then there are some values which wouldnt convert to the expected in excel either.2/22/2007 remains as is so I have to add a zero before month and year and then do the data conversion.
This will take up long time to clean/modfiy formats.
I'm wondering if there is any other technique that will help reduce this manual intervention?
There is a fair chance that the 2/2/2007 date in excel is actually entered in the cell as 2/2/2007 and that excel, in it's fashion is just storing text in a column meant for a date. It looks like a date, but it is not.