topic Re: Combine Records within a certain amount of time in SAS Programming

Combine Records within a certain amount of time

ddavies — Mon, 11 May 2020 10:46:14 GMT

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.

Re: Combine Records within a certain amount of time

PaigeMiller — Mon, 11 May 2020 10:49:11 GMT

@ddavies wrote:

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

Within 90 minutes of what?

Re: Combine Records within a certain amount of time

ddavies — Mon, 11 May 2020 11:04:03 GMT

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

Re: Combine Records within a certain amount of time

PaigeMiller — Mon, 11 May 2020 13:09:45 GMT

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

Re: Combine Records within a certain amount of time

Ksharp — Mon, 11 May 2020 11:57:58 GMT

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;

Re: Combine Records within a certain amount of time

ballardw — Mon, 11 May 2020 14:11:52 GMT

@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.