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!
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!
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!
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;
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.