topic Re: Rolling count for last 3 months in SAS Programming

Rolling count for last 3 months

nyc_user — Wed, 29 Aug 2018 19:20:01 GMT

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

Re: Rolling count for last 3 months

PaigeMiller — Wed, 29 Aug 2018 19:25:47 GMT

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-multiple/td-p/165303

Re: Rolling count for last 3 months

PeterClemmensen — Wed, 29 Aug 2018 19:41:54 GMT

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

Re: Rolling count for last 3 months

novinosrin — Wed, 29 Aug 2018 19:44:12 GMT


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;

Re: Rolling count for last 3 months

nyc_user — Wed, 29 Aug 2018 20:45:15 GMT

Thank you Paige Miller !

Re: Rolling count for last 3 months

novinosrin — Wed, 29 Aug 2018 20:48:00 GMT

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;

Re: Rolling count for last 3 months

nyc_user — Wed, 29 Aug 2018 20:52:05 GMT

Thank you novinosrin !

Re: Rolling count for last 3 months

novinosrin — Wed, 29 Aug 2018 20:56:05 GMT

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;

Re: Rolling count for last 3 months

nyc_user — Wed, 29 Aug 2018 21:01:45 GMT

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

Re: Rolling count for last 3 months

novinosrin — Wed, 29 Aug 2018 21:44:57 GMT

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;