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
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...
Thank you Paige Miller !
Paige Miller, can I change the transformout to 90 days vs 3months?
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
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;
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;
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;
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.