st: Average If with Multiple Conditions

[Lisa Wang <lhwang0925@gmail.com>]

Dear all,

I am having trouble finding the correct code in Stata. A sample of my
data looks like this:

    +----------------------------------+
     | student   d_r   marks   timeline |
     |----------------------------------|
  1. |       A   145   33.06         -1 |
  2. |       A   100   13.76          0 |
  3. |       A    80   33.11          0 |
  4. |       A   100   54.75          1 |
  5. |       A    80   32.45          1 |
  6. |       A   145    13.9          2 |
     |----------------------------------|
  7. |       B   145   13.87         -1 |
  8. |       B   100   32.59         -1 |
  9. |       B   145   31.66          0 |
 10. |       B    80   13.58          0 |
 11. |       B   100   24.45          1 |
 12. |       B    80   55.14          1 |
     |----------------------------------|
 13. |       C   145   32.28         -1 |
 14. |       C   100   13.74         -1 |
 15. |       C   145   96.18          0 |
 16. |       C    80   41.73          0 |
 17. |       C   100   18.02          1 |
 18. |       C    80   91.63          2 |
     +----------------------------------+


What I would like to do is an = AVERAGEIFS(marks, d_r, 145, timeline,
-1) in the first cell.

The desired end result would be:

---------------------------------------------------|
  1. |    .            -1         0             1           2 |
  2. |  145   26.403    63.92     -           13.9 |
  3. |  100   23.165     13.76   32.4067    - |
  4. |   80          -        29.473 43.795    91.63 |
     +----------------------------------------------------+
Otherwise, as some output table or in some new columns/cells is fine
as well, so that I can average out all the marks again for each
particular timeline day (eg. day 0 average = 35.7177).



I did this in Excel but unsure as to how to do the same in Stata,
which is what I would like to learn better and then reduce the
reliance on Excel. Coupled with the fact that I have 1.3 million
observations in my dataset, which Excel won't be able to process as
efficiently as Stata.

I think I need to do -levelsof d_r- and also - levelsof timeline- to
find all the unique values that act as the set of criteria in my
averageif and then store this as a local macro -local new_local =
r(levels)-? But any guidance is greatly appreciated.


Many thanks,
Lisa
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/