[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: SAS Forum: Calculating Sum Across Rolling Years
From: Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date: 2017-06-27 17:35:47
Message-ID: 4140710274060889.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]
SAS Forum: Calculating Sum Across Rolling Years
see
https://goo.gl/8yrYuR
https://communities.sas.com/t5/SAS-Enterprise-Guide/Calculating-Sum-Across-Rolling-Years/m-p/371008
/* T1005730 SAS-L: Rolling sum of events by ID with arbitrary window size R and Base SAS/WPS
WORKING CODE (two solutions
WPS/R (basically a one liner - (after filling in missing dates))
have=as.data.table(read_sas('d:/sd1/havmrgall.sas7bdat'));
want<-have[,max := rollapply(EVENT, width=3, sum, align='right', partial=F, fill=NA), by=ID];
import r=want data=wrk.wantwps;
SAS (complete solution - may be slow until SAS updates its compiler/interpreter?)
===================================================================================
* power of just three meta data values;
do until (last.id);
set have;
by id;
when( first.id ) call symputx("beg",put(date,8.));
when ( last.id ) do;
call symputx("end",put(date,8.));
call symputx("id",put(id,8.));
DOSUBL SEPARATELY FOR EACH ID
%let dim=%eval(&end - &beg + 1);
*fill in mising dates;
data havgen
do date =&beg to &end;
event=0;
output;
end;
data want
array sumcum[0:&dim.] _temporary_;
merge havgen(in=lon) have(where=(id=&id)) end=dne;
by date;
tot=sum(tot,event);
sumcum[_n_]=tot;
do difs= 0 to %eval(&dim. - &window) by 1;
dif=sumcum[difs+3] - sum(sumcum[difs],0);
end;
go back
HAVE
====
%let window=3;
Obs ID DATE EVENT
1 14011 01JAN1960 1
2 14011 03JAN1960 1
3 14011 06JAN1960 1
4 14011 10JAN1960 1
5 24011 01JUN1960 1
6 24011 03JUN1960 1
7 24011 06JUN1960 1
8 24011 10JUN1960 1
WANT (first ID for explanation purposes)
====
Up to 40 obs from rda.havmrg total obs
Obs TOT ID DATE EVENT ROLLING
SUM
1 1 14011 01JAN1960 1
2 1 14011 02JAN1960 0
3 2 14011 03JAN1960 1 2 = 1 + 0 + 1
4 2 14011 04JAN1960 0 1 = 0 + 1 + 0
5 2 14011 05JAN1960 0 1 = 1 + 0 + 0
6 3 14011 06JAN1960 1 1 = 0 + 0 + 1
7 3 14011 07JAN1960 0 1 = 0 + 1 + 0
8 3 14011 08JAN1960 0 1 = 1 + 0 + 0
9 3 14011 09JAN1960 0 0 = 0 + 0 + 0
10 4 14011 10JAN1960 1 1 = 0 + 0 + 1
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/ | (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
%symdel beg end id;
proc datasets lib=work kill;
run;quit;
data have(keep=id date event );
retain id;
informat date date9.;
retain event 1;
input ID Date;
cards4;
14011 01JAN1960
14011 03JAN1960
14011 06JAN1960
14011 10JAN1960
24011 01JUN1960
24011 03JUN1960
24011 06JUN1960
24011 10JUN1960
;;;;
run;quit;
* _ _ _
___ __ _ ___ ___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _` / __| / __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_| \__ \ \__ \ (_) | | |_| | |_| | (_) | | | |
|___/\__,_|___/ |___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
%let window=3;
data _null_;
* power of just three meta values;
do until (last.id);
set have;
by id;
select;
when( first.id ) call symputx("beg",put(date,8.));
when ( last.id ) do;
call symputx("end",put(date,8.));
call symputx("id",put(id,8.));
end;
otherwise;
end;
end;
* do for each ID (should be fast if compiled properly;
rc=dosubl('
data havgen;
retain id &id;
do date =&beg to &end;
event=0;
output;
end;
run;quit;
%let dim=%eval(&end - &beg + 1);
data
havmrg(keep=id dtefin dif)
havnot(keep=id start end duration events
label="not enough data");
retain tot 0;
array sumcum[0:&dim.] _temporary_;
merge havgen(in=lon) have(where=(id=&id)) end=dne;
by date;
if lon;
tot=sum(tot,event);
sumcum[_n_]=tot;
put sumcum[_n_];
if dne then do;
if &dim < &window then do;
start=put(&beg,date9.);
end=put(&end,date9.);
duration=&dim;
events=tot;
putlog id= start= end= duration= events=;
output havnot;
end;
else do;
do difs= 0 to %eval(&dim. - &window) by 1;
dif=sumcum[difs+3] - sum(sumcum[difs],0);
dtefin= &beg + difs + 2;
put dtefin date9. + 2 dif=;
output havmrg;
end;
end;
end;
run;quit;
proc append data=havmrg base=havmrgall;run;quit;
proc append data=havnot base=havgennot force;run;quit;
');
run;quit;
proc print data=havmrgall;
format dtefin date9.;
run;quit;
* _ _ _ __ ______ ____
_ __ ___ __ _| | _____ __| | __ _| |_ __ _ \ \ / / _ \/ ___|
| '_ ` _ \ / _` | |/ / _ \ / _` |/ _` | __/ _` | \ \ /\ / /| |_) \___ \
| | | | | | (_| | < __/ | (_| | (_| | || (_| | \ V V / | __/ ___) |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| \_/\_/ |_| |____/
;
data _null_;
* power of just three meta values;
do until (last.id);
set have;
by id;
select;
when( first.id ) call symputx("beg",put(date,8.));
when ( last.id ) do;
call symputx("end",put(date,8.));
call symputx("id",put(id,8.));
end;
otherwise;
end;
end;
* do for each ID (should be fast if compiled properly?);
rc=dosubl('
data havgen;
retain id &id;
do date =&beg to &end;
event=0;
output;
end;
run;quit;
data sd1.havmrg;
merge havgen(in=lon) have(where=(id=&id)) end=dne;
by date;
run;quit;
proc append data=sd1.havmrg base=sd1.havmrgall;run;quit;
');
run;quit;
*_ ______ ____ _ _ _
\ \ / / _ \/ ___| ___ ___ | |_ _| |_(_) ___ _ __
\ \ /\ / /| |_) \___ \ / __|/ _ \| | | | | __| |/ _ \| '_ \
\ V V / | __/ ___) | \__ \ (_) | | |_| | |_| | (_) | | | |
\_/\_/ |_| |____/ |___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
%utl_submit_wps64("
libname sd1 'd:/sd1';
options set=R_HOME 'C:/Program Files/R/R-3.4.0';
libname wrk '%sysfunc(pathname(work))';
proc r;
submit;
source('c:/Program Files/R/R-3.4.0/etc/Rprofile.site',echo=T);
library(haven);
library(zoo);
library(data.table);
have=as.data.table(read_sas('d:/sd1/havmrgall.sas7bdat'));
head(have);
want<-have[,max := rollapply(EVENT, width=3, sum, align='right', partial=F, fill=NA), by=ID];
str(want);
colnames(want)<-colnames(have);
want$DATE=rownames(want);
endsubmit;
import r=want data=wrk.wantwps;
run;quit;
");
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic