topic Re: Combine Date Time (both char variables) into one numeric DATETIME variable in SAS Programming

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

ppchennn — Thu, 28 Dec 2017 06:43:09 GMT

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.

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

LinusH — Thu, 28 Dec 2017 07:38:21 GMT

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

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

ppchennn — Thu, 28 Dec 2017 08:09:41 GMT

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.

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

Miracle — Thu, 28 Dec 2017 13:18:56 GMT

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;

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

art297 — Thu, 28 Dec 2017 14:10:58 GMT

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

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

ppchennn — Fri, 29 Dec 2017 01:46:02 GMT

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.

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

ppchennn — Fri, 29 Dec 2017 01:51:21 GMT

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?

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

Miracle — Fri, 29 Dec 2017 01:55:26 GMT

Mine is 9.4.

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

Patrick — Fri, 29 Dec 2017 02:18:27 GMT

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

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

ppchennn — Fri, 29 Dec 2017 05:42:26 GMT

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

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

Tom — Fri, 29 Dec 2017 05:44:38 GMT

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

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

Patrick — Fri, 29 Dec 2017 22:56:11 GMT

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

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

ppchennn — Tue, 02 Jan 2018 13:16:45 GMT

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