topic Re: Create observations for unaccounted time intervals in SAS Procedures

Create observations for unaccounted time intervals

abyyy — Thu, 09 Mar 2023 03:18:13 GMT

Hi! I need help with filling in missing time intervals to account for all minutes of the day starting from 12:00 AM to 11:59 PM.

Variables: ID date start_time end_time activity

How do I fill in the missing time intervals with activity="default minutes" to include the end_time of the previous observation and the start_time of the following observation for a whole dataset? For example,

ID date start_time end_time activity

1 03/01/2002 12:00 AM 6:00 AM sleep

1 03/01/2002 6:00 AM 6:30 AM eating

1 03/01/2002 7:30 AM 8:00 AM riding in car

1 03/01/2002 1:00 PM 2:00 PM default minutes

1 03/01/2002 8:00 PM 11:59 PM sleep

I appreciate any help!!!

Re: Create observations for unaccounted time intervals

Kurt_Bremser — Thu, 09 Mar 2023 11:39:50 GMT

data have;
infile datalines dlm="," dsd truncover;
input ID $ date :ddmmyy10. (start_time end_time) (:ANYDTTME.) activity $30.;
format date ddmmyy10. start_time end_time time5.;
datalines;
1,03/01/2002,12:00 AM,6:00 AM,sleep
1,03/01/2002,6:00 AM,6:30 AM,eating
1,03/01/2002,7:30 AM,8:00 AM,riding in car 
1,03/01/2002,1:00 PM,2:00 PM,default minutes
1,03/01/2002,8:00 PM,11:59 PM,sleep
;

data want;
merge
  have
  have (firstobs=2 keep=id start_time rename=(id=_id start_time=_start));
;
output;
if id = _id and _start gt end_time
then do;
  start_time = end_time;
  end_time = _start;
  activity = "default minutes";
  output;
end;
drop _id _start;
run;

As you can see, I had to make assumptions about your data and use one of the dangerous "ANY" informats.

You should avoid this by using a data step with DATALINES to present your data; we can then create an exact replica of your dataset(s) with a simple copy/paste and submit, without having to make guesses about attributes and content.

Re: Create observations for unaccounted time intervals

abyyy — Fri, 10 Mar 2023 00:37:52 GMT

Thank you so much!!! This worked out perfectly!!!