Aggregate data at county level

[Giulia Fantini]

Hi,
I need to estimate fraud at county level.

Fraud variables:
- Fraud_this_year
- Past_fraud
- Next_year_fraud

County level variable PrimaryOfficialCountyCode

What I need to do is to estimate Fraud at county level.
If we look to Arapahoe (PrimaryOfficialCountyName, column 4) we have multiple observation at city and county level for each year for each of the three fraud variables.

Therefore, what I am looking for is to determine how many Fraud_this_year, Past_fraud and Next_year_fraud I have each year at county level (PrimaryOfficialCountyCode)

Thus I would expect something like that:

PrimaryOfficialCountyCode	year	Fraud this year	Past fraud	Next year fraud
8005	2011	1	1	1
8005	2012	1	1	1
8005	2013	1	1	1
8005	2014	1	1	0
8005	2015	0	1	0

If I run this it does not give me the result that I want.
collapse (count) num_this_year_fraud=Fraud_this_year num_past_fraud=Past_fraud num_next_year_fraud=Next_year_fraud, by (year PrimaryOfficialCountyCode PrimaryOfficialCountyName)

Can I kindly ask your help?

Thanks a lot.
Giulia

[Clyde Schechter]

I'm not sure I understand what you want, but if I do have it right, you need -(sum)-, not -(count)-, in your -collapse- command.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 17, 16 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.

[Giulia Fantini]

Thanks a lot!

I'll take on board your advise for future posts.

Thanks again.
Best wishes,
Giulia