BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
Linlin
Lapis Lazuli | Level 10

can you post some sample data?

RussellNonBrand
Calcite | Level 5

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
Opal | Level 21

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
Linlin
Lapis Lazuli | Level 10

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
Super User

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
Calcite | Level 5

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
Super User

Unfortunately. I am in BeiJing ,China.

Smiley Happy

Ksharp

Haikuo
Onyx | Level 15

How about some virtual beer?:smileylaugh:

www.virtualbeer.com/

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
  • 8 replies
  • 5106 views
  • 7 likes
  • 5 in conversation