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.
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;
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
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.
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;
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
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.
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).
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?
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;
@PatrickThank you for the input!
The informat B8601TM. works for my SAS 9.3 Windows environment as well.
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
.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: