Bysort: egen help

[Courtney Caudill]

I am needing to create line graphs showing trends in shootings across years. Below is a table of what I assume the data should look like:

RECODE of	Type
year	FATAL NONFATAL	Total
2015	57 437	494
2016	53 392	445
2017	61 360	421
2018	50 284	334
2019	60 310	370
2020	87 436	523
2021	84 363	447
2022	64 350	414
2023	68 363	431
2024	61 308	369
Total	645 3,603	4,248

(my apologies, the table will not separate fatal and nonfatal In this window; however, fatal is the first 2 numbers and nonfatal the last 3)

I should create two variables sorted by year (1) for fatal shootings and (2) for nonfatal shootings. My data should be representative of the above table by the respective shooting types. In order to do this, I used the following code (this is for fatal shootings):

bysort year_r: egen totalftl= total(shottype) if shottype==1

Unfortunately, the results I get do not match the above tabulated values. The results are as follows:

totalftl
	Freq	percent	valid
9	9	0.21	1.38	1.38
50	50	1.17	7.65	9.02
53	53	1.24	8.10	17.13
57	57	1.33	8.72	25.84
60	60	1.40	9.17	35.02
61	122	2.85	18.65	53.67
64	64	1.50	9.79	63.46
68	68	1.59	10.40	73.85
84	84	1.96	12.84	86.70
87	87	2.03	13.30	100.00
Total	654	15.29	100.00
Missing .	3624	84.71
Total	4278	100.00

Does anyone have any suggestions as to how I can fix this code?

Best,
Courtney

[Courtney Caudill]

Upon further investigation, it appears the output has grouped 2024 and 2017 together. The variable year_r is a recode of year to exclude 2025. The output appears to be including 2025.

[Clyde Schechter]

I doubt anyone will be able to help you with this unless you post back showing example data. It's good to see what you're trying to get and the output of the commands you tried, but without seeing the data from which it came, it's impossible to say what's going wrong. There may be nothing wrong with your command: it might be a problem with the data. Or not. Also without seeing the data, nobody can know what the command is actually doing.

So please post back with a data example that reproduces the problem you are having. Be sure to use the -dataex- command to show the example data. If you are running version 16 or later, or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Added: Crossed with #2. In light of that, it is important that you also show the exact command that you used to -recode- the year, as the problem may well lie there. That's in addition to showing example data.

[Nick Cox]

Your code implies a different data layout from your table, and I can't work out from the second part of your post what the original data were like.

Perhaps the technique you're missing is more like

bysort year shottype : gen deaths = _N

If you need more advice on how to change your code, I think you'll need to give us a data example featuring year and shottype and whatever else is relevant.

But this may get you closer to a desired graph:

* Example generated by -dataex-. For more info, type help dataex
clear
input int(year total) byte fatal int nonfatal
2015 494 57 437
2016 445 53 392
2017 421 61 360
2018 334 50 284
2019 370 60 310
2020 523 87 436
2021 447 84 363
2022 414 64 350
2023 431 68 363
2024 369 61 308
end

line nonfatal fatal year, xla(2015/2024) xsc(r(. 2025)) text(308 2024 " non-fatal", place(e) color(stc1)) text(61 2024 " fatal", color(stc2) place(e)) legend(off) ytitle(Deaths) xtitle("")

I used added text options not a legend and suppressed the unnecessary text "year".

[Nick Cox]

Clyde Schechter's advuce and mine are I think totally consistent. However, I can't make sense of #2 either. It's a bit alarming that a simple variable like year needed to be recoded. So, you may need to take us further back and show us (1) year-relevant data before recode and (2) the recode statement.

[Nick Cox]

The y axis title in #4 would naturally be better as Shootings, as non-fatal shootings are not deaths!