Excel text field to SAS date-time conversion

[DocMartin]

This is probably fairly easy, but I've tried various approaches and nothing worked.  I'm importing an Excel file into SAS. There are many date-time fields, but one of them is read in as text. I've gone to the original Excel file and converted that column into a date-time. That didn't work. 

Here's a small sample of the column in the Excel file that's problematic:

DischargeDate

8/29/20 6:36 PM

8/31/20 3:47 AM

8/29/20 6:36 PM

8/31/20 3:47 AM

9/8/20 7:32 PM

9/8/20 7:32 PM

8/29/20 6:36 PM

Here's my sas code to read the Excel file into SAS:

libname icu XLSX 'C:\\Study103\DATA\NEWS\ICU_NEWS.xlsx';

data rr1; set icu.rr;
   ....
the rest are various input and format statements, none of which works

I want to read in DischargeDate  and convert it into a new variable that has format datetime16.

Thanks!

Andrew

[ballardw]

At least show what you tried.

A common issue with "input statements that don't work" is often using a wrong informat, length for the informat or with date and time values not assigning a format to display the result. Another is trying to place the result back into the same variable and since dates are numeric you don't want them in the already existing character variable.

data example;
   x ="9/8/20 7:32 PM";
   y = input(x, anydtdtm.);
   format y datetime.;
run;

[Tom · Accepted solution]

You still have some character strings in your datetime column, otherwise SAS would have created a datetime variable automatically.

In a spreadsheet any cell can contain any type of data.  In a dataset each variable can only contain one type of data.  When converting a spreadsheet into a dataset SAS will have to decide on type to use when creating a variable to hold the cells in a column.  If ANY of the cells in the column are character then SAS will create the column as character.  Once that decision is made it will now need to represent any numeric cell as a string of characters. For datetime values SAS will represent the values as numeric digit string representing the number Excel uses to store that value.  Excel stores dates as number of days since 1900 and time as percent of a day.   So a date value of 8-29-20 6:36 PM would be stored as the number 44072.77500.

If you want to convert the string "44072.77500" into a SAS datetime value first convert it to a number, then adjust the integer part for the difference in base date used by SAS and then convert the number of days into number of seconds SAS uses to store datetime values.

So if your current variable created from your Excel sheet is named DATESTRING then this code will create a new numeric variable named DATETIME.

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

[DocMartin]

Ahh... I bet that I was using the incorrect length and/or not adding the SAS zero date. But your code works and thanks for the explanation.

BTW: I did strip out the character values ("NULL") from the column. rustrating, but thanks now that I have a solution.