BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yxs8016
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

4 REPLIES 4
PGStats
Opal | Level 21

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
yxs8016
Fluorite | Level 6

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.

Ksharp
Super User
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;
yxs8016
Fluorite | Level 6
Thank you for your help!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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