Converting a SAS character to date variable

I imported date from excel with a date variable that is formatted with date and time. When I try to reformat using SAS format commands and input functions, nothing is able to convert the variable to the correct format.

The date in excel reads 3/15/2021 1:00:00 PM, when imported into SAS it reads 44270.541666666664.

I need it to be any format of date.

I tried using format in a data statement:

data text1; 
  set lib.text (drop = LoadFileName FirstName LastName PhoneNumber CreateBy);
  format LastAttemptDttm datetime.;
run;

There was no change in the variable after this.

where LastAttemptDttm is the date variable. In addition I also tried

data text1;
 set lib.text (drop = LoadFileName FirstName LastName PhoneNumber CreateBy);
 date_var = input(LastAttemptDttm, datetime.);
run;

After this, date_var was created, but had 0 observations.

I ran a proc contents to see what was going on, and LastAttemptDttm was listed as a character variable.

The best thing is to fix the Excel sheet so that the column with the datetime value does not have any cells with strings instead of numbers. Then SAS will automatically create a numeric variable instead of character variable.

But if you do have to convert the string '44270.541666666664' into a date value then you can just use this formula

date = int(input(LastAttemptDttm,32.)) + '30DEC1899'd ;

If you want convert it to a datetime value instead then use:

datetime = dhms(input(LastAttemptDttm,32.) + '30DEC1899'd,0,0,0) ;

Make sure to attach the proper display format to the new numeric variable so the date (number of days) or datetime (number of seconds) values print in a style that humans can recognize.