topic Re: Convert UTC timestamp string to date in SAS Programming

Convert UTC timestamp string to date

PROCDATARUN — Thu, 26 Oct 2023 06:08:32 GMT

Hello SAS Community,

Running into the following result when attempting to convert character UTC Date to Datefield.

Input data

ActivationUTC = 08-FEB-23 10.54.32 PM UTC

(INPUT(t2.ActivationUTC , datetime20.)) FORMAT=DESDFDT7. AS ActivationDate

Output data = 18Jul19

Expected data = 08Feb23

Can you correct my code ?

Re: Convert UTC timestamp string to date

Patrick — Thu, 26 Oct 2023 07:31:30 GMT

Code like below should work for you.

data have;
  ActivationUTC = '08-FEB-23 10.54.32 PM UTC';
  format want_dttm datetime20.;
  want_dttm=input(ActivationUTC,datetime18.);
  if findw(ActivationUTC,'PM') then
    want_dttm=want_dttm + 43200;
run;

A SAS datetime value is the count of seconds since 01Jan1960. If PM is found in the source string then half a day gets added to the SAS datetime value (43200 seconds).

There is no specific SAS informat for your source string and it also doesn't comply with any ISO pattern. It especially doesn't contain any time offset so be aware that the SAS value will be for Zulu time. You will need to increase/decrease the SAS datetime value for your time zone if that's what you need.

Re: Convert UTC timestamp string to date

Oligolas — Thu, 26 Oct 2023 08:10:07 GMT

Hi,

it's one step easyer actually:

data want;
ActivationUTC = '08-FEB-23 10.54.32 PM UTC';
wantdt = input(ActivationUTC,datetime21.);
format wantdt datetime21.;
run;

Re: Convert UTC timestamp string to date

LinusH — Thu, 26 Oct 2023 08:21:04 GMT

You learn something every day. I thought this would be a perfect job for a picture informat...but there is no such thing...?

Either I can't find it now, or there shoud be a ballot entry for this. Or is this use case too narrow?

Re: Convert UTC timestamp string to date

Patrick — Fri, 27 Oct 2023 01:10:20 GMT

@Oligolas Oh! The datetime informat can also deal with AM/PM. Didn't realize this and it makes things of course even easier.

@PROCDATARUN Use what @Oligolas proposed.