Hi,

I've got a data handling challenge that I was hoping to get some assistance with. The data I'm working with contains all the crime incidents that occurred throughout the country in a given year with details such as the location (state), date, and type (property or violent crime).

E.g.,

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 STATE long DATE byte TYPE
"AL" 20150101 1
"AL" 20150102 1
"AL" 20150430 0
"AL" 20150115 1
"KY" 20151226 1
"KY" 20150402 0
"KY" 20150117 1
"OH" 20151019 1
"OH" 20150912 0
"OH" 20150216 0
"OH" 20150216 1
"OH" 20150830 1
"TX" 20150227 1
"TX" 20150303 0
"TX" 20150913 0
end

I'm hoping to end up with a small data set that contains weekly crime rates by state. Something like this:

HTML Code:
STATE, WEEK, PROPCRIMERATE, VIOLCRIMERATE, TOTALCRIMERATE
AL, 1, 0, 1.33, 1.33
AL, 2, X, X, X
...
AL, 52, X, X, X
KY, 1, X, X, X
KY, 2, X, X, X
...
KY, 52, X, X, X
...
So, each state would have 52 rows with an indicator for each week and the property crime, violent crime, and total crime rate for each week.

Can anyone help out with a way to do this?

Thanks.

Owen Gallupe

Note:
TYPE (in first example data) is an indicator of the type of crime committed where 0=property crime and 1=violent crime.

Example of a weekly crime rate calculation - AL first week of 2015:
For ease of small numbers, let's assume that the population of AL=150.

AL had 2 separate violent crimes in the first week of the year (occurring on 2015-01-01 and 2015-01-02 - first two rows of the first data set above). Therefore, the violent crime rate for that state for that week (expressed as the number per 100 population) would be (2/150)*100 = 1.33.