Combine Date Time (both char variables) into one numeric DATETIME variable

[ppchennn]

I have a raw dataset which have a character DATE variable and a character TIME variable.

When I tried to combine them into one Numeric DATETIME variable, I found that whenever the TIME is 00:xx(hh:mm), the combined DATETIME variable will be shifted by one day.

I am using the following code:

(note that I did not define DATE/TIME into Numeric Format in the first DATA STEP because my raw dataset is like this.)

DATA have;
input DATE $ TIME $;
datalines;
20171102 0024
20171212 1124
20170708 1020
20170808 0030
;
run;

data want; set have;
sdate=input(date,yymmdd10.);
stime=input(time,hhmmss8.);
datetime=put(dhms(sdate,0,0,stime),DATETIME.);
run;

I am thinking maybe the format for sdate and stime is inappropriate?

Thanks.

[LinusH]

Can't replicate your problem:

20171102    0024    21125    1440    02NOV17:00:24:00
20171212    1124    21165    41040    12DEC17:11:24:00
20170708    1020    21008    37200    08JUL17:10:20:00
20170808    0030    21039    1800    08AUG17:00:30:00

[ppchennn]

my output looks like this:

DATE	TIME	sdate	stime	datetime
20171102	0024	21125	86400	03NOV17:00:00:00
20171212	1124	21165	41040	12DEC17:11:24:00
20170708	1020	21008	37200	08JUL17:10:20:00
20170808	0030	21039	108000	09AUG17:06:00:00

seems like something is not right with my stime.

[Miracle]

Hi, have you already figured out?

data have;
input date $ time $;
datalines;
20171102 0024
20171212 1124
20170708 1020
20170808 0030
;
run;

data want; 
	set have;
	sdate=input(date,anydtdte12.);
	stime=input(substr(time,1,2)||':'||substr(time,3,2),time6.);
	stime_wrong=input(time,hhmmss6.);
	dtime=dhms(sdate,0,0,stime);
	format sdate date11. stime time6. stime_wrong time6. dtime datetime15. ;
run;

proc print noobs data=want; run;

[ppchennn]

Hi, Thank you for the input! I am using the same method by adding a ":" to stime as a workaround. Just wondering, are you also using SAS 9.3?

[Miracle]

Mine is 9.4.

[art297]

I was only able to replicate your problem if the time variable didn't actually have the leading zeros as shown in your example.

If that is indeed the case, and is only showing them because you're using a z4. format in printing them, then code like the following might solve your problem:

data have;
  input DATE $ TIME $;
  datalines;
20171102 24
20171212 1124
20170708 1020
20170808 30
;
run;

data want;
  set have;
  sdate=input(date,yymmdd10.);
  stime=input(put(input(time,4.),z4.),hhmmss8.);
  datetime=put(dhms(sdate,0,0,stime),DATETIME.);
run;

Art, CEO, AnalystFinder.com

[ppchennn]

Hello,

Thank you for your input! My data does have the 0 as in the first data step.

Your code does not work for me as well, I'm guessing it might be that my SAS is an older 9.3 version.

But you minded me to be careful with the formats, thanks.

[Patrick]

@ppchennn

For the code you've posted SAS should return the correct result since quite a few versions so being on SAS 9.3 is highly likely not the explanation.

Are you really telling us that if you execute the exact code as you've posted, the time in the first row gets translated into 86400 (=number seconds of 1 day)? That would be very mysterious.

I'm assuming that there must be something going on with your raw data.

