Importing data with wrong date format

[lizzy28]

Hi,

I have two date fields in the original excel file, both are in the format of MM/DD/YYYY. However, when the data was imported, one was imported with the datetime format and the other was imported as a character field with the number values like 18482 for 8/7/1950.

Does anyone can help explain why the date fields were imported differently? How can I get the right format for them, especially the one imported as character?

Thanks a lot!

Lizi

[Tom · Accepted solution]

If it imported as character it is because the column has at least one cell with character values in it.

When that happens instead of getting the formatted values of the dates you get the actual number that Excel uses to store dates converting to a string of digits.

The real solution is to fix the Excel file so that all cells in the column contain dates and not strings.

If you cannot do that there are other questions on this site with answers for how to convert those strings to numbers and subtract the right number to get them converted to SAS date values.

[lizzy28]

Thanks a lot Tom! You are right. There are indeed a couple of string values in the original data.