BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ppchennn
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

12 REPLIES 12
LinusH
Tourmaline | Level 20

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
ppchennn
Fluorite | Level 6

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.

Miracle
Barite | Level 11

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
Fluorite | Level 6
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?
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

@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
Fluorite | Level 6

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

 

Patrick
Opal | Level 21

@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
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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