Help using Base SAS procedures

Counting distinct customers from rolling 12 months?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Counting distinct customers from rolling 12 months?

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!


Accepted Solutions
Solution
‎03-28-2012 10:53 PM
Respected Advisor
Posts: 4,920

Re: Counting distinct customers from rolling 12 months?

Posted in reply to RussellNonBrand

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;

PG

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Counting distinct customers from rolling 12 months?

Posted in reply to RussellNonBrand

can you post some sample data?

Occasional Contributor
Posts: 13

Re: Counting distinct customers from rolling 12 months?

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!

Attachment
Solution
‎03-28-2012 10:53 PM
Respected Advisor
Posts: 4,920

Re: Counting distinct customers from rolling 12 months?

Posted in reply to RussellNonBrand

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;

PG
Super Contributor
Posts: 1,636

Re: Counting distinct customers from rolling 12 months?

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;

Super User
Posts: 10,023

Re: Counting distinct customers from rolling 12 months?

Posted in reply to RussellNonBrand

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

Occasional Contributor
Posts: 13

Re: Counting distinct customers from rolling 12 months?

Posted in reply to 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.)

Super User
Posts: 10,023

Re: Counting distinct customers from rolling 12 months?

Posted in reply to RussellNonBrand

Unfortunately. I am in BeiJing ,China.

Smiley Happy

Ksharp

Respected Advisor
Posts: 3,156

Re: Counting distinct customers from rolling 12 months?

How about some virtual beer?:smileylaugh:

www.virtualbeer.com/

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1188 views
  • 7 likes
  • 5 in conversation