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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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