Combine Records within a certain amount of time

[ddavies]

I would like to combine the client_count where actual_date_b is within 90 minutes?

actual_date	actual_date_b	client_cnt
4/13/2020	13APR2020:16:30:00	10
4/14/2020	14APR2020:16:25:00	9
4/16/2020	16APR2020:15:00:00	12
4/16/2020	16APR2020:16:20:00	1
4/13/2020	13APR2020:12:00:00	2
4/15/2020	15APR2020:12:00:00	1

The final table would only show actual_date and client_count.

[PaigeMiller]

---

@ddavies wrote:

I would like to combine the client_count where actual_date_b is within 90 minutes?

---

Within 90 minutes of what?

[ddavies]

When actual_date_b is within 90 minutes of itself, so in the table, the 3rd and 4th record would end up combined

[ballardw]

---

@ddavies wrote:

When actual_date_b is within 90 minutes of itself, so in the table, the 3rd and 4th record would end up combined

---

Since any value should be within "90 minutes of itself" need to provide a better rule.

Maybe in terms of "within 90 minutes of the previous (or any previous)" value.

[PaigeMiller]

data have2;
     set have;
     prev_actual_date_b = lag(actual_date_b);
     if (actual_date_b - prev_actual_date_b)>90*60 then count+1;
run;
proc summary data=have2 nway;
    class count;
    var client_cnt;
    id date;
    output out=want sum=;
run;

you don't say what want if you get three records like this

16APR2020:15:00:00

16APR2020:16:20:00

16APR2020:17:20:00

[Ksharp]

This could give you a start .

data have;
infile cards expandtabs truncover;
input actual_date : mmddyy10.	actual_date_b : datetime32.	client_cnt;
format actual_date  mmddyy10.	actual_date_b  datetime32.;
cards;
4/13/2020	13APR2020:16:30:00	10
4/14/2020	14APR2020:16:25:00	9
4/16/2020	16APR2020:15:00:00	12
4/16/2020	16APR2020:16:20:00	1
4/13/2020	13APR2020:12:00:00	2
4/15/2020	15APR2020:12:00:00	1
;
run;

proc sql;
create table want as
select a.actual_date_b as group,b.*
from have as a,have as b
 where b.actual_date_b between a.actual_date_b and a.actual_date_b+'01:30:00't
  group by a.actual_date_b
   having count(*)>1;
quit;