title: How to collapse data by week correctly in Stata?
body: <p>I have a transaction level dataset and I want to collapse and calculate weekly average price. The dataset can be simplified as follows,</p>
  
  <pre><code>clear 
  input str9 date quantity price id
  "01jan2010" 50 70 1
  "02jan2010" 60 80 2
  "02jan2010" 70 90 3
  "04jan2010" 70 95 4
  "08jan2010" 60 81 5
  "09jan2010" 70 88 6
  "12jan2010" 55 87 7
  "13jan2010" 52 88 8
  end
  gen date2=date(date,"DMY")
  format date2 %td
  drop date
  </code></pre>
  
  <p>I want to create a variable <code>date3</code>. For every transaction happened in a week, <code>date3</code> is the Monday of that week.</p>
  
  <p>Here's the code I have:</p>
  
  <pre><code>sort date2
  gen date3=date2 if dow(date2)==1
  replace date3=date3[_n-1] if missing(date3)
  format date3 %td
  </code></pre>
  
  <p>However, there are Mondays with no transactions, but the rest of the week has transactions. In those cases, <code>date3</code> is not the Monday date of that week, but Monday date in the weeks before. </p>
  
  <p>My data becomes the following using the above code:</p>
  
  <pre><code>quantity    price   id  date2       date3
  50          70  1   01jan2010   
  60          80  2   02jan2010   
  70          90  3   02jan2010   
  70          95  4   04jan2010   04jan2010
  60          81  5   08jan2010   04jan2010
  70          88  6   09jan2010   04jan2010
  55          87  7   12jan2010   04jan2010
  52          88  8   13jan2010   04jan2010
  </code></pre>
  
  <p>To me, it does not matter if <code>id</code> =1,2,3 have no <code>date3</code>. What I am concerned is that <code>id</code>=7 and <code>id</code>=8 should have a <code>date3</code> of 11jan2010. But because there is no transaction on that day, the date becomes 04jan2010. Is there a way to fix this?</p>
  
  <p>(I was thinking of constructing a new dataset with consecutive dates since 01jan2010 and then <code>merge</code> with the one above, and then <code>drop</code> if missing quantity of price. But I was wondering if there's a more efficient way).</p>
  
  <hr>
  
  <p>In addition, I have a weekly index data that reports on every Friday since 01jan2010. If I use <code>wofd</code> command, Stata will generate 53 weeks in 2010. (Or more precisely, two 2010w52.) How can I get just 52 weeks in Stata?</p>
  
  <p>(I found this <a href="http://www.stata.com/statalist/archive/2012-02/msg01030.html" rel="nofollow">http://www.stata.com/statalist/archive/2012-02/msg01030.html</a> but I still cannot figure out how this can help solve my problem. )</p>