Import Data from Excel - DateTime column has mixed values

[_Hopper]

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?

 

[Tom]

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.

[Ksharp]

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

[Tom]

---

@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

[Ksharp]

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

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

[Tom]

Yes

[_Hopper]

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.

[Tom]

---

@_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.

[Kurt_Bremser]

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

[Ksharp]

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

[Kurt_Bremser]

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.

[ballardw]

---

@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.