Can anyone confirm if the following code works for solving logins that occurred in previous 30 days per user? proc sql;
create table records2 AS
select *
from records
where (datepart(login_datetime) between '01JAN2019'd and '31Dec2020'd) ;
quit;
data records3;
set records2;
date_30_days_before = intnx('day',datepart(login_datetime),-30,'B') ;
format date_30_days_before DATE9.;
run;
proc print data=records3;
run;
proc sql;
create table consolidate_logins as
select userid,
login_datetime,
date_30_days_before,
count(*) as logins
from records3
group by userid,
login_datetime,
date_30_days_before;
quit;
proc sql;
create table logins_month as
select a.userid,
a.login_datetime format=mmddyy10.,
a.logins as logins_that_day,
sum(b.logins) as logins_last_30_days /*Number of logins on, or within 30 days of, the login_date*/
from consolidate_logins as a
left join consolidate_logins as b
on a.userid = b.userid
and b.value <= a.login_datetime
and b.value >= a.date_30_days_before
group by a.userid,
a.login_datetime,
a.logins
;
quit;
... View more