Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Counting observations between dates by group
From
Joseph Monte <[email protected]>
To
[email protected]
Subject
Re: st: Counting observations between dates by group
Date
Mon, 12 Sep 2011 07:45:47 +0100
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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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/