Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# st: Average If with Multiple Conditions

 From Lisa Wang To statalist@hsphsun2.harvard.edu Subject st: Average If with Multiple Conditions Date Tue, 30 Oct 2012 05:03:27 +1100

```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/
```