topic UTC offset for historical data in SAS Programming

UTC offset for historical data

PeteLund — Thu, 26 Aug 2021 15:39:54 GMT

I'm getting historical data (back 20+ years) that contains UTC times that I need to convert to local time. The local UTC offset is different depending on the time of year - for me, either seven or eight hours. It seems like the "new" TIMEZONEU2S function only works with the current days UTC offset. Is there a way to get this done directly in SAS?

Re: UTC offset for historical data

ballardw — Thu, 26 Aug 2021 15:58:10 GMT

Some example values, expected result and possibly the location may be helpful to getting a better response.

You should likely provide examples where the TIMEZONEU2S is not returning the expected value along with the code you used to do the conversion.

Re: UTC offset for historical data

PeteLund — Thu, 26 Aug 2021 17:36:12 GMT

Here's a little data step with a couple date/times from my real data and the resulting log:

data _null_;
format OldDT ConvertedDT datetime13.;
input OldDate mmddyy10. + 1 OldTime time5.;

OldDT = dhms(OldDate,0,0,OldTime);
ConvertedDT = TZONEU2S(OldDT);

put _all_;
datalines;
04/09/2016 20:22
03/02/2018 22:38
run;

OldDT=09APR16:20:22 ConvertedDT=09APR16:13:22 OldDate=20553 OldTime=73320 _ERROR_=0 _N_=1
OldDT=02MAR18:22:38 ConvertedDT=02MAR18:15:38 OldDate=21245 OldTime=81480 _ERROR_=0 _N_=2

The current UTC offset for Seattle (where I am) is currently -7 and all the conversions done by the TZONEU2S function use that offset.

It would have been -7 on 04/09/2016, so the first row in the log is correct. But it would have been -8 on 03/02/2018, so row 2 is wrong - the ConvertedDT should be 02MAR18:14:38.

Re: UTC offset for historical data

ChrisHemedinger — Thu, 26 Aug 2021 17:58:57 GMT

According to the doc, TZONEU2S should apply daylight savings rules. I guess you're not seeing that?

Re: UTC offset for historical data

PeteLund — Thu, 26 Aug 2021 18:02:53 GMT

Hi Chris -

No, it doesn't seem to be using the daylight savings rules. If I use that function on the thousands of records I have, covering every day for the past many years, the converted dates are always 7 hours different from the original.

Re: UTC offset for historical data

ballardw — Thu, 26 Aug 2021 18:12:10 GMT

Perhaps the issue is 1) the start datetime is not actually UTC or 2) Not applying the timezone correctly:

data _null_;
  format udt ConvertedDT datetime18.;
  input udt is8601dz.;
  ConvertedDT = TZONEU2S(udt,'America/Los_Angeles');
  put udt= converteddt=;
datalines;
2018-02-28T22:38:56+00:00
2018-03-01T22:38:56+00:00
2018-03-02T22:38:56+00:00
2018-03-03T22:38:56+00:00
2018-03-10T22:38:56+00:00
2018-03-11T22:38:56+00:00
2018-04-01T22:38:56+00:00
2018-05-01T22:38:56+00:00
2018-06-01T22:38:56+00:00
run;

Generates:

udt=28FEB18:22:38:56 ConvertedDT=28FEB18:14:38:56
udt=01MAR18:22:38:56 ConvertedDT=01MAR18:14:38:56
udt=02MAR18:22:38:56 ConvertedDT=02MAR18:14:38:56
udt=03MAR18:22:38:56 ConvertedDT=03MAR18:14:38:56
udt=10MAR18:22:38:56 ConvertedDT=10MAR18:14:38:56
udt=11MAR18:22:38:56 ConvertedDT=11MAR18:15:38:56
udt=01APR18:22:38:56 ConvertedDT=01APR18:15:38:56
udt=01MAY18:22:38:56 ConvertedDT=01MAY18:15:38:56
udt=01JUN18:22:38:56 ConvertedDT=01JUN18:15:38:56

Included some extra dates, the key ones being 10 and 11 Mar since 11 Mar is the first day of DST

Re: UTC offset for historical data

PeteLund — Thu, 26 Aug 2021 18:21:29 GMT

Forcing the time zone (America/Los_Angeles) in the function call did the trick.

Thanks much!