topic Re: Excel DateTime to SAS DateTime - have code - not working in SAS Programming

Excel DateTime to SAS DateTime - have code - not working

_Dan_ — Tue, 27 Apr 2021 12:42:38 GMT

Hi,

I have the following dates in a .xlsx file:

23Feb2021 15:57:13.00
23Feb2021 18:36:25.00
23Feb2021 18:52:04.00
23Feb2021 19:00:56.00
22Feb2021 16:44:40.00
24Feb2021 17:09:48.00
(their Excel numeric equivalents:)

44250.66473379629
44250.77528935185
44250.786157407405
44250.79231481481
44249.69768518519
44251.7151388889

I have the following code in a proc sql (having to input as the column is being read as character, due to actual SAS formatted dates (datetime19.) being in the same column for some reason, and thus being treated as char):

select (input(Created,8.) - 21916)*86400 as Created_E format datetime19.

And I get the following outputs in SAS:

23FEB2021:15:50:24
23FEB2021:18:28:48
23FEB2021:18:43:12
23FEB2021:18:57:36
22FEB2021:16:33:36
24FEB2021:17:02:24

Why are my dates all around 5-11 minutes out, please?

Re: Excel DateTime to SAS DateTime - have code - not working

Kurt_Bremser — Tue, 27 Apr 2021 13:34:13 GMT

The 8. informat cuts off all digits after the first 8.

Is the variable "created" in fact character, or is it numeric?

If character, use the 32. informat (the maximum length) in the INPUT function; otherwise, use the variable as is.

Re: Excel DateTime to SAS DateTime - have code - not working

_Dan_ — Tue, 27 Apr 2021 13:57:25 GMT

Thanks Kurt. You are, of course, correct; I'm having to ingest the column as character, but my misunderstanding of the input function on this occasion led me to believe I was limited to '8' because that's the maximum length of a numeric field. Extending this to 32 has of course resolved this issue. As soon as I saw your response I realise I'd come across this years ago and stumbled across this exact fix too. Lack of regular coding - rusty hands 🙂