Help with rolling 3 months moving total

Reply
Frequent Contributor
Posts: 124

Help with rolling 3 months moving total

I have following data:

data have;

  input yrmn $6. id $2.  dose $10.  value 5.;

cards;

201101 12 xyz 2.5mg 18

201102 12 xyz 2.5mg  8

201104 12 xyz 2.5mg 10

201105 12 xyz 2.5mg 16

201106 12 xyz 2.5mg 12

201107 12 xyz 2.5mg 18

201108 12 xyz 2.5mg 12

201109 12 xyz 2.5mg 14

201111 12 xyz 2.5mg 10

201112 12 xyz 2.5mg 16

201101 12 xyz 5mg   12

201102 12 xyz 5mg    9

201103 12 xyz 5mg   10

201104 12 xyz 5mg   14

201105 12 xyz 5mg   13

201106 12 xyz 5mg   10

I want moving  rolling 3 month Total Values as follows:

yrmn     id    dose        Value    Value_3m

201101 12   xyz 2.5mg   18       18

201102 12   xyz 2.5mg     8       26

201104 12   xyz 2.5mg   10       18

201105 12   xyz 2.5mg   16       26   

201106 12   xyz 2.5mg   12       38

201107 12   xyz 2.5mg   18       46

201108 12   xyz 2.5mg   12       42

201109 12   xyz 2.5mg   14       44

201111 12   xyz 2.5mg   10       36

201112 12   xyz 2.5mg   16       40

201101 12   xyz 5mg      12       12

201102 12   xyz 5mg       9        21

201103 12   xyz 5mg      10       31

201104 12   xyz 5mg      14       33

201105 12   xyz 5mg      13       37

201107 12   xyz 5mg      10       23

In the above data you will note that there are missing yrmn.

So you have to account that in calculating 3 month rolling total.

Any help in this will be highly appreciated.

Super User
Posts: 5,255

Re: Help with rolling 3 months moving total

You could create a format that holds overlapping rolling periods.

Use that with the MULTILABEL functionality in PROC SUMMARY (for storing the results) or PROC TABULATE (for creating a report).

Data never sleeps
Frequent Contributor
Posts: 124

Re: Help with rolling 3 months moving total

How do I do that?

Please help...

Respected Advisor
Posts: 3,777

Re: Help with rolling 3 months moving total

For dose 2.5 it doesn't look like you have done the sum the same way at month 4(missing 3) and month 11(missing 10).  I looks like you did not include the missing for week 10.  Maybe I'm just don't understand.

PROC Star
Posts: 7,356

Re: Help with rolling 3 months moving total

Here is an alternative way to accomplish the task:

data have;

  informat yrmn $6.;

  informat id $2.;

  informat dose $10.;

  informat value 5.;

  input yrmn id dose & value;

cards;

201101 12 xyz 2.5mg  18

201102 12 xyz 2.5mg   8

201104 12 xyz 2.5mg  10

201105 12 xyz 2.5mg  16

201106 12 xyz 2.5mg  12

201107 12 xyz 2.5mg  18

201108 12 xyz 2.5mg  12

201109 12 xyz 2.5mg  14

201111 12 xyz 2.5mg  10

201112 12 xyz 2.5mg  16

201101 12 xyz 5mg    12

201102 12 xyz 5mg     9

201103 12 xyz 5mg    10

201104 12 xyz 5mg    14

201105 12 xyz 5mg    13

201106 12 xyz 5mg    10

;

data need (drop=_Smiley Happy;

  format yrmn yymmN6.;

  set have (rename=(yrmn=_yrmn));

  by id dose;

  retain _last;

  yrmn=input(_yrmn,yymmn6.);

  output;

  if first.dose or intck("month", _last, yrmn) eq 1 then do;

    _last=yrmn;

  end;

  else do;

    _holdmth=yrmn;

    do _i=1 to intck("month", _last, _holdmth)-1 ;

      call missing(value);

      yrmn=intnx("month", _last, _i);

      output;

    end;

    _last=_holdmth;

  end;

run;

proc sort data=need;

  by id dose yrmn;

run;

data want (drop=_Smiley Happy;

  set need;

  by id dose;

  array stack {0:2} _temporary_;

  if first.dose then do;

    call missing(of stack{*});

    _counter=0;

  end;

  _counter+1;

  stack{mod(_counter,3)} = value;

  total = sum(of stack{*});

  if not missing(value) then output;

run;

Frequent Contributor
Posts: 124

Re: Help with rolling 3 months moving total

Thanks everybody for help...

Respected Advisor
Posts: 3,777

Re: Help with rolling 3 months moving total

I was think maybe somethink with LAG1 and LAG2 would work OK.  You do have to fill in the missing months too.  Really a job for proc EXPAND but I don't have it.

data have;
   input yrmn yymmn6. id:$2.  dose & $10.  value 5.;
  
format yrmn yymmn.;
  
cards;
201101 12 xyz 2.5mg  18
201102 12 xyz 2.5mg   8
201104 12 xyz 2.5mg  10
201105 12 xyz 2.5mg  16
201106 12 xyz 2.5mg  12
201107 12 xyz 2.5mg  18
201108 12 xyz 2.5mg  12
201109 12 xyz 2.5mg  14
201111 12 xyz 2.5mg  10
201112 12 xyz 2.5mg  16
201101 12 xyz 5mg    12
201102 12 xyz 5mg     9
201103 12 xyz 5mg    10
201104 12 xyz 5mg    14
201105 12 xyz 5mg    13
201106 12 xyz 5mg    10
;;;;
   run;
proc print;
  
run;
data classdata;
   if 0 then set have(keep=yrmn);
   do year=2011;
     
do month=1 to 12;
         yrmn = mdy(month,
1,year);
         output;
        
end;
     
end;
  
keep yrmn;
   run;
proc summary data=have classdata=classdata nway;
  
by id dose;
   class yrmn;
   output out=expand(drop=_Smiley Happy idgroup(out(value)=);
   run;
data lag2;
   do _n_=1 by 1 until(last.dose);
      set expand;
      by id dose;
      array l[2];
      l1 = lag1(value);
      l2 = lag2(value);
     
if _n_ le dim(l) then do i=_n_ to dim(l); l=.; end;
     
if not missing(value) then do;
         value_3m=sum(value,l1,l2);        
        
output;
        
end;
     
drop i;
      end;
  
run;
proc print;
  
run;
Ask a Question
Discussion stats
  • 6 replies
  • 414 views
  • 0 likes
  • 4 in conversation