Time Duration in Different Locations

[angeliquec]

Hi, I need help on how to get the duration between two timeslots:

In my HAVE dataset below, a mobile's log-in time is recorded. With that, I'd like to get the duration a mobile spent in "home" and in "work", whose values in my WANT dataset. 

data have;
input mobile time :datetime21. scenario location $4.;
datalines;
600000001 13FEB2019:00:01:00 0 home
600000001 13FEB2019:00:03:00 0 home
600000001 13FEB2019:00:04:00 0 home
600000001 13FEB2019:00:05:00 1 work
600000001 13FEB2019:00:11:00 1 work
600000001  13FEB2019:00:13:00 0 home
;
run;

Resulting dataset:

data want;
input mobile duration :time. location $;
datalines;
600000001 03:00:00 home
600000001 09:00:00 work
;
run;

data want;
set want;
hours=duration/60/60;
run;

Thank you!

[Reeza · Accepted solution]

You can use the NOTSORTED option to do this relatively easily. 

1. Use NOTSORTED to tell SAS to use groups

2. At first of each "group" retain start

3. At last of each "group" calculate duration

4. If a single record for location, exclude that record - not sure this makes sense, but you need to decide how to handle the last records. 

data want;
set have;
by mobile scenario location notsorted;
retain start;

if first.location then start = time;
if last.location then duration = (time - start)/60/60;

*not sure this part is correct;
if first.location and last.location then delete;

if last.location then output;



run;

---

@angeliquec wrote:

Hi, I need help on how to get the duration between two timeslots:

In my HAVE dataset below, a mobile's log-in time is recorded. With that, I'd like to get the duration a mobile spent in "home" and in "work", whose values in my WANT dataset. 

data have;
input mobile time :datetime21. scenario location $4.;
datalines;
600000001 13FEB2019:00:01:00 0 home
600000001 13FEB2019:00:03:00 0 home
600000001 13FEB2019:00:04:00 0 home
600000001 13FEB2019:00:05:00 1 work
600000001 13FEB2019:00:11:00 1 work
600000001  13FEB2019:00:13:00 0 home
;
run;

Resulting dataset:

data want;
input mobile duration :time. location $;
datalines;
600000001 03:00:00 home
600000001 09:00:00 work
;
run;

data want;
set want;
hours=duration/60/60;
run;

Thank you!

---

[VRKiwi]

If you want to sum the locations even when they are apart, this would work.

data have;
input MOBILE TIME :DATETIME21. SCENARIO LOCATION $4.;
datalines;
600000001 13FEB2019:00:01:00 0 home
600000001 13FEB2019:00:03:00 0 home
600000001 13FEB2019:00:04:00 0 home
600000001 13FEB2019:00:05:00 1 work
600000001 13FEB2019:00:11:00 1 work
600000001 13FEB2019:00:13:00 0 home
600000001 13FEB2019:00:15:00 0 home
600000001 13FEB2019:00:16:00 1 work
;
run;

data WANT;
  set HAVE (keep= TIME LOCATION);
  REALTIME = dif(TIME); 
  if LOCATION = lag(LOCATION) then do;
    HOMETIME+REALTIME*(LOCATION = 'home') ; 
    WORKTIME+REALTIME*(LOCATION = 'work') ; 
  end;
  else REALTIME = .; 
 HOURS = REALTIME/60;
run;

proc print data=want(Keep= HOURS LOCATION REALTIME HOMETIME WORKTIME);
run;