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!
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;
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;
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.
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;
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 25. 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.