DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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.


Accepted Solutions
Solution
2 weeks ago
Respected Advisor
Posts: 4,237

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

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Posts: 5,554

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

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

 

Data never sleeps
Occasional Contributor
Posts: 11

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

my output looks like this:

DATETIMEsdatestimedatetime
201711020024211258640003NOV17:00:00:00
201712121124211654104012DEC17:11:24:00
201707081020210083720008JUL17:10:20:00
2017080800302103910800009AUG17:06:00:00

seems like something is not right with my stime.

Regular Contributor
Posts: 234

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

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;
Occasional Contributor
Posts: 11

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

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?
Regular Contributor
Posts: 234

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

Mine is 9.4.
PROC Star
Posts: 7,647

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

[ Edited ]

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

 

Occasional Contributor
Posts: 11

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

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.

Respected Advisor
Posts: 4,237

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

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

Occasional Contributor
Posts: 11

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

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

datetime
2017110224
201712121124
201707081020
2017080830

 

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?

 

Solution
2 weeks ago
Respected Advisor
Posts: 4,237

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

[ Edited ]

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

 

Highlighted
Occasional Contributor
Posts: 11

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

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

Super User
Super User
Posts: 7,255

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

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

.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 289 views
  • 5 likes
  • 6 in conversation