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

# Re: st: Average If with Multiple Conditions

 From Nick Cox To statalist@hsphsun2.harvard.edu Subject Re: st: Average If with Multiple Conditions Date Mon, 29 Oct 2012 18:29:51 +0000

```A -collapse- gets you a table with the same dimensions and values.

. collapse marks, by(d_r timeline)

. tabdisp d_r timeline, c(marks)

--------------------------------------------------
|                timeline
d_r |       -1         0         1         2
----------+---------------------------------------
80 |           29.47333    43.795     91.63
100 |   23.165     13.76  32.40667
145 | 26.40333     63.92                13.9
--------------------------------------------------

Is that what you want?

On Mon, Oct 29, 2012 at 6:03 PM, Lisa Wang <lhwang0925@gmail.com> wrote:

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