topic Re: How to insert a row corresponding to start and end of time period and take nearest value in SAS Programming

How to insert a row corresponding to start and end of time period and take nearest value

SVSzwec — Wed, 28 Sep 2022 08:05:04 GMT

Hi all,

I have a dataset where I am trying to work with measures over specific time periods (e.g. Midnight to 6AM, 6AM to 10AM etc.). The data is measured regularly in roughly 5 minute intervals however it is not always measured on the hour. I would like to try and create an entry corresponding to the start and end of each time period (time_cat) and pull the measure value closest to that time point.

For example in the first case I want to create an entry at time 00:00:00 and take the measure value of the entry following it. In the very last case I would want ot create an entry at 21:00:00 and take the measure value preceding it.

I'm not really sure where the best place to start with this is and any help would be great appreciated.

Thanks!

data WORK.measure_tp;
infile datalines dsd truncover;
input sensor:BEST. in_range_flag:32. datepart:DDMMYY. timepart:TIME. time_cat:32.;
format sensor BEST. datepart DDMMYY. timepart TIME.;
label sensor="Sensor Measure";
datalines;
7.1 1 18/07/19 0:00:35 0
7.4 1 18/07/19 0:05:35 0
6.8 1 18/07/19 0:10:35 0
6.8 1 18/07/19 0:15:35 0
6.4 1 18/07/19 0:20:35 0
6.9 1 18/07/19 5:40:34 0
7.2 1 18/07/19 5:45:34 0
7.3 1 18/07/19 5:50:34 0
7.8 1 18/07/19 5:55:34 0
7 1 18/07/19 6:00:34 1
6.7 1 18/07/19 6:05:34 1
6.4 1 18/07/19 6:10:34 1
6.3 1 18/07/19 6:15:34 1
6 1 18/07/19 9:45:33 1
5.9 1 18/07/19 9:50:33 1
5.6 1 18/07/19 9:55:33 1
5.4 1 18/07/19 10:00:33 .
5.2 1 18/07/19 10:05:34 .
5.5 1 18/07/19 10:10:33 .
5.9 1 18/07/19 10:15:33 .
6.9 1 18/07/19 10:45:33 .
7 1 18/07/19 10:50:33 .
7 1 18/07/19 10:55:33 .
7.2 1 18/07/19 11:00:33 2
7.3 1 18/07/19 11:05:33 2
7.4 1 18/07/19 11:10:33 2
7.3 1 18/07/19 11:15:34 2
11.2 0 18/07/19 14:45:33 2
11.6 0 18/07/19 14:50:33 2
11.8 0 18/07/19 14:55:33 2
12.2 0 18/07/19 15:00:33 .
12.3 0 18/07/19 15:05:33 .
12.3 0 18/07/19 15:10:33 .
12.4 0 18/07/19 15:15:33 .
15.6 0 18/07/19 16:45:33 .
15.7 0 18/07/19 16:50:33 .
16 0 18/07/19 16:55:33 .
16 0 18/07/19 17:00:33 3
15.9 0 18/07/19 17:05:34 3
16 0 18/07/19 17:10:34 3
16 0 18/07/19 17:15:32 3
15.9 0 18/07/19 17:20:32 3
7.7 1 18/07/19 20:45:32 3
10 1 18/07/19 20:45:38 3
10.2 0 18/07/19 20:50:32 3
10.2 0 18/07/19 20:55:33 3
;;;;

Re: How to insert a row corresponding to start and end of time period and take nearest value

Shmuel — Wed, 28 Sep 2022 08:05:16 GMT

Try next code. I hope I understood you correctly:

data want;
 set measures_tp;
   by datepart timepart;
        retain nxt;
       if first.datepert then do;
         nxt = timepart;
         timepart=0; output;
         timepart = nxt;
      end;
      
      output;
      if last.datepart then do;
         timepart = input('21:00',time5.);
         output;
       end;
run;

Re: How to insert a row corresponding to start and end of time period and take nearest value

SVSzwec — Wed, 28 Sep 2022 08:19:32 GMT

Thanks for the code Shmuel, sadly it's not quite what I need.

I'm hoping to have each time category sandwiched by two time points.

For time_cat = 0, I want to create an entry with time 00:00:00 and an entry at time 06:00:00.

For time_cat = 1, an entry at the start with time 06:00:00 and one at the end at 10:00:00..

For time_cat = 2, one starting at 11:00:00AM and one ending at 15:00:00.

For time_cat = 3, the first entry starting at 17:00:00 and one final entry ending at 21:00:00.

In each case I'm hoping to create those entries and pull the sensor value that occurs closest to that time point.

Re: How to insert a row corresponding to start and end of time period and take nearest value

Shmuel — Wed, 28 Sep 2022 14:13:59 GMT

I assume:

1) your data have more then one day.

2) the data is already sorted by datepart timepart and

time_cat is assigned correctly according to timepart/

In such case I preffer to create all time_cat(s) entries and merge them with the given data as in next tested code. Do you realy need the sensor on the new added rows?

It can be done within the merge step or in a new step by using similar methods as in my previous post.

The code is:

%let dt_start = '18JUL2019'd;  /*** adapt dt_start dt_end dates as need ***/
%let dt_end = '18JUL2019'd;
data cats;
  format datepart DDMMYY. timepart TIME.;

  do datepart = &dt_start to &dt_end by 1;
     timepart = 0; time_cat=0; output;
	 timepart = input('06:00't,hhmm5.); time_cat=0; output;
	 
	 timepart = input('06:00't,hhmm5.); time_cat=1; output;
	 timepart = input('10:00't,hhmm5.); time_cat=1; output;
	 
	 timepart = input('11:00't,hhmm5.); time_cat=2; output;
	 timepart = input('15:00't,hhmm5.); time_cat=2; output;

	 timepart = input('17:00't,hhmm5.); time_cat=3; output;
	 timepart = input('21:00't,hhmm5.); time_cat=3; output;
  end;
run;

data temp;
 merge measure_tp cats;
   by datepart timepart;
run;

Re: How to insert a row corresponding to start and end of time period and take nearest value

SVSzwec — Wed, 28 Sep 2022 23:45:38 GMT

Many thanks Shmuel! The combination of your two blocks of code do exactly what I want for this!