Re: st: Equivalent of Excel's COUNTIF

[Stas Kolenikov <skolenik@gmail.com>]

This is grossly inefficient, as any code with explicit cycling over
observations is, but it will probably do the work for you:

clear
set obs 10
set seed 10101
generate int arrival = ceil(runiform()*50)
generate int discharge = arrival + ceil(runiform()*15)
gen long npatients = .
gen long _num_discharged = .
sort arrival
isid arrival
forvalues k=1/`=_N' {
  replace _num_discharged = sum( discharge <= arrival[`k'] )
  replace npatients = (_n-1) - _num_discharged[`k'] in `k'
}
list

It is not important that the -arrival- and -discharge- variables are
generated as integers, but it is important that _num_discharged is as
accurate as possible. The (_n-1) here is the number of patients who
have arrived by the time arrival[_n], and we know this since we have
sorted by -arrival- and checked that all values are unique.

There may be alternative algorithms that would not require the
observation-by-observation comparisons, but you would need to create
some sort of stack of the patients... and that's not really
straightforward, at least within Stata (but should be doable in Mata).

On Tue, Nov 15, 2011 at 3:47 PM, Teubner, David (Health)
<david.teubner@health.sa.gov.au> wrote:
> Apologies for the slightly long-winded first post.
>
> I have a data stet of emergency department presentations for the last 18 years.  The dataset includes numerous variables about individual patient presentations to the department, including time of arrival and time of leaving.  What it does not have is a variable for the number of patients already in the department when each new patient arrives.
>
> It seems to me that I could construct this by simply subtracting the number of patients who have left (prior to the time of arrival of a new patient) from the number who have arrived.  I understand how to calculate the number who have arrived (sort by arrival time and use the _n function) but I don't understand how to do the left number.  In Excel I would use COUNTIF(-all arrival times-,<individual patients discharge time).  Is there a way to do this in Stata?

--
Stas Kolenikov, also found at http://stas.kolenikov.name
Small print: I use this email account for mailing lists only.

*
*   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/