topic How to count number of of call in the a week in SAS Programming

How to count number of of call in the a week

yxs8016 — Wed, 01 Mar 2017 00:48:02 GMT

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!

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

PGStats — Wed, 01 Mar 2017 01:20:01 GMT

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;

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

Ksharp — Wed, 01 Mar 2017 10:33:09 GMT

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;

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

yxs8016 — Wed, 01 Mar 2017 16:21:42 GMT

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.

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

yxs8016 — Wed, 01 Mar 2017 16:22:57 GMT

Thank you for your help!