Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: Counting observations between dates by group


From   Nick Cox <njcoxstata@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Counting observations between dates by group
Date   Mon, 12 Sep 2011 09:33:28 +0100

That's fine. Thanks for closing the thread.


On Mon, Sep 12, 2011 at 7:45 AM, Joseph Monte <hmjc66@gmail.com> wrote:
> Nick,
>
> Thanks for the help. Your code worked well. I did require the
> exclusion of the observation in question. Apologize for the errors in
> my code.
>
> Joe
>
>
> On Wed, Sep 7, 2011 at 5:20 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>> 1. I saw a problem with your verbal stipulation "excluding the
>> observation in question" because your code does nothing to exclude the
>> observation in question. So, I bailed out at that point, noting a side
>> issue about -format- as I jumped.
>>
>> You have not addressed this point.
>>
>> 2. I see no problem with -inrange()- as its limits are inclusive.
>>
>> 3. On a mock-up of your data I get this
>>
>> . count if joeid != 4241  & name == "Whlsl" & inrange(date2,
>> d(5may1983), d(16jun1983))
>>    3
>>
>> You don't have a variable -joeid-. You can confirm the result by
>>
>> . count if _n != 4241  & name == "Whlsl" & inrange(date2, d(5may1983),
>> d(16jun1983))
>>    3
>>
>> 4. I don't know what -egen, tag()- is doing for you. I understand the
>> function, but I don't know why it is in your code.I guess you
>> misunderstand what it does.
>>
>> 5. With one thing and another I suggest this as code
>>
>> local N = _N
>> gen count=.
>> qui forval i = 1/`N'{
>>        count if name==name[`i'] & _n != `i' &
>> inrange(date2,before[`i'],after[`i'])
>>        replace count = r(N) in `i'
>> }
>>
>> There is a general discussion of these problems at
>>
>> 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
>>
>> which you may have looked at already.
>>
>>
>> On Wed, Sep 7, 2011 at 4:38 PM, Joseph Monte <hmjc66@gmail.com> wrote:
>>> Nick,
>>>
>>> I believe that -inrange- is not appropriate for my situation which is
>>> probably why the count is wrong in obs 4241.
>>>
>>> Here is basically what I want:-
>>>
>>> For each observation, I want to count the number of observations by
>>> "name" where "date2" occurs in between "before" and "after" (both
>>> inclusive). So, to use obs 4241 as an example of what I am looking
>>> for, obs 4241 should have a count of 3 since, within the name "Whlsl",
>>> date2 values of 13may1983, 23may1983, 14jun1983 occur in between
>>> 05may1983 (before)  and 16jun1983 (after).
>>>
>>> I would appreciate any additional help with the code.
>>>
>>> Thanks,
>>>
>>> Joe
>>>
>>>
>>>
>>> On Wed, Sep 7, 2011 at 11:59 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>>>> Nothing in your code excludes the observation in question.
>>>>
>>>> To do that, you could add the extra condition
>>>>
>>>> _n != `i'
>>>>
>>>> Incidentally, the loop in
>>>>
>>>> foreach var of varlist date1 date2 before after {
>>>>        format `var' %td
>>>> }
>>>>
>>>> is not needed as -format- will take a varlist.
>>>>
>>>> On Wed, Sep 7, 2011 at 11:05 AM, Joseph Monte <hmjc66@gmail.com> wrote:
>>>>
>>>>> I would appreciate your help. I am trying to create a variable that
>>>>> counts the number of times that the variable "date2" occurs between
>>>>> the variable "before" and the variable "after" (which are dates as
>>>>> well) by name, excluding the observation in question. The output below
>>>>> is exactly what I want based on the code I created below.
>>>>>
>>>>> The problem is that in my actual output, a quick glance at some of the
>>>>> observations showed an error in the count for one observation. The
>>>>> actual output is shown below the SAMPLE CODE AND OUTPUT. Specifically,
>>>>> obs 4241 should have a count of 3, not 2 (i.e. date2 values of
>>>>> 13may1983, 23may1983, 14jun1983). Is there something wrong with my
>>>>> code?
>>>>>
>>>>> SAMPLE CODE AND OUTPUT
>>>>>
>>>>> clear
>>>>> input str1 name date1 date2
>>>>> "A" 1234 1254
>>>>> "A" 1242 1260
>>>>> "A" 1249 1451
>>>>> "B" 1222 1352
>>>>> "B" 1245 1265
>>>>> "C" 1299 1354
>>>>> end
>>>>>
>>>>> gen before=date1-21
>>>>> gen after=date1+21
>>>>>
>>>>> foreach var of varlist date1 date2 before after {
>>>>>        format `var' %td
>>>>> }
>>>>>
>>>>> local N = _N
>>>>> gen count=.
>>>>> qui forval i = 1/`N'{
>>>>>        egen tag = tag(count) if name==name[`i'] &
>>>>> inrange(date2,before[`i'],after[`i'])
>>>>>                count if tag
>>>>>                replace count = r(N) in `i'
>>>>>                drop tag
>>>>> }
>>>>>
>>>>> list
>>>>>
>>>>>     +--------------------------------------------------------------+
>>>>>     | name       date1       date2      before       after   count |
>>>>>     |--------------------------------------------------------------|
>>>>>  1. |    A   19may1963   08jun1963   28apr1963   09jun1963       0 |
>>>>>  2. |    A   27may1963   14jun1963   06may1963   17jun1963       1 |
>>>>>  3. |    A   03jun1963   22dec1963   13may1963   24jun1963       2 |
>>>>>  4. |    B   07may1963   14sep1963   16apr1963   28may1963       0 |
>>>>>  5. |    B   30may1963   19jun1963   09may1963   20jun1963       0 |
>>>>>     |--------------------------------------------------------------|
>>>>>  6. |    C   23jul1963   16sep1963   02jul1963   13aug1963       0 |
>>>>>     +--------------------------------------------------------------+
>>>>>
>>>>>
>>>>> ACTUAL OUTPUT
>>>>>     +--------------------------------------------------------------+
>>>>>     | name       date1       date2      before       after   count |
>>>>>     |--------------------------------------------------------------|
>>>>> 4226. |     Util   09aug2000   16nov2000   19jul2000   30aug2000       1 |
>>>>> 4227. |     Util   13dec2000   23apr2001   22nov2000   03jan2001       0 |
>>>>> 4228. |     Util   30oct2003   13jan2004   09oct2003   20nov2003       0 |
>>>>> 4229. |     Util   09mar2004   22jun2004   17feb2004   30mar2004       0 |
>>>>> 4230. |     Util   13oct2004   10nov2004   22sep2004   03nov2004       0 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4231. |     Util   29mar2005   25jul2005   08mar2005   19apr2005       0 |
>>>>> 4232. |     Util   06sep2006   25may2007   16aug2006   27sep2006       0 |
>>>>> 4233. |     Util   29mar2007   14nov2007   08mar2007   19apr2007       0 |
>>>>> 4234. |     Util   31aug2007   15nov2007   10aug2007   21sep2007       0 |
>>>>> 4235. |     Util   19jun2007   11mar2008   29may2007   10jul2007       0 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4236. |    Whlsl   18mar1983   12apr1983   25feb1983   08apr1983       0 |
>>>>> 4237. |    Whlsl   30mar1983   26apr1983   09mar1983   20apr1983       1 |
>>>>> 4238. |    Whlsl   12apr1983   13may1983   22mar1983   03may1983       2 |
>>>>> 4239. |    Whlsl   13apr1983   23may1983   23mar1983   04may1983       2 |
>>>>> 4240. |    Whlsl   26apr1983   14jun1983   05apr1983   17may1983       3 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4241. |    Whlsl   26may1983   12jul1983   05may1983   16jun1983       2 |
>>>>> 4242. |    Whlsl   29apr1983   21jul1983   08apr1983   20may1983       3 |
>>>>> 4243. |    Whlsl   17jun1983   03aug1983   27may1983   08jul1983       1 |
>>>>> 4244. |    Whlsl   20jul1983   24aug1983   29jun1983   10aug1983       3 |
>>>>> 4245. |    Whlsl   06jul1983   15sep1983   15jun1983   27jul1983       2 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4246. |    Whlsl   29jul1983   27sep1983   08jul1983   19aug1983       3 |
>>>>> 4247. |    Whlsl   19aug1983   05oct1983   29jul1983   09sep1983       2 |
>>>>> 4248. |    Whlsl   14oct1983   27jan1984   23sep1983   04nov1983       2 |
>>>>> 4249. |    Whlsl   26jan1984   14mar1984   05jan1984   16feb1984       1 |
>>>>> 4250. |    Whlsl   17jan1984   22mar1984   27dec1983   07feb1984       1 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4251. |    Whlsl   12jul1984   02aug1984   21jun1984   02aug1984       0 |
>>>>> 4252. |    Whlsl   22jan1985   22feb1985   01jan1985   12feb1985       0 |
>>>>> 4253. |    Whlsl   19mar1985   23apr1985   26feb1985   09apr1985       0 |
>>>>> 4254. |    Whlsl   07may1985   13jun1985   16apr1985   28may1985       1 |
>>>>> 4255. |    Whlsl   23may1985   21jun1985   02may1985   13jun1985       1 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4256. |    Whlsl   11jul1985   31jul1985   20jun1985   01aug1985       1 |
>>>>> 4257. |    Whlsl   09aug1985   01oct1985   19jul1985   30aug1985       1 |
>>>>> 4258. |    Whlsl   02aug1985   09oct1985   12jul1985   23aug1985       1 |
>>>>> 4259. |    Whlsl   26aug1985   07nov1985   05aug1985   16sep1985       0 |
>>>>> 4260. |    Whlsl   06nov1985   05dec1985   16oct1985   27nov1985       1 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4261. |    Whlsl   29oct1985   05dec1985   08oct1985   19nov1985       2 |
>>>>> 4262. |    Whlsl   09dec1985   13dec1985   18nov1985   30dec1985       2 |
>>>>> 4263. |    Whlsl   12dec1985   17dec1985   21nov1985   02jan1986       2 |
>>>>> 4264. |    Whlsl   19dec1985   05mar1986   28nov1985   09jan1986       2 |
>>>>> 4265. |    Whlsl   31jan1986   05mar1986   10jan1986   21feb1986       0 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4266. |    Whlsl   19feb1986   27mar1986   29jan1986   12mar1986       2 |
>>>>> 4267. |    Whlsl   16apr1986   23apr1986   26mar1986   07may1986       1 |
>>>>> 4268. |    Whlsl   26mar1986   06may1986   05mar1986   16apr1986       2 |
>>>>> 4269. |    Whlsl   02may1986   11jun1986   11apr1986   23may1986       2 |
>>>>> 4270. |    Whlsl   08may1986   18jun1986   17apr1986   29may1986       2 |
>>>>>      |------------------------------------------------------------------|
>>>>> 4271. |    Whlsl   23jun1986   01jul1986   02jun1986   14jul1986       1 |
>>>>> 4272. |    Whlsl   11jul1986   17jul1986   20jun1986   01aug1986       1 |
>>>>> 4273. |    Whlsl   03jul1986   21aug1986   12jun1986   24jul1986       2 |
>>>>> 4274. |    Whlsl   01aug1986   17sep1986   11jul1986   22aug1986       2 |
>>>>> 4275. |    Whlsl   22aug1986   03oct1986   01aug1986   12sep1986       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/
>

*
*   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   |   Site index