Re: st: How to count occurrences of specific value

[Nick Cox <njcoxstata@gmail.com>]

The line

replace temp = temp[`i'] + temp[`j']

should presumably be

replace temp = temp[`i'] + temp[`j'] in `i'

Otherwise, -temp- ends up holding the last constant calculated.
Nick
njcoxstata@gmail.com


On 2 May 2013 16:21, Michael Barker <mdb96statalist@gmail.com> wrote:
> It looks like you are comparing each observation to every other
> observation in your data set. If your data are sorted, you only have
> to look back within each 5-year window for each id. It looks like your
> data are sorted descending by date, so the code would look like this:
>
> gen temp = flag
> local N = _N
> forvalues i = 1(1)`N' {
> local j=`i'+1
> while (id[`i']==id[`j'] & (date[`i'] - date[`j'])/365.25 <= 5) {
> replace temp = temp[`i'] + temp[`j']
> local j = `j'+1
> }
>
> If your data were sorted ascending by date, you would just iterate j
> downwards (j = i-1, j=j-1) and start the "forvalues" loop at 2
> (forvalues 2(1)`N' {)
>
> Mike
>
> On Wed, May 1, 2013 at 8:31 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>> Your code wouldn't work as
>>
>> id = id[`i']
>>
>> should be
>>
>> id == id[`i']
>>
>> but I presume you just copied it incorrectly.
>>
>> I don't know a sure-fire way to speed this up. It might just be faster
>> if you -expand-ed the data to one observation for every day. Then the
>> code would be  simpler,  but you would end up with several millions of
>> observations. Or you could translate the code into Mata.
>>
>> I don't think -egen- will help you here at all. The essence of the
>> problem is comparing each observation with others for the same
>> identifier, and -egen- stops at simple recipes of that kind.
>> Nick
>> njcoxstata@gmail.com
>>
>>
>> On 1 May 2013 13:03, Jia Peng <jiapengcass@gmail.com> wrote:
>>> Dear All,
>>>
>>> I have a data set with the following structure,
>>>
>>> id              date                    flag
>>> 95001   14jun2000       1
>>> 95001   12apr2000       1
>>> 95001   16mar2000       0
>>> 95001   16nov1999       0
>>> 95001   10may1999       1
>>> 95001   30mar1995       0
>>> 95002   01nov1989       0
>>> 95002   01mar1985       1
>>> 95002   01jun1983       0
>>> 95002   01may1983       1
>>> 95002   01dec1982       0
>>> 95002   01oct1982       0
>>>
>>> And now, I would like to generate a new variable, say temp, which represents
>>> for each observation how many times flag == 1 has occurred within the same
>>> id from five years ago to the date specified, i.e., for the first
>>> observation, I want to count how many times flag == 1 has occurred with the
>>> id 95001 between 14jun1995 and 14jun2000.
>>>
>>> I have tried to loop over every observation using the following code,
>>>
>>> gen temp = .
>>> local N = _N
>>> forvalues i = 1(1)`N' {
>>> count if flag == 1 & id = id[`i'] & (date[`i'] - date)/365.25 <= 5 &
>>> (date[`i'] - date)/365.25 >= 0
>>> replace temp = r(N) in `i'
>>> }
>>>
>>> However, there are half a million observations in the entire data and the
>>> above code cost hours of time. Is there any way to solve the above problem
>>> more efficiently?
>>>
>>> I have also tried to use -egen-, but all I can get is how many times flag ==
>>> 1 has occurred with the same id. Is there any way to take into consideration
>>> different date ranges in this context?
>>>
>>> Any thoughts?
>>>
>>>
>>> Peng Jia
>>>
>>> *
>>> *   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/
>> *
>> *   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/
> *
> *   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/
*
*   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/