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]

st: Counting observations between dates by group


From   Joseph Monte <hmjc66@gmail.com>
To   statalist <statalist@hsphsun2.harvard.edu>
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/


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