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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.