Convert UTC to NZST

[RALL]

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

[RALL]

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

[SASKiwi · Accepted solution]

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

:

[RALL]

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?  

[Shmuel]

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.

[RALL]

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;

[Shmuel]

---

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

Help us to help you:

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;

[RALL]

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

[RALL]

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;

[SASKiwi]

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

[Shmuel]

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

[RALL]

@SASKiwi @Shmuel 

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

[SASKiwi]

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