BookmarkSubscribeRSS Feed
ZoeyElle
Calcite | Level 5
Hi,

I have a list which details customers calling in to a company to inquire on things.
I need to count how many times EACH customer calls and how many times they call PER MONTH.

Here's what my list looks like:

Name. Cardno. Datetime
Victor. 123456. 02NOV2010:10:42:32 PM
James. 567890. 16NOV2010:12:01:16 PM
James. 567890. 16NOV2010:02:30:43 PM
Serena. 324243. 07DEC2010:09:34:21 PM
Alfred. 453545. 08DEC2010:06:32:12 PM
James. 567890. 08DEC2010:07:18:45 AM

My result should be like below:

Name. Nov. Dec. Freq
Victor. 1. 0. 1
James. 2. 1. 3
Serena. 0. 1. 1
Alfred. 0. 1. 1

Urgent help is very much appreciated.
7 REPLIES 7
Cynthia_sas
SAS Super FREQ
Hi:
This looks like the kind of report that could easily be accomplished with a PROC TABULATE or a PROC REPORT. The trick will be to extract the date portion from your DATETIME variable and then either format the date portion to only show the month or the month and year or to extract just the month from the date portion. The statistic for a count of observations is the N statistic (N is how you get a COUNT and PCTN is the percent of the COUNT). The universal CLASS variable ALL will be how you get the final total (what you show as the FREQ) column.

You could probably summarize the data with PROC MEANS or PROC SQL, too, but there's no need to make 2 passes through the data after you read it, when TABULATE or REPORT would generate the report with 1 pass through the data.

cynthia
Ksharp
Super User
Hi.
Just as Cynthia said proc report can do it in one proc.But If you want use dataset, that need some more code.

[pre]
data temp;
input name $ cardno $ datatime & datetime24.;
format datatime datetime24.;
datalines;
Victor. 123456. 02NOV2010:10:42:32 PM
James. 567890. 16NOV2010:12:01:16 PM
James. 567890. 16NOV2010:02:30:43 PM
Serena. 324243. 07DEC2010:09:34:21 PM
Alfred. 453545. 08DEC2010:06:32:12 PM
James. 567890. 08DEC2010:07:18:45 AM
;
run;
data temp;
set temp;
month=put(datepart(datatime),monname.);
run;
options label;
proc report data=temp(keep=name month) nowd out=result(drop=_break_);
column name month n;
define name /group;
define month/across ' ';
define n /'Freq';
run;
[/pre]

Ksharp
DBailey
Lapis Lazuli | Level 10
TabA
Name. Cardno. DT
Victor. 123456. 02NOV2010:10:42:32 PM
James. 567890. 16NOV2010:12:01:16 PM
James. 567890. 16NOV2010:02:30:43 PM
Serena. 324243. 07DEC2010:09:34:21 PM
Alfred. 453545. 08DEC2010:06:32:12 PM
James. 567890. 08DEC2010:07:18:45 AM

proc sql;
select
name,
cardno,
sum(case when intnx('month',datepart(dt),0,begin)='01Nov2010' then 1 else 0 end) as Nov,
sum(case when intnx('month',datepart(dt),0,begin)='01Dec2010' then 1 else 0 end) as Dec,
count(*) as Total_Calls
from TabA
group by Name, cardno;
quit;
ballardw
Super User
I would ask if you want by MONTH or by YEAR and MONTH.
Using a format such as YYMON9. on your DT variable in most of the approaches would avoid creating a new data set.
FriedEgg
SAS Employee
data temp;
input name $ cardno $ datatime & datetime24.;
format datatime datetime24.;
datalines;
Victor. 123456. 02NOV2010:10:42:32 PM
James. 567890. 16NOV2010:12:01:16 PM
James. 567890. 16NOV2010:02:30:43 PM
Serena. 324243. 07DEC2010:09:34:21 PM
Alfred. 453545. 08DEC2010:06:32:12 PM
James. 567890. 08DEC2010:07:18:45 AM
;
run;
data temp;
set temp;
date=datepart(datatime);
yes=1;
run;
proc tabulate data=temp out=_temp format=comma20.;
var yes;
class name; class date;
table name,date*(yes=' '*sum=' ') yes=' '*sum=' ';
/* change format below to yymon9. etc for different results */
format date monname.;
run;
ZoeyElle
Calcite | Level 5
Hi everyone....
Thank you for all the help....
Proc tabulate solved the problem.
Thanks again!

Now, i need to do more than a simple count.
Data will be the same format as before.

Here's what i need to do : only count the record as 1 if the current and previous call gap>=30 mins

Name. Cardno. Datetime. Count
Victor. 12345. 9/8/2010 8:04:28 AM. 1
Victor. 12345. 9/8/2010 8:09:42 AM 0
Victor. 12345. 9/8/2010 8:11:17 AM. 0
Victor. 12345. 9/8/2010 8:12:13 AM. 0
Victor. 12345. 9/8/2010 9:11:12 AM. 1
Victor. 12345. 9/8/2010 4:05:57 AM. 1

Explanation: 1st observation is counted as it is the first call. 2nd, 3rd, 4th obs not counted as they are all within 30 mins of first call. Obs 5 is counted as it exceeds 30 mins when compared to 1st obs. Obs6 also counted as it exceeds 30 mins when compared to obs 5.

Again i need to know how many times EACH customer calls and how many times they call PER MONTH but with the new condition.

Need all the help i can get. Thanks. Message was edited by: Zoey Elle
Ksharp
Super User
Your obs 6 has some problem?


[pre]
options datestyle=MDY;
data temp;
input Name $ Cardno $ Datetime & anydtdtm24. ;
format datetime datetime24.;
datalines;
Victor. 12345. 9/8/2010:8:04:28 AM
Victor. 12345. 9/8/2010:8:09:42 AM
Victor. 12345. 9/8/2010:8:11:17 AM
Victor. 12345. 9/8/2010:8:12:13 AM
Victor. 12345. 9/8/2010:9:11:12 AM
Victor. 12345. 9/8/2010:4:05:57 PM
;
run;
data want;
set temp;
retain time;
if _n_ eq 1 then do;
time=timepart(datetime);
count=1;
end;
else do;
if timepart(datetime) - time gt "00:30:00"t then do;
time=timepart(datetime);
count=1;
end;
else count=0;
end;
run;

[/pre]






Ksharp

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 949 views
  • 0 likes
  • 6 in conversation