[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    StackOverflow: Rolling sum_of six months by group
From:       Roger Deangelis <roger_deangelis () COMCAST ! NET>
Date:       2021-11-19 21:39:55
Message-ID: 2959370945468436.WA.rogerdeangeliscomcast.net () listserv ! uga ! edu
[Download RAW message or body]

%let pgm=utl-rolling-sum_of-six-months-by-group;

Rolling sum_of six months by group

github
https://tinyurl.com/p7utf4ke
https://github.com/rogerjdeangelis/utl-rolling-sum_of-six-months-by-group

StackOverflow
https://tinyurl.com/ymxbema4
https://stackoverflow.com/questions/68857908/sas-code-sum-of-last-n-rows-for-every-row

This type og problem is best solved using R or Python.
R functions are more flexible and you are less

/*                   _
(_)_ __  _ __  _   _| |_
| | `_ \| `_ \| | | | __|
| | | | | |_) | |_| | |_
|_|_| |_| .__/ \__,_|\__|
        |_|
*/


options validvarname=upcase;

libname sd1 "d:/sd1";

data sd1.have;
   informat dte $12.;
   input Dte ID Number @@;
cards4;
2018-07-01 1 0 2018-08-01 1 0 2018-09-01 1 1 2018-10-01 1 3 2018-11-01 1 1
2018-12-01 1 2 2019-01-01 1 0 2019-02-01 1 0 2019-03-01 1 1 2019-04-01 1 0
2019-05-01 1 0 2019-06-01 1 0 2019-07-01 1 1 2019-08-01 1 0 2019-09-01 1 0
2019-10-01 1 2 2019-11-01 1 0 2019-12-01 1 0 2020-01-01 1 0 2020-02-01 1 0
2020-03-01 1 0 2020-04-01 1 0 2020-05-01 1 0 2020-06-01 1 0 2020-07-01 1 0
2020-08-01 1 1 2020-09-01 1 0 2020-10-01 1 0 2020-11-01 1 1 2020-12-01 1 0
2021-01-01 1 0 2021-02-01 1 1 2021-03-01 1 1 2021-04-01 1 0 2018-07-01 2 0
2018-07-01 2 0 2018-08-01 2 0 2018-09-01 2 1 2018-10-01 2 3 2018-11-01 2 2
2018-12-01 2 2 2019-01-01 2 0 2019-02-01 2 0 2019-03-01 2 2 2019-04-01 2 0
2019-05-01 2 0 2019-06-01 2 0 2019-07-01 2 2 2019-08-01 2 0 2019-09-01 2 0
2019-10-01 2 2 2019-11-01 2 0 2019-12-01 2 0 2020-01-01 2 0 2020-02-01 2 0
2020-03-01 2 0 2020-04-01 2 0 2020-05-01 2 0 2020-06-01 2 0 2020-07-01 2 0
2020-08-01 2 2 2020-09-01 2 0 2020-10-01 2 0 2020-11-01 2 2 2020-12-01 2 0
2021-01-01 2 0 2021-02-01 2 2 2021-03-01 2 2 2021-04-01 2 0

;;;;
run;quit;

Up to 40 obs from SD1.HAVE total obs=69

                                  |
Obs       DTE        ID    NUMBER |  RULES
                                  |
  1    2018-07-01     1       0   |   7  0+0+1+3+1+2
  2    2018-08-01     1       0   |   7  0+1+3+1+2+0
  3    2018-09-01     1       1   |
  4    2018-10-01     1       3   |
  5    2018-11-01     1       1   |
  6    2018-12-01     1       2   |
  7    2019-01-01     1       0   |
  8    2019-02-01     1       0   |
  9    2019-03-01     1       1   |
 10    2019-04-01     1       0   |
 11    2019-05-01     1       0   |
 12    2019-06-01     1       0   |
...

/*
 _ __  _ __ ___   ___ ___  ___ ___
| `_ \| `__/ _ \ / __/ _ \/ __/ __|
| |_) | | | (_) | (_|  __/\__ \__ \
| .__/|_|  \___/ \___\___||___/___/
|_|
*/


%utl_submit_r64('
library(dplyr);
library(zoo);
library(haven);
library(SASxport);
library(data.table);
have<-read_sas("d:/sd1/have.sas7bdat");
want <- as.data.table(have %>%
 group_by(ID) %>%
 dplyr::mutate(ROLL6 = rollsumr(NUMBER, k = 6, fill = NA, align = "left")));
write.xport(want,file="d:/xpt/want.xpt");
');

libname xpt xport "d:/xpt/want.xpt";

proc print data=xpt.want;
run;quit;

/*           _               _
  ___  _   _| |_ _ __  _   _| |_
 / _ \| | | | __| `_ \| | | | __|
| (_) | |_| | |_| |_) | |_| | |_
 \___/ \__,_|\__| .__/ \__,_|\__|
                |_|
*/


    Obs       DTE        ID    NUMBER    ROLL6

      1    2018-07-01     1       0        7
      2    2018-08-01     1       0        7
      3    2018-09-01     1       1        7
      4    2018-10-01     1       3        7
      5    2018-11-01     1       1        4
      6    2018-12-01     1       2        3
      7    2019-01-01     1       0        1
      8    2019-02-01     1       0        2
      9    2019-03-01     1       1        2
     10    2019-04-01     1       0        1
     11    2019-05-01     1       0        3
     12    2019-06-01     1       0        3
     13    2019-07-01     1       1        3
     14    2019-08-01     1       0        2
     15    2019-09-01     1       0        2
     16    2019-10-01     1       2        2
     17    2019-11-01     1       0        0
     18    2019-12-01     1       0        0
     19    2020-01-01     1       0        0
     20    2020-02-01     1       0        0
     21    2020-03-01     1       0        1
     22    2020-04-01     1       0        1
     23    2020-05-01     1       0        1
     24    2020-06-01     1       0        2
     25    2020-07-01     1       0        2
     26    2020-08-01     1       1        2
     27    2020-09-01     1       0        2
     28    2020-10-01     1       0        3
     29    2020-11-01     1       1        3
     30    2020-12-01     1       0        .   Because of the shift up
     31    2021-01-01     1       0        .
     32    2021-02-01     1       1        .
     33    2021-03-01     1       1        .
     34    2021-04-01     1       0        .
     35    2018-07-01     2       0        6
     36    2018-07-01     2       0        8
     37    2018-08-01     2       0        8
   ....

/*              _
  ___ _ __   __| |
 / _ \ `_ \ / _` |
|  __/ | | | (_| |
 \___|_| |_|\__,_|

*/
[prev in list] [next in list] [prev in thread] [next in thread]