Counting distinct customers from rolling 12 months?

[RussellNonBrand]

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!

[Linlin]

can you post some sample data?

[RussellNonBrand]

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!

[PGStats · Accepted solution]

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;

[Linlin]

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;

[Ksharp]

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

[RussellNonBrand]

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.)

[Ksharp]

Unfortunately. I am in BeiJing ,China.

Ksharp

[Haikuo]

How about some virtual beer?:smileylaugh:

www.virtualbeer.com/