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.
@ddavies wrote:
I would like to combine the client_count where actual_date_b is within 90 minutes?
Within 90 minutes of what?
When actual_date_b is within 90 minutes of itself, so in the table, the 3rd and 4th record would end up combined
@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.
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
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: