Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: RE: Count no of unique occurrences on a prior-12-month basis


From   "Marietta Jones" <marjones75@googlemail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: RE: Count no of unique occurrences on a prior-12-month basis
Date   Mon, 4 Aug 2008 18:08:23 +0100

Hi Nick,

It works fine for me now as well.
Thanks for your help!

Marietta

On Mon, Aug 4, 2008 at 4:25 PM, Nick Cox <n.j.cox@durham.ac.uk> wrote:
> I just tried it out and it worked fine for me.
>
> Are you familiar with /// as a way to comment out end-of-lines?
> Are you copying left and right quotes correctly?
>
> 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
> }
>
> Nick
> n.j.cox@durham.ac.uk
>
> Marietta Jones
>
> 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?
>
>
> On Fri, Aug 1, 2008 at 5:19 PM, Nick Cox <n.j.cox@durham.ac.uk> wrote:
>
>> 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
>
>> 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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index