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
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.
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.
Thanks a lot Tom! You are right. There are indeed a couple of string values in the original data.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.