I have a data set where I have a time variable:
data have;
input time time9.;
datalines;
21:00:00
9:00:00
13:00:00
17:00:00
;
run;
data have2;
set have;
format time time9.;
run;
What I would like is to find a way to create a cumulative time variable based on the hours elapsed from the previous time:
Time | CumTime |
21:00:00 | 21 |
09:00:00 | 33 |
13:00:00 | 37 |
17:00:00 | 41 |
Any help would be much appreciated!
Thank you for your help.
Emily
Like this?
data HAVE;
input TIME time9.;
format TIME time9.;
datalines;
21:00:00
9:00:00
13:00:00
17:00:00
;
run;
data WANT;
set HAVE;
CUM+TIME/3600;
run;
TIME | CUM |
---|---|
21:00:00 | 21 |
9:00:00 | 30 |
13:00:00 | 43 |
17:00:00 | 60 |
This then?
data HAVE;
input TIME time9.;
format TIME time9.;
datalines;
21:00:00
9:00:00
13:00:00
17:00:00
;
run;
data WANT;
set HAVE;
if TIME<lag(TIME) then DAYS+1;
CUM=DAYS*24+TIME/3600;
run;
TIME | DAYS | CUM |
---|---|---|
21:00:00 | 0 | 21 |
9:00:00 | 1 | 33 |
13:00:00 | 1 | 37 |
17:00:00 | 1 | 41 |
Can we assume that there is never more than 24 hours between two times? How would we know that the interval between the 21 and 9 is really 12 hours and not 36 (or more) hours?
Adding: every computer system I have ever dealt with provides a date and time, instead of the example here which provides only the time. If you have both the date and time, the problem is simple.
data want;
set have;
retain running_duration;
prev_date = lag(date);
prev_time = lag(time);
duration = dhms(date, 0, 0, time) - dhms(prev_date, 0, 0, time);
running_duration =sum( duration, running_duration);
run;
LAG() gets previous value
DHMS() converts your value to a datetime. Your duration is now in seconds so you'll need to convert that to a format you like.
@emilysully wrote:
Hi Reeza,
I do have a set of dates that go along with it:
data HAVE;
input date MMDDYY10. TIME time9.;
format date MMDDYY10. TIME time9.;
datalines;
09/29/2019 21:00:00
09/30/2019 9:00:00
09/30/2019 13:00:00
09/30/2019 17:00:00
;
run;
Thank you for your assistance.
Emily
So make a datetime value and use DIF() function to see how it changes from observation to observation.
data want ;
set have ;
datetime=dhms(date,0,0,time);
format datetime datetime20.;
if _N_=1 then cum_time=time;
cum_time + dif(datetime);
format cum_time hhmm.;
run;
Obs date TIME datetime cum_time 1 09/29/2019 21:00:00 29SEP2019:21:00:00 21:00 2 09/30/2019 9:00:00 30SEP2019:09:00:00 33:00 3 09/30/2019 13:00:00 30SEP2019:13:00:00 37:00 4 09/30/2019 17:00:00 30SEP2019:17:00:00 41:00
or just remember the start datetime and subtract to find the elapsed time. You can convert a time value to hours by dividing by the number of seconds in one hour.
data want ;
set have ;
if _N_=1 then start=dhms(date,0,0,0);
retain start;
cum_time = dhms(date,0,0,time)-start;
cum_hours = cum_time / '1:00't ;
format cum_time hhmm.;
format start datetime20.;
run;
cum_ Obs date TIME start cum_time hours 1 09/29/2019 21:00:00 29SEP2019:00:00:00 21:00 21 2 09/30/2019 9:00:00 29SEP2019:00:00:00 33:00 33 3 09/30/2019 13:00:00 29SEP2019:00:00:00 37:00 37 4 09/30/2019 17:00:00 29SEP2019:00:00:00 41:00 41
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.