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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!


 

View solution in original post

2 REPLIES 2
Reeza
Super User

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
Obsidian | Level 7

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; 

 

 

 

 

timesheet.PNG

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 572 views
  • 0 likes
  • 3 in conversation