To check if this is data related or something else can I please ask you to do exactly the same than what we're doing: Copy/paste the code and data you've posted from here into a new SAS code window in your environment, execute the code and tell us if you still get the wrong result (I'd be very surprised if you do).

[ppchennn]

@Patrick

Hello,

Thank you for your input. Yes, I am using the exact code that I posted.

-I copied and pasted the code:

data have;
input date $ time $;
put time;/*display time in the log*/
datalines;
20171102 0024
20171212 1124
20170708 1020
20170808 0030
;
run;

data want;
  set have;
  sdate=input(date,yymmdd10.);
  stime=input(put(input(time,4.),z4.),hhmmss8.);
  datetime=put(dhms(sdate,0,0,stime),DATETIME.);
put stime;/*display stime in the log*/
run;

-The log looks like this:
3845  data have;
3846  input date $ time $;
3847  put time; /*display time in the log*/
3848  datalines;

0024
1124
1020
0030
NOTE: The data set WORK.HAVE has 4 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


3853  ;
3854
3855  run;
3856
3857
3858
3859  data want;
3860    set have;
3861    sdate=input(date,yymmdd10.);
3862    stime=input(put(input(time,4.),z4.),hhmmss8.);
3863    datetime=put(dhms(sdate,0,0,stime),DATETIME.);
3864  put stime; /*display stime in the log*/
3865  run;

86400
41040
37200
108000
NOTE: There were 4 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 4 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

I do find that when I view "Have" in the excel file, it appears like this:

date	time
20171102	24
20171212	1124
20170708	1020
20170808	30

The leading zero disappears and I think this is why the resulting stime is 86400 (from reading time as 24:00) for the first row.

Do you know what might have gone wrong?

[Patrick · Accepted solution]

@ppchennn

It appears that the behavior of the HHMMSS. informat differs between versions as @Tom illustrates. That's something very unusual for SAS software. 

There is a SAS Note which somehow addresses this - but I find this Note rather confusing and your case is also not specifically covered.

http://support.sas.com/kb/39/035.html 

May be try informat B8601TM. instead of HHMMSS. as mentioned in above note. It works in my SAS9.4 M3 Windows environment.

data have;
  input date $ time $;
  put time;/*display time in the log*/
  datalines;
20171102 0024
20171212 1124
20170708 1020
20170808 0030
;
run;

data want;
  set have;
  sdate=input(date,yymmdd10.);
  stime=input(time,b8601tm.);
  datetime=put(dhms(sdate,0,0,stime),DATETIME.);
  put stime;/*display stime in the log*/
run;

[ppchennn]

@PatrickThank you for the input!
The informat B8601TM. works for my SAS 9.3 Windows environment as well.

[Tom]

Yes. 

Consider this example program.

data test ;
  input @1 str $ @1 timeraw hhmmss8. ;
  time = timeraw ;
  format time time8.;
cards;
1122
0024
0011
002400
112233
001122
000011
00112233
11223344
;

proc print;
run;

In SAS version 9.4M2 on Windows the HHMMSS. informat will treat '0024' as representing '24:00't.

SAS 9.4 on WINDOWS

Obs    str         timeraw        time

 1     1122          40920    11:22:00
 2     0024          86400    24:00:00
 3     0011          39600    11:00:00
 4     002400         1440     0:24:00
 5     112233        40953    11:22:33
 6     001122          682     0:11:22
 7     000011           11     0:00:11
 8     00112233      40953    11:22:33
 9     11223344    4041224     1122:33

But on SAS 9.2 on Linux it reads is as 24 seconds.

Obs    str         timeraw        time

 1     1122            682     0:11:22
 2     0024             24     0:00:24
 3     0011             11     0:00:11
 4     002400         1440     0:24:00
 5     112233        40953    11:22:33
 6     001122          682     0:11:22
 7     000011           11     0:00:11
 8     00112233      40953    11:22:33
 9     11223344    4041224     1122:33

With SAS 9.4 M5 on Linux it reads it as 24 minutes.

Obs    str         timeraw        time

 1     1122          40920    11:22:00
 2     0024           1440     0:24:00
 3     0011            660     0:11:00
 4     002400         1440     0:24:00
 5     112233        40953    11:22:33
 6     001122          682     0:11:22
 7     000011           11     0:00:11
 8     00112233      40953    11:22:33
 9     11223344    4041224     1122:33

.