topic Character Date Conversion to Proper Date Format in SAS Programming

Character Date Conversion to Proper Date Format

lberghammer — Tue, 26 Jul 2022 01:11:54 GMT

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;

Re: Character Date Conversion to Proper Date Format

SASKiwi — Tue, 26 Jul 2022 03:14:22 GMT

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.

Re: Character Date Conversion to Proper Date Format

Kurt_Bremser — Tue, 26 Jul 2022 06:14:25 GMT

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.