topic Import Data from Excel - DateTime column has mixed values in SAS Programming

Import Data from Excel - DateTime column has mixed values

_Hopper — Fri, 03 Apr 2026 16:24:12 GMT

I have an excel import file that looks like this:

But two values are character even though they look the same so when imported this is what I see

Is there a workaround for this?

Re: Import Data from Excel - DateTime column has mixed values

Tom — Fri, 03 Apr 2026 17:37:44 GMT

When you have both character and numeric values in a column in EXCEL then SAS is forced to define the variable as character. When this happens with DATE or DATETIME values SAS store the actual numeric value EXCEL uses as a normal digit string.

Best solution is to fix the Excel file so that all of the values in that column are actual Excel datetime values.

But if you are stuck with the file you have just convert the character variable into a new numeric variable. Then adjust for the difference in the way that EXCEL and SAS count days.

data want;
  set have;
  actual_datetime = input(DateTIme,??32.);
  if missing(actual_datetime) then actual_datetime=input(DateTime,anydtdte20.);
  else actual_datetime = dhms(actual_datetime+'30DEC1899'd,0,0,0);
  format actual_datetime datetime19.;
run;

So when the value looks like a number then just add the number of days between 1960 and 1900 to adjust for the difference in what day each system thinks is day one (and the fact that Excel thinks 1900 was a leap year) and convert the number of days into number of seconds.

Otherwise just ask SAS to convert the string into a datetime value.

Re: Import Data from Excel - DateTime column has mixed values

Ksharp — Sat, 04 Apr 2026 01:33:08 GMT

Can you post this excel with this column, so I can test and solve it ?

Re: Import Data from Excel - DateTime column has mixed values

Ksharp — Sat, 04 Apr 2026 01:39:05 GMT

Tom,
You only take care of date part of it, not time part, right ?

Re: Import Data from Excel - DateTime column has mixed values

Tom — Sat, 04 Apr 2026 02:51:55 GMT

@Ksharp wrote:
Tom,
You only take care of date part of it, not time part, right ?

No.

Excel stores time of day as a fraction of day. The DHMS() function converts a fraction of day into the corresponding number of seconds.

Essentially it is doing:

60*(60*(24*D + H) + M) + S

Re: Import Data from Excel - DateTime column has mixed values

Kurt_Bremser — Sat, 04 Apr 2026 10:13:19 GMT

Save to a CSV file and read that with a DATA step. Both the "real" and character datetime values will appear in the same format, so you can read them with the E8601DT informat.

Disclaimer: I tested the "save to csv" with LibreOffice on a Mac, but I sincerely hope MS Excel will do the same.

Re: Import Data from Excel - DateTime column has mixed values

Ksharp — Sun, 05 Apr 2026 01:24:06 GMT

Tom,
Great. Learn something new.
So you should use ANYDTDTM. instead of ANYDTDTE, right ?

actual_datetime=input(DateTime,anydtdte20.);
--->
actual_datetime=input(DateTime,anydtdtm20.);

Re: Import Data from Excel - DateTime column has mixed values

ballardw — Sun, 05 Apr 2026 17:21:51 GMT

@Kurt_Bremser wrote:

Save to a CSV file and read that with a DATA step. Both the "real" and character datetime values will appear in the same format, so you can read them with the E8601DT informat.

Disclaimer: I tested the "save to csv" with LibreOffice on a Mac, but I sincerely hope MS Excel will do the same.

Used to have to do this about once a week with my last job and Excel save to CSV usually worked except in the pathological cases of one data source that would have a column with cells that were formatted in Excel as currency for part of the column and then dates for the remainder. In which case the solution was to set the entire column of the Excel source to the proper appearance before exporting to CSV.

Re: Import Data from Excel - DateTime column has mixed values

_Hopper — Mon, 06 Apr 2026 13:19:27 GMT

Will this approach still work if the supplier gets their act together and has the values formatted the correct way the next time around? What I want is a permanent solution not one that fixes this issue and then the issue appears again when (if) the input file is corrected.

PS: I'm not allowed to modify the source data (e.g., save it as csv). I have to use it as is.

Re: Import Data from Excel - DateTime column has mixed values

Tom — Mon, 06 Apr 2026 18:45:52 GMT

Yes

Re: Import Data from Excel - DateTime column has mixed values

Tom — Mon, 06 Apr 2026 18:54:28 GMT

@_Hopper wrote:

Will this approach still work if the supplier gets their act together and has the values formatted the correct way the next time around? What I want is a permanent solution not one that fixes this issue and then the issue appears again when (if) the input file is corrected.

PS: I'm not allowed to modify the source data (e.g., save it as csv). I have to use it as is.

No. In that case you will need a little more logic to detect if the DateTime variable is numeric or character.

For example you might use this %VARINFO() macro to detect the type of the variable and then use it to determine whether or not you need to take action.

data want;
    set have;
%if %varinfo(HAVE,DATETIME,TYPE)=C %then %do;
    ... logic to generate ACTUAL_DATETIME ...
    rename actual_datetime=DateTime ;
    drop DateTime;
%end;
run;

You might also want to look into using a different file format for the transfer.

Perhaps SAS transport files? That way there is no conversion to be done.

Or a delimited text file. That way you can write a data step to read the file and set the variable names, types and lengths they way you want them.

Re: Import Data from Excel - DateTime column has mixed values

Kurt_Bremser — Mon, 06 Apr 2026 19:23:07 GMT

Using File - Save as in Excel with CSV as target does NOT modify the source file in any way.