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

# st: Calculating prior consensus under multiple conditions

 From vskirnev@smail.uni-koeln.de To statalist@hsphsun2.harvard.edu Subject st: Calculating prior consensus under multiple conditions Date Sun, 29 Jan 2012 22:23:33 +0100

Looking at this mailing list has been a great help for my research project so far - I hope you can help me with my current problem.
```
```
I need to calculate a prior consensus analyst earnings forecast (mean of prior forecasts by other analysts). This calculation needs to be done only for the last forecasts by every analyst for a specific period (e.g., a financial quarter). I have tagged them with variable last=1 (previous forecasts by the same analyst in the same period have last=0).
```
So the logic of this calculation looks like this:

If an observation is last=1 then {

```
go back 90 days from the date of this forecast (variable 'fordate') and calculate mean of variable 'value' (what was forecasted) using:
```
```
- forecasts of OTHER analysts (there is a variable 'analyst' unique for each one) - of those use ONLY the last ones - meaning if within last 90 days analyst A issued 4 forecasts for the same company, I need to use only the most recent one
```
}

This has to be repeated for all observations with last=1.

```
My attempts so far were not successful, so I hope you can give me some good advice.
```

```
Just as an example of how data might look like - assuming all these were done for the end of 4th quarter (31dec2010):
```
Company  Analyst  ForecastDate  ForecastValue Last
3M       A        15oct2010     80            0
3M       B        17oct2010     85            0
3M       C        21oct2010     86            0
3M       B        07nov2010     79            1
3M       A        21nov2010     75            0
3M       A        29nov2010     83            1
3M       C        13dec2010     84            1

Prior consensus has to be calculated three times: for observations 4, 6 and 7.

For 4 (analyst B): consensus = (obs1 + obs3)/2
For 6 (analyst A): consensus = (obs3 + obs4)/2
For 7 (analyst C): consensus = (obs4 + obs6)/2

I hope this clarifies the problem. Thanks for your input in advance!

Vitaly Skirnevskiy

+49(0)221 168 163 38
University of Cologne
Albertus-Magnus-Platz
50923 Cologne
Germany

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```