How to count number of of call in the a week

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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?

Could someone please help me? I'd really appreciate it!!! Thank you!


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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,802

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: 9,854

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

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

Thank you for your help!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 138 views
  • 4 likes
  • 3 in conversation