SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Importing data with wrong date format

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Importing data with wrong date format

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


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 7,929

Re: Importing data with wrong date format

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.

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 7,929

Re: Importing data with wrong date format

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.

Frequent Contributor
Posts: 110

Re: Importing data with wrong date format

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

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 125 views
  • 0 likes
  • 2 in conversation