Re: Rolling count for last 3 months

nyc_user · Posted 08-29-2018 03:20 PM

Trying to get a rolling count of logins for last 3 months based on reporting month end date.

Data have:

EndOfMonth_Reporting	USERID	LoginMDY90
31OCT2017	976	23OCT2017
30NOV2017	976	01NOV2017
30NOV2017	976	05NOV2017
30NOV2017	976	08NOV2017
30NOV2017	976	10NOV2017
30NOV2017	976	12NOV2017
30NOV2017	976	16NOV2017
30NOV2017	976	17NOV2017
30NOV2017	976	19NOV2017
30NOV2017	976	21NOV2017
30NOV2017	976	29NOV2017
30NOV2017	976	30NOV2017
31DEC2017	976	04DEC2017
31DEC2017	976	07DEC2017
31DEC2017	976	09DEC2017
31DEC2017	976	11DEC2017
31DEC2017	976	15DEC2017
31DEC2017	976	19DEC2017
31DEC2017	976	21DEC2017
31DEC2017	976	22DEC2017
31JAN2018	976	01JAN2018
31JAN2018	976	02JAN2018
31JAN2018	976	04JAN2018
31JAN2018	976	09JAN2018
31JAN2018	976	10JAN2018
31JAN2018	976	16JAN2018
31JAN2018	976	19JAN2018
31JAN2018	976	23JAN2018
31JAN2018	976	25JAN2018
31JAN2018	976	29JAN2018
28FEB2018	976	01FEB2018
28FEB2018	976	02FEB2018
28FEB2018	976	05FEB2018
28FEB2018	976	12FEB2018
28FEB2018	976	15FEB2018

Data Need:

EndOfMonth_Reporting	Count LoginMD	Count LoginMDY90
31OCT2017	1	1
30NOV2017	11	12
31DEC2017	8	20
31JAN2018	10	29
28FEB2018	5	23

I've been trying unsuccessfully

count(EndOfMonth_Reporting) as count loginmd, sum(EndOfMonth_Reporting and EndOfMonth_Reporting-90) as Count LoginMDY90

PaigeMiller · Posted 08-29-2018 03:23 PM

I'm not aware of an SQL solution. PROC EXPAND can do this if you have it licensed; if not, you can do this in a DATA step. https://communities.sas.com/t5/SAS-Procedures/Proc-Expand-and-performing-a-rolling-sum-based-on-mult...

--
Paige Miller

nyc_user · Posted 08-29-2018 04:45 PM

Thank you Paige Miller !

nyc_user · Posted 08-29-2018 05:01 PM

Paige Miller, can I change the transformout to 90 days vs 3months?

PeterClemmensen · Posted 08-29-2018 03:41 PM

I believe this can be done in SQL, but I don't believe it should be done in SQL.

PROC EXPAND is the way to go

The DATA to DATA Step Macro
Blog: SASnrd

novinosrin · Posted 08-29-2018 03:44 PM


data have;
input EndOfMonth_Reporting :date9.	USERID	LoginMDY90 :date9.;
format EndOfMonth_Reporting LoginMDY90 date9.;
cards;
31-Oct-17	976	23-Oct-17
30-Nov-17	976	1-Nov-17
30-Nov-17	976	5-Nov-17
30-Nov-17	976	8-Nov-17
30-Nov-17	976	10-Nov-17
30-Nov-17	976	12-Nov-17
30-Nov-17	976	16-Nov-17
30-Nov-17	976	17-Nov-17
30-Nov-17	976	19-Nov-17
30-Nov-17	976	21-Nov-17
30-Nov-17	976	29-Nov-17
30-Nov-17	976	30-Nov-17
31-Dec-17	976	4-Dec-17
31-Dec-17	976	7-Dec-17
31-Dec-17	976	9-Dec-17
31-Dec-17	976	11-Dec-17
31-Dec-17	976	15-Dec-17
31-Dec-17	976	19-Dec-17
31-Dec-17	976	21-Dec-17
31-Dec-17	976	22-Dec-17
31-Jan-18	976	1-Jan-18
31-Jan-18	976	2-Jan-18
31-Jan-18	976	4-Jan-18
31-Jan-18	976	9-Jan-18
31-Jan-18	976	10-Jan-18
31-Jan-18	976	16-Jan-18
31-Jan-18	976	19-Jan-18
31-Jan-18	976	23-Jan-18
31-Jan-18	976	25-Jan-18
31-Jan-18	976	29-Jan-18
28-Feb-18	976	1-Feb-18
28-Feb-18	976	2-Feb-18
28-Feb-18	976	5-Feb-18
28-Feb-18	976	12-Feb-18
28-Feb-18	976	15-Feb-18
;
proc sql;
create table want as
select distinct a.userid, a.EndOfMonth_Reporting,sum(intnx('days',a.EndOfMonth_Reporting,-90)<=b.LoginMDY90<=a.EndOfMonth_Reporting) as CountLoginMDY90
from have a, have b
where a.userid=b.userid
group by a.userid, a.EndOfMonth_Reporting,a.LoginMDY90;
quit;

novinosrin · Posted 08-29-2018 04:48 PM

Missed the Count LoginMD in the previous response:

