How to create variables that calculates prison suicide/attempts/self-harm rates

[Maha Aon]

Hello,

I have a data set about suicide, suicide attempts and self-harm incidents for each prison in a country in 2023. I would like to calculate the suicide, suicide attempt and self-harm rates per prison (i.e., 3 rates for each prison). There are 76 prisons and the relevant variables are prison, incidenttype (suicide, suicide attempt or self-harm) and population (the numerical population on the day of the incident). The new variables (x_rate) should = number of x incidents / average population. This should be generated for each prison. So the 1st variable would be suicide_rate = number of suicides for each prison / average population for each prison. The 2nd variable would be Sattempt_rate and the 3rd SH_rate.

The first challenge is that the population changes by day so the population variable is not static per prison. I wish to use the average population per prison as the denominator. I calculated that as follows:

. mean population, over(prison)

I thought I would first generate a variable that can act as the denominator: gen Avpop = mean population, over(prison)but I got the message 'mean not found'.

The second challenge is that the data set includes all three types of incidents (suicides, attempts and self-harm in the variable incidenttype). So I am not sure how to generate the numerator for each of the new variables.

Thank you so much!
Maha

[Carlo Lazzaro]

Maha:
welcome to this forum.
Data excerpts/examples are hugely appreciated on this forum (please, see how to share them reading and acting on the FAQ). Thanks.
As far as the mean calculation is concerned, you may want to try something along the following lines:

use "C:\Program Files\Stata18\ado\base\a\auto.dta"
(1978 automobile data)
bysort foreign: egen wanted=mean(price)

[Maha Aon]

Than you so much, Carlo. Very helpful!

I did search the FAQ to see how I can can paste data excerpts here, but failed to see anything. Basically, the new variable that you helped me create is a list of numbers (prison population) which looks like this:

AvPop
1. 1776
2. 1310.918
3. 1681.983
4. 10654.75
5. 1776
etc.

And the prison variable looks like this

prison
1. 34
2. 56
3. 46
4. 73
5. 21

So this variable can now act as the denominator for the new variables representing incident rates (number of x incidents / average population). The second challenge is to create the numerator, but the data set includes three types of incidents (suicides, suicide attempts and self-harm in the variable incidenttype). The incidenttype variable looks like this where SH= self-harm, SA= suicide attempt:


incidenttype
1. SH
2. SH
3. SA
4. Suicide
5. SA

For a total of 1036 observations. So now I need to create the variables representing incident rates for each prison (3 variables, one for each incident type). The new variables would only have 76 observations (1 per prison). How do I do that? Thank you so much in advance!

Maha

[Carlo Lazzaro]

Maha:
you may want to consider something along the following lines:

. set obs 5
Number of observations (_N) was 0, now 5.

. gen year=2024

. gen prison=_n

. gen pop=1000

. gen suicide=1 in 1/3

. replace suicide=2 in 4

. replace suicide=3 in 5

. label define suicide 1 "Suicide" 2 "Attempted suicide" 3 "Self harm"

. label val suicide suicide

. expand 2

. bysort year prison: gen wanted=suicide/pop

. list

     +---------------------------------------------------+
     | year   prison    pop             suicide   wanted |
     |---------------------------------------------------|
  1. | 2024        1   1000             Suicide     .001 |
  2. | 2024        1   1000             Suicide     .001 |
  3. | 2024        2   1000             Suicide     .001 |
  4. | 2024        2   1000             Suicide     .001 |
  5. | 2024        3   1000             Suicide     .001 |
     |---------------------------------------------------|
  6. | 2024        3   1000             Suicide     .001 |
  7. | 2024        4   1000   Attempted suicide     .002 |
  8. | 2024        4   1000   Attempted suicide     .002 |
  9. | 2024        5   1000           Self harm     .003 |
 10. | 2024        5   1000           Self harm     .003 |
     +---------------------------------------------------+

.

Please see Help - Statalist 12.2 and 12.3 about CODE delimiters and -dataex-. Thanks.

[Maha Aon]

Thank you very much, Carlo for your quick and helpful response.