title: sas - calculate moving average for grouped data with BY statement
body: <p>I'm a SAS beginner and I'm curious if the following task can be done much more simple as it is currently in my head.</p>
  
  <p>I have the following (simplified) meta data in a table named user_date_money:</p>
  
  <p>User - Date - Money</p>
  
  <p>with various users and dates for every calendar day (for the last 4 years). The data is ordered by User ASC and Date ASC, sample data looks like this:</p>
  
  <pre><code>User  | Date     | Money
  Anna   23.10.2013   5
  Anna   24.10.2013   1
  Anna   25.10.2013   12
        ....       
  Aron   23.10.2013   5
  Aron   24.10.2013   12
  Aron   25.10.2013   4 
       ....
  Zoe    23.10.2013   1
  Zoe    24.10.2013   1
  Zoe    25.10.2013   0
  </code></pre>
  
  <p>I now want to calculate a five day moving average for the Money. I started with the pretty popular apprach with the lag() function like this:</p>
  
  <pre><code>data cma; 
  set user_date_money;
  if missing(money) then
  do;
  OBS = 0;
  money = 0.0;
  end;
  else OBS = 1;
  money5 = lag5(money);
  OBS5= lag5(obs);
  if missing(money5) then money5= 0.0;
  if missing(obs5) then obs5= 0;
  
  if _N_ = 1 then
  do;
  SUM = 0.0;
  N = 0;
  end;
  else;
  sum = sum + money-money5;
  n = n + obs-obs5;
  MEAN = sum / n ;
  retain sum n;
  run;
  </code></pre>
  
  <p>as you see, the problem with this method occurs if there if the data step runs into a new user. Aron would get some lagged values from Anna which of course should not happen.</p>
  
  <p>Now my question: I am pretty sure you can handle the user switch by adding some extra fields like laggeduser and by resetting the N, Sum and Mean variables if you notice such a switch but:</p>
  
  <p>Can this be done in an easier way? Perhaps using the BY Clause in any way?
  Thanks for your ideas and help!</p>
  
  <p>Best regards</p>