Character Date Conversion to Proper Date Format

[lberghammer]

various date and date-time fields imported as character ($18.)

date 1

44474.44181712963

44474.49334490741

44474.52622685185

44475.32769675926

44476.46425925926

wrote this code to convert to the character string to a date format, however they all come back as missing (.).

data Enrollment1_R2_&V; set Enrollment1_R_&V;
SurveyDT=input(strip(qdate), mmddyy10.);
ConsentDT=input(strip(consent_date), mmddyy10.);
EnrollmentDT=input(strip(Enrollment_date), mmddyy10.);
DOBDT=input(strip(dob), mmddyy10.);
format surveydt consentdt enrollmentdt dobdt mmddyy10.;
run;

[SASKiwi]

Most computer software I'm familiar with does not store dates or date-times as decimals, including SAS. I'd be prepared to place a bet that this was imported from Excel. Internally Excel stores dates as the number of days since 1 Jan 1900, so 44474 could be represented as 7 Oct 2021. What the rest of the decimal represents is anyone's guess. If this is coming in from a spreadsheet then save it as a CSV and try importing again. At least as a CSV you have control over how the columns formats are handled. 

[Kurt_Bremser · Accepted solution]

These are most probably Exdel datetimes; they count the number of days from (nominally*) 1900-01-01 as day 1, and have the time as a fraction of the day.

Do this:

dt_num = (input(dt_char,32.) + "30dec1899") * 86400;
format dt_num e8601dt19.;

and see if you get values identical to what you see in Excel..

* "nominally" because Excel has a bug which considers 1900 a leap year. 30dec1899 to also compensate for the fact that Excel starts with day 1, and SAS with day 0 for 1960-01-01.