Solved
New Contributor
Posts: 3

# How to count number of of call in the a week

Suppose I have sorted sample dataset looks like this

CustomerID   CallID  Date

123                1          01/30/2017

123                2          01/30/2017

123                3          02/03/2017

123                4          02/07/2017

123                5          02/08/2017

I want to count how many calls did I received from the same customer in the past 7 days as of each date. The desired output dataset would be

CustomerID   CallID  Date                    NumOfCallsInOneWeek

123                1          01/30/2017         1

123                2          01/30/2017         2

123                3          02/03/2017         3

123                4          02/07/2017         2

123                5          02/08/2017         3

Note as of 02/07/2017, the 2 calls on 01/30/2017 are received over a week ago, so they are not counted.

How could I do it using either data step or proc sql?

Accepted Solutions
Solution
‎03-01-2017 11:22 AM
Super User
Posts: 10,787

## Re: How to count number of of call in the a week

``````data have;
input CustomerID   CallID  Date :mmddyy10.;
format date yymmdd10.;
datalines;
123                1          01/30/2017
123                2          01/30/2017
123                3          02/03/2017
123                4          02/07/2017
123                5          02/08/2017
;
run;
data want;
set have;
by customerid;
array x{9999} _temporary_;
if first.customerid then do;n=0;call missing(of x{*});end;
n+1;
x{n}=date;
count=0;
do i=1 to n;
if date-6<=x{i}<=date then count+1;
end;
drop n i;
run;``````

All Replies
Posts: 5,541

## Re: How to count number of of call in the a week

I think the number of calls for callId=1 should be two, unless callId represents an ever increasing sequence,. i.e. unless we know that callId=1 occured before callId=2 because 1 < 2.

``````data have;
input CustomerID   CallID  Date :mmddyy10.;
format date yymmdd10.;
datalines;
123                1          01/30/2017
123                2          01/30/2017
123                3          02/03/2017
123                4          02/07/2017
123                5          02/08/2017
;

proc sql;
create table want as
select
a.CustomerID, a.CallID, a.Date,
count(b.callId) as NumOfCallsInOneWeek
from
have as a inner join
have as b on
a.CustomerID=b.CustomerID and
b.date between intnx("week", a.date, -1, "same") and a.date
group by a.CustomerID, a.CallID, a.Date;
select * from want;
quit;``````
PG
New Contributor
Posts: 3

## Re: How to count number of of call in the a week

You are right. I should've described it more clearly. I am assuming CallID=1 occured before CallID=2. To be more specific, the column Date should be DateTime.

I'm sorry that I miss guided you. I really appreciate your response. Thank you.

Solution
‎03-01-2017 11:22 AM
Super User
Posts: 10,787

## Re: How to count number of of call in the a week

``````data have;
input CustomerID   CallID  Date :mmddyy10.;
format date yymmdd10.;
datalines;
123                1          01/30/2017
123                2          01/30/2017
123                3          02/03/2017
123                4          02/07/2017
123                5          02/08/2017
;
run;
data want;
set have;
by customerid;
array x{9999} _temporary_;
if first.customerid then do;n=0;call missing(of x{*});end;
n+1;
x{n}=date;
count=0;
do i=1 to n;
if date-6<=x{i}<=date then count+1;
end;
drop n i;
run;``````
New Contributor
Posts: 3