Hi Nick,
Thank you very much for your detailed response.
I will definitely have a look at the Stata Journal tips that you suggested.
The code that you suggested:
local N = _N
> gen count = .
> qui forval i = 1/`N' {
> egen tag = tag(Investor_id) ///
> if Company_no == Company_no[`i'] & ///
> inrange(Date, Date[`i'] - 365, Date[`i'])
>
> count if tag
>
> replace count = r(N) in `i'
>
> drop tag
> }
must have a small error somewhere, as I get an error message when I
try to run it.
The error message I get is "varlist not allowed".
After setting trace on, I found that this error refers to the line of
code "egen tag = tag(Investor_id) ///".
Do you have any idea what the error in the code may be?
Many thanks,
Marietta
On Fri, Aug 1, 2008 at 5:19 PM, Nick Cox <[email protected]> wrote:
> It's a bit naughty posting the same question three times. It was clear
> first time, just that nobody was able and willing to answer before now.
>
> I don't understand the last sentence ("If there are no observations over
> the past
> 12 months, the value of the new variable should equal 1"), but otherwise
> here goes:
>
> This problem is the subject of a Stata Journal Tip in which the
> associated ideas are discussed at excruciating length.
>
> SJ-7-3 pr0033 . . . . . . . . . . . . . . Stata tip 51: Events in
> intervals
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N.
> J. Cox
> Q3/07 SJ 7(3):440--443 (no
> commands)
> tip for counting or summarizing irregularly spaced
> events in intervals
>
> The basic idea explored in that Tip is, however, quite simple.
>
> We want to count how many investors held stock in that company in the
> previous 12 months. The Tip analyses it something like this:
>
> Focus on observation 1:
>
> count if Company_no == Company_no[1] & inrange(Date, Date[1] - 365,
> Date[1])
>
> To extend this to all observations, we loop over observations and make
> sure that we store values as they emerge from a -count-:
>
> local N = _N
> gen count = .
> qui forval i = 1/`N' {
> count if Company_no == Company_no[`i'] & inrange(Date, Date[`i']
> - 365, Date[`i'])
> replace count = r(N) in `i'
> }
>
> Except that that doesn't count each investor just once, as stipulated.
>
> Here's a way of building that in:
>
> local N = _N
> gen count = .
> qui forval i = 1/`N' {
> egen tag = tag(Investor_id) ///
> if Company_no == Company_no[`i'] & ///
> inrange(Date, Date[`i'] - 365, Date[`i'])
>
> count if tag
>
> replace count = r(N) in `i'
>
> drop tag
> }
>
> Warning: untested code. If the precise definition here of previous year
> doesn't suit, then you need to change it.
>
> If this problem interests you, here is more of the same:
>
> SJ-7-4 dm0033 . . . . . . Speaking Stata: Counting groups, especially
> panels
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N.
> J. Cox
> Q4/07 SJ 7(4):571--581 (no
> commands)
> discusses how to count panels through reduction commands
> or through tabulation commands and how to overcome
> problems that do not yield easily to these approaches
>
> SJ-7-1 pr0029 . . . . . . . . . . . . . . . Speaking Stata: Making it
> count
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N.
> J. Cox
> Q1/07 SJ 7(1):117--130 (no
> commands)
> discusses count used with a loop over observations
> or variables
>
> Nick
> [email protected]
>
> Marietta Jones
>
> I have the following dataset:
>
> Company_no Date Investor_id
> 1 03/01/2000 1
> 1 04/09/2000 1
> 1 12/12/2001 2
> 2 13/12/2000 4
> 2 07/08/2001 7
> 3 09/08/2000 4
> 3 19/03/2001 4
> 3 02/05/2001 5
> 3 03/12/2001 6
>
>
> For each observation, I would like to calculate the number of distinct
> investors owning shares in a given company in the previous 12 months.
> The new variable (No_investors_past_12_months) will look as follows:
>
> Company_no Date Investor_id No_investors_past_12_months
> 1 03/01/2000 1 1
> 1 04/09/2000 1 1
> 1 12/12/2001 2 1
> 2 13/12/2000 4 1
> 2 07/08/2001 7 2
> 3 09/08/2000 4 1
> 3 19/03/2001 4 1
> 3 02/05/2001 5 2
> 3 03/12/2001 6 3
>
> For example, for company 2 on 07/08/2001 there are two investors who
> have owned shares of this company within the past 12 months.
>
> The idea is that the same investor should be counted only once and we
> should count all observations over the past 12 months, including the
> current observation date. If there are no observations over the past
> 12 months, the value of the new variable should equal 1.
>
>
> *
> * 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/
>
*
* 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/
