topic Re: Counting distinct customers from rolling 12 months? in SAS Procedures

Counting distinct customers from rolling 12 months?

RussellNonBrand — Thu, 29 Mar 2012 00:51:57 GMT

Hey everyone,

I have been trying to search thet net, but to no avail. I am putting together a new insights deck, and for part of it I need to count unique customers who have done a certain activity within the past 12 months, by month

My final output will look something like:

Month # of Customers (who have done this activity from 12 months back)

Jan-11 10 (i.e. 10 distinct customers have done this actvitiy at least once between 2/1/10 - 1/31/11)

Feb-11 12

Mar-11 15

Apr-11 14

May-11 18

etc...

So if someone had done the activity in, say, January 2010 and April 2010, he would show up in the LTM for January and for March, but drop off for April.

I can't just group by month and add, because then I will pick up duplicate customers.

Please help! Thank you so much!

Counting distinct customers from rolling 12 months?

Linlin — Thu, 29 Mar 2012 01:01:09 GMT

can you post some sample data?

Re: Counting distinct customers from rolling 12 months?

RussellNonBrand — Thu, 29 Mar 2012 01:39:00 GMT

Here is an example of YearMonth variable (YYYYMM) and a sample account number, then the chart on the right is what I want to populate. Realistically I will be pulling about 35 million accounts, so it's a slightly difficult task

Thanks so much!

Re: Counting distinct customers from rolling 12 months?

PGStats — Thu, 29 Mar 2012 02:53:59 GMT

Assuming that dataset HAVE contains variables actDate : a SAS date ( i.e. actDate=mdy(mod(yearMonth, 100), 1, floor(yearMonth/100)) ) and Account_Number, this will do the count:

proc sql;

create table periods as

select distinct

intnx("month",actDate,0,"beginning") as period format=mmyyd7.,

intnx("month",actDate,-11,"beginning") as periodBeg format=yymmdd10.,

intnx("month",actDate,0,"end") as periodEnd format=yymmdd10.

from have;

create table want as

select period, count(distinct Account_Number) as nbCust

from periods inner join have on actDate between periodBeg and periodEnd

group by period;

drop table periods;

select * from want;

quit;

Re: Counting distinct customers from rolling 12 months?

Linlin — Thu, 29 Mar 2012 03:54:16 GMT

Hi PG,

I ran your code using the data OP privided. I like your code very much! Thank you!

data have;

input YearMonth $ Account_Number;

actDate=mdy(input(substr(yearmonth,5,2),2.),1,input(substr(yearmonth,1,4),4.));

cards;

201001 1

201001 2

201002 1

201002 3

201003 4

201003 5

201004 4

201004 5

201004 6

201005 1

201005 5

201006 1

201006 5

201006 7

201006 8

201006 9

201007 5

201007 7

201008 9

201009 4

201010 10

201010 11

201011 1

201011 12

201012 7

201101 1

201102 11

201103 12

;

proc sql;

create table periods as

select distinct

intnx("month",actDate,0,"beginning") as period format=mmyyd7.,

intnx("month",actDate,-11,"beginning") as periodBeg format=yymmdd10.,

intnx("month",actDate,0,"end") as periodEnd format=yymmdd10.

from have;

create table want as

select period, count(distinct Account_Number) as nbCust

from periods inner join have on actDate between periodBeg and periodEnd

group by period;

*drop table periods;

select * from want;

quit;

Re: Counting distinct customers from rolling 12 months?

Ksharp — Thu, 29 Mar 2012 04:24:28 GMT

How about:

data have;
 input month : yymmn. no;
 format month yymmn.;
 cards;
201001     1
201001     2
201002     1
201002     3
201003     4
201003     5
201004     4
201004     5
201004     6
201005     1
201005     5
201006     1
201006     5
201006     7
201006     8
201006     9
201007     5
201007     7
201008     9
201009     4
201010     10
201010     11
201011     1
201011     12
201012     7
201101     1
201102     11
201103     12
;
run;



proc sql;
 select  distinct month ,(select count(distinct no)  from have where month between intnx('year',a.month,-1,'s')+1  and intnx('month',a.month,0,'e') ) as count
  from have as a ;
quit;

Ksharp

Message was edited by: xia keshan

Re: Counting distinct customers from rolling 12 months?

RussellNonBrand — Thu, 29 Mar 2012 04:50:45 GMT

Wow.. you all are lifesavers. Thank you so much!

Ever have that week where you've put in almost 50 hours by Wednesday getting a new project ready for presentation to S/EVP's... working with your manager to get it all correct... then your manager's boss tells you 2 days before presentation that it's not showing what he wants to see? Yep, that was my day. You all just saved me so much headache.

Anyone live near Atlanta, GA? I owe you a beer. Dead serious. (I could use one as well.)

Re: Counting distinct customers from rolling 12 months?

Ksharp — Thu, 29 Mar 2012 07:27:56 GMT

Unfortunately. I am in BeiJing ,China.

Ksharp

Re: Counting distinct customers from rolling 12 months?

Haikuo — Thu, 29 Mar 2012 14:01:16 GMT

How about some virtual beer?:smileylaugh: