Help using Base SAS procedures

Count freq of each person by month

Reply
Occasional Contributor
Posts: 10

Count freq of each person by month

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.
SAS Super FREQ
Posts: 8,868

Re: Count freq of each person by month

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
Super User
Posts: 10,044

Re: Count freq of each person by month

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
Super Contributor
Posts: 578

Re: Count freq of each person by month

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;
Super User
Posts: 11,343

Re: Count freq of each person by month

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.
Trusted Advisor
Posts: 1,301

Re: Count freq of each person by month

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;
Occasional Contributor
Posts: 10

Re: Count freq of each person by month

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
Super User
Posts: 10,044

Re: Count freq of each person by month

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
Ask a Question
Discussion stats
  • 7 replies
  • 192 views
  • 0 likes
  • 6 in conversation