Obsidian | Level 7

## Convert UTC to NZST

Hi, I am trying to convert my date and time columns to NZST (-12 hours) from UTC time.

 Date Time (UTC) 31/08/2020 20:46:49 5/05/2020 20:50:58 19/05/2020 23:39:29 2/09/2020 20:23:10 19/05/2020 4:51:55 1/09/2020 19:31:21 2/09/2020 1:59:41 4/05/2020 21:30:31 5/05/2020 1:31:04 19/05/2020 1:08:49 1/09/2020 2:58:11 6/05/2020 2:01:38 19/05/2020 21:30:10 6/05/2020 2:18:04 19/05/2020 3:41:37 20/05/2020 1:36:52 5/05/2020 21:42:10 6/05/2020 2:14:53 20/05/2020 3:08:00 4/05/2020 21:10:06
1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: Convert UTC to NZST

@RALL - NZST is actually 12 hours ahead of UTC. You are best off combining your dates and times into a SAS datetime then you can shift the datetime forward by 12 hours. If you go past midnight this calculation will change your date to the next day:

``````data _null_;
date = '31/08/2020';
time = '20:46:49';
UTC_datetime = dhms(input(date, ddmmyy10.), input(substr(time,1,2), 2.)
,input(substr(time,4,2), 2.)
,input(substr(time,7,2), 2.));
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
put _all_;
run;``````

:

12 REPLIES 12
Obsidian | Level 7

## Re: Convert UTC to NZST

I've combined the columns to make them datetime, data below, any help would be much appreciated.

 Date (UTC) 2020-05-06:03:03:57 2020-05-06:03:03:57 2020-05-06:03:03:57 2020-05-06:03:03:57 2020-05-06:03:03:57 2020-05-06:03:03:56 2020-05-06:03:03:56 2020-05-06:03:03:56 2020-05-06:03:03:56 2020-05-06:03:03:56 2020-05-06:03:03:55 2020-05-06:03:03:55 2020-05-06:03:03:55 2020-05-06:03:03:55 2020-05-06:03:03:55 2020-05-06:03:03:55 2020-05-06:03:03:54 2020-05-06:03:03:54
PROC Star

## Re: Convert UTC to NZST

@RALL - NZST is actually 12 hours ahead of UTC. You are best off combining your dates and times into a SAS datetime then you can shift the datetime forward by 12 hours. If you go past midnight this calculation will change your date to the next day:

``````data _null_;
date = '31/08/2020';
time = '20:46:49';
UTC_datetime = dhms(input(date, ddmmyy10.), input(substr(time,1,2), 2.)
,input(substr(time,4,2), 2.)
,input(substr(time,7,2), 2.));
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
put _all_;
run;``````

:

Obsidian | Level 7

## Re: Convert UTC to NZST

This is awesome, forgive me I'm a noob using SAS.

I've combined them, datetime in a column.

Your code does exactly want I want but how do I get it to apply to the million rows of data I have?

Garnet | Level 18

## Re: Convert UTC to NZST

If your input is a sas dataset and the variables DATE and TIME are numeric then you can adapt @SASKiwi formula to:

``````data want;
set have;
hh = hour(time);
mm = minute(time);
ss = second(time);
UTC_datetime = dhms(date,hh,mm,ss);
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
run;``````

If the sas dataset is not the origin but some external file like CSV or excel,

you may prefer read the date and time as one datetime variable or prepare ahead the

URC_datetime vaiable.

Obsidian | Level 7

## Re: Convert UTC to NZST

I ran the following but there was no data in the columns added;

data ral.SIGNINS_COMBINED_NZTIME;
set ral.SIGNINS_COMBINED_2;
hh = hour(time);
mm = minute(time);
ss = second(time);
UTC_datetime = dhms(date,hh,mm,ss);
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
run;

Garnet | Level 18

## Re: Convert UTC to NZST

@RALL wrote:

I ran the following but there was no data in the columns added;

data ral.SIGNINS_COMBINED_NZTIME;
set ral.SIGNINS_COMBINED_2;
hh = hour(time);
mm = minute(time);
ss = second(time);
UTC_datetime = dhms(date,hh,mm,ss);
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
run;

Without a log we cannot guess what issues you had. Are there errors or any messages to give us a hint. If there are no errors what results you got.

1) run the next code and post the output report:

``proc contents data=ral.SIGNINS_COMBINED_2; run;``

that will give us the real type and format of your input data.

2) run the code and post the log with the code and messages in it.

3) if the are no errors then run next code and post the report created:

``````proc print data=ral.SIGNINS_COMBINED_NZTIME(obs=5);
var date time UTC_datetime NZST_datetime;
format date ddmmyy10. time time8.
UTC_datetime NZST_datetime  datetime19.;
run;``````
Obsidian | Level 7

## Re: Convert UTC to NZST

NOTE: Variable time is uninitialized.

NOTE: Variable date is uninitialized.

NOTE: Argument 2 to function INTNX('DTHOUR',.,12,'SAMEDAY') at line 38 column 21 is invalid.

WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

750000 at 34:11   750000 at 35:11   750000 at 36:11   750000 at 37:20

NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to

missing values.

Each place is given by: (Number of times) at (Line):(Column).

750000 at 38:21

NOTE: There were 750000 observations read from the data set RAL.SIGNINS_COMBINED_2.

NOTE: The data set RAL.SIGNINS_COMBINED_NZTIME has 750000 observations and 38 variables.

NOTE: Compressing data set RAL.SIGNINS_COMBINED_NZTIME decreased size by 69.63 percent.

Compressed is 5555 pages; un-compressed would require 18293 pages.

NOTE: DATA statement used (Total process time):

real time           11.45 seconds

cpu time            3.41 seconds

When I run option 3;

 Obs date time UTC_datetime NZST_datetime 1 . . . . 2 . . . . 3 . . . . 4 . . . . 5 . . . .

NOTE: There were 5 observations read from the data set RAL.SIGNINS_COMBINED_NZTIME.

NOTE: PROCEDURE PRINT used (Total process time):

real time           0.06 seconds

cpu time            0.01 seconds

Obsidian | Level 7

## Re: Convert UTC to NZST

1) the code

data ral.SIGNINS_COMBINED_NZTIME;
set ral.SIGNINS_COMBINED_2;
hh = hour(time);
mm = minute(time);
ss = second(time);
UTC_datetime = dhms(date,hh,mm,ss);
NZST_datetime = intnx('DTHOUR', utc_datetime, 12, 'SAMEDAY');
format utc_datetime NZST_datetime datetime22.;
run;

PROC Star

## Re: Convert UTC to NZST

@RALL  - Please do @Shmuel 's PROC CONTENTS instruction. It is obvious that your DATE and TIME variables either aren't called DATE and TIME or they have lost their data for some reason.

Garnet | Level 18

## Re: Convert UTC to NZST

@RALL, have you noticed next notes in the log:

```NOTE: Variable time is uninitialized.
NOTE: Variable date is uninitialized.```

in your first post, the labels of the variables are DATE and TIME,

but according to those notes, there are no variables named such in dataset

ral.SIGNINS_COMBINED_2;

1) what is your input dataset name?

2) run the proc contents with your input dataset name and post the results

All other notes and the empty output are the result of above.

Obsidian | Level 7

## Re: Convert UTC to NZST

Thanks for the help. I renamed the variables and that solved it.

PROC Star

## Re: Convert UTC to NZST

@RALL - Glad to hear it. BTW there is no problem running that logic over millions of rows.

Discussion stats
• 12 replies
• 1742 views
• 1 like
• 3 in conversation