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/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.