data have;
input EndOfMonth_Reporting :date9.	USERID	LoginMDY90 :date9.;
format EndOfMonth_Reporting LoginMDY90 date9.;
cards;
31-Oct-17	976	23-Oct-17
30-Nov-17	976	1-Nov-17
30-Nov-17	976	5-Nov-17
30-Nov-17	976	8-Nov-17
30-Nov-17	976	10-Nov-17
30-Nov-17	976	12-Nov-17
30-Nov-17	976	16-Nov-17
30-Nov-17	976	17-Nov-17
30-Nov-17	976	19-Nov-17
30-Nov-17	976	21-Nov-17
30-Nov-17	976	29-Nov-17
30-Nov-17	976	30-Nov-17
31-Dec-17	976	4-Dec-17
31-Dec-17	976	7-Dec-17
31-Dec-17	976	9-Dec-17
31-Dec-17	976	11-Dec-17
31-Dec-17	976	15-Dec-17
31-Dec-17	976	19-Dec-17
31-Dec-17	976	21-Dec-17
31-Dec-17	976	22-Dec-17
31-Jan-18	976	1-Jan-18
31-Jan-18	976	2-Jan-18
31-Jan-18	976	4-Jan-18
31-Jan-18	976	9-Jan-18
31-Jan-18	976	10-Jan-18
31-Jan-18	976	16-Jan-18
31-Jan-18	976	19-Jan-18
31-Jan-18	976	23-Jan-18
31-Jan-18	976	25-Jan-18
31-Jan-18	976	29-Jan-18
28-Feb-18	976	1-Feb-18
28-Feb-18	976	2-Feb-18
28-Feb-18	976	5-Feb-18
28-Feb-18	976	12-Feb-18
28-Feb-18	976	15-Feb-18
;
proc sql;
create table want as
select t1.userid, t1.EndOfMonth_Reporting,t2.count_loginmd,CountLoginMDY90
from 
(select distinct a.userid, a.EndOfMonth_Reporting,sum(intnx('days',a.EndOfMonth_Reporting,-90)<=b.LoginMDY90<=a.EndOfMonth_Reporting) as CountLoginMDY90
from have a, have b
where a.userid=b.userid
group by a.userid, a.EndOfMonth_Reporting,a.LoginMDY90 ) t1
inner join 
(select userid,EndOfMonth_Reporting,count(EndOfMonth_Reporting) as count_loginmd
from have 
group by userid, EndOfMonth_Reporting ) t2
on t1.userid=t2.userid and t1.EndOfMonth_Reporting=t2.EndOfMonth_Reporting;
quit;

novinosrin · Posted 08-29-2018 04:56 PM

Cleaner and easier to follow:


proc sql;
create table want as
select distinct a.userid, a.EndOfMonth_Reporting,a.count_loginmd,sum(intnx('days',a.EndOfMonth_Reporting,-90)<=b.LoginMDY90<=a.EndOfMonth_Reporting) as CountLoginMDY90
from 
(select *,count(EndOfMonth_Reporting) as count_loginmd
from have 
group by userid, EndOfMonth_Reporting) a, have b
where a.userid=b.userid
group by a.userid, a.EndOfMonth_Reporting,a.LoginMDY90;
quit;

nyc_user · Posted 08-29-2018 04:52 PM

Thank you novinosrin !

novinosrin · Posted 08-29-2018 05:44 PM

Hash fun:

data have;
input EndOfMonth_Reporting :date9.	USERID	LoginMDY90 :date9.;
format EndOfMonth_Reporting LoginMDY90 date9.;
cards;
31-Oct-17	976	23-Oct-17
30-Nov-17	976	1-Nov-17
30-Nov-17	976	5-Nov-17
30-Nov-17	976	8-Nov-17
30-Nov-17	976	10-Nov-17
30-Nov-17	976	12-Nov-17
30-Nov-17	976	16-Nov-17
30-Nov-17	976	17-Nov-17
30-Nov-17	976	19-Nov-17
30-Nov-17	976	21-Nov-17
30-Nov-17	976	29-Nov-17
30-Nov-17	976	30-Nov-17
31-Dec-17	976	4-Dec-17
31-Dec-17	976	7-Dec-17
31-Dec-17	976	9-Dec-17
31-Dec-17	976	11-Dec-17
31-Dec-17	976	15-Dec-17
31-Dec-17	976	19-Dec-17
31-Dec-17	976	21-Dec-17
31-Dec-17	976	22-Dec-17
31-Jan-18	976	1-Jan-18
31-Jan-18	976	2-Jan-18
31-Jan-18	976	4-Jan-18
31-Jan-18	976	9-Jan-18
31-Jan-18	976	10-Jan-18
31-Jan-18	976	16-Jan-18
31-Jan-18	976	19-Jan-18
31-Jan-18	976	23-Jan-18
31-Jan-18	976	25-Jan-18
31-Jan-18	976	29-Jan-18
28-Feb-18	976	1-Feb-18
28-Feb-18	976	2-Feb-18
28-Feb-18	976	5-Feb-18
28-Feb-18	976	12-Feb-18
28-Feb-18	976	15-Feb-18
;

data want ;
if _n_=1 then do;
	if 0 then set have;
   dcl hash H (dataset:'have(drop=EndOfMonth_Reporting)',ordered: "A",multidata:'y') ;
   h.definekey  ("userid") ;
   h.definedata ("LoginMDY90") ;
   h.definedone () ;
end;
do CountLoginMD=1 by 1 until(last.EndOfMonth_Reporting);
set have(drop=LoginMDY90);
by userid EndOfMonth_Reporting;
if last.EndOfMonth_Reporting then
do rc=h.find() by 0 while(rc=0);
CountLoginMDY90=sum(CountLoginMDY90,intnx('days',EndOfMonth_Reporting,-90)<=LoginMDY90<=EndOfMonth_Reporting );
rc=h.find_next();
end;
end;
keep userid EndOfMonth_Reporting CountLoginMD CountLoginMDY90;
run;

SAS Innovate 2025: Call for Content

Classroom Training Available!