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]
st: Counting observations between dates by group
From
Joseph Monte <[email protected]>
To
statalist <[email protected]>
Subject
st: Counting observations between dates by group
Date
Wed, 7 Sep 2011 11:05:32 +0100
Dear Statalisters,
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 |
Thanks,
Joe
*
* 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/