topic Re: How do I create a cumulative time variable in my dataset? in SAS Programming

How do I create a cumulative time variable in my dataset?

emilysully — Mon, 30 Sep 2019 00:53:42 GMT

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

Re: How do I create a cumulative time variable in my dataset?

ChrisNZ — Mon, 30 Sep 2019 01:10:38 GMT

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

Re: How do I create a cumulative time variable in my dataset?

emilysully — Mon, 30 Sep 2019 01:16:32 GMT

Hello,

Thank you so much for your response. That is the gist of what I am looking
for and what I have generally come up with in my attempts. However, the
cumulative time does not match up with the time variable. For example, the
09:00:00 would correspond to hour 33 as 12 hours would have passed from
21:00:00 to 09:00:00.

Emily

Re: How do I create a cumulative time variable in my dataset?

ChrisNZ — Mon, 30 Sep 2019 01:18:23 GMT

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

Re: How do I create a cumulative time variable in my dataset?

PaigeMiller — Mon, 30 Sep 2019 01:31:42 GMT

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.

Re: How do I create a cumulative time variable in my dataset?

emilysully — Mon, 30 Sep 2019 02:40:32 GMT

Hello,

Thank you for your response. Yes, you can assume that.

Emily

Re: How do I create a cumulative time variable in my dataset?

Reeza — Mon, 30 Sep 2019 15:38:32 GMT

Do you have dates to go alone with those times? If so this becomes a trivial calculation but otherwise you're making a lot of assumptions that likely don't hold all the time.

Re: How do I create a cumulative time variable in my dataset?

emilysully — Mon, 30 Sep 2019 15:58:32 GMT

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

Re: How do I create a cumulative time variable in my dataset?

Reeza — Mon, 30 Sep 2019 16:01:40 GMT

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

Re: How do I create a cumulative time variable in my dataset?

Tom — Mon, 30 Sep 2019 18:51:59 GMT

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