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!
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;
can you post some sample data?
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!
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;
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;
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
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.)
Unfortunately. I am in BeiJing ,China.
Ksharp
How about some virtual beer?:smileylaugh:
www.virtualbeer.com/
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.