Hello!
I was wondering if anyone here could help me with transforming my dataset?
I've attached an example of what my dataset currently looks like below. I would like to get my dataset to a point where each observation has the following variables: 'LSOA area', 'month', 'total value of that area that month', 'total amount of property_dummy in that area that month' & 'total amount of violent_dummy in that area that month'.
I've attached a screenshot down below of what I feel it should look like.
I believe this means that I need to collapse my data, but I'm struggeling to find out how.
Since my dataset contains 306 LSOA areas, this transformation would reduce the amount of observations to 3672. (306*12 months)
I'm struggeling to find the correct loop command to automate this. If anyone could help me, it would be greatly appreciated!
Thank you in advance and kind regards,
Glen
I was wondering if anyone here could help me with transforming my dataset?
I've attached an example of what my dataset currently looks like below. I would like to get my dataset to a point where each observation has the following variables: 'LSOA area', 'month', 'total value of that area that month', 'total amount of property_dummy in that area that month' & 'total amount of violent_dummy in that area that month'.
I've attached a screenshot down below of what I feel it should look like.
I believe this means that I need to collapse my data, but I'm struggeling to find out how.
Since my dataset contains 306 LSOA areas, this transformation would reduce the amount of observations to 3672. (306*12 months)
I'm struggeling to find the correct loop command to automate this. If anyone could help me, it would be greatly appreciated!
Thank you in advance and kind regards,
Glen
Code:
input float ID str9 lsoacode str33 lsoaname float month long value float(property_dummy violent_dummy property violent) long(crime_num averageincome) str8 postcode_ws str7 postcode_zs float IDPS byte(merge1 merge2) 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 1 0 14 14 54000 "BA1 6FD" "BA16FD" 1095 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 1 0 2 0 2 54000 "BA1 6SF" "BA16SF" 1225 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 0 0 0 1 54000 "BA1 6NU" "BA16NU" 1167 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 1 0 14 14 54000 "BA1 6PA" "BA16PA" 1172 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 1 0 7 0 7 54000 "BA1 6SE" "BA16SE" 1224 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 1 0 14 14 54000 "BA1 6TG" "BA16TG" 1244 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 0 0 0 1 54000 "BA1 6FD" "BA16FD" 1095 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 0 0 0 1 54000 "BA1 6TF" "BA16TF" 1243 3 3 1 "E01014409" "Bath and North East Somerset 004A" 1 1000 0 0 0 0 1 54000 "BA1 6QX" "BA16QX" 1204 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 0 0 0 1 54000 "BA1 6NR" "BA16NR" 1164 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 0 0 0 1 54000 "BA1 6SR" "BA16SR" 1232 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 1 0 14 14 54000 "BA1 6SF" "BA16SF" 1225 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 1 0 3 0 3 54000 "BA1 7AY" "BA17AY" 1268 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 0 0 0 1 54000 "BA1 6NX" "BA16NX" 1169 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 0 0 0 9 54000 "BA1 6NU" "BA16NU" 1167 3 3 1 "E01014409" "Bath and North East Somerset 004A" 2 1000 0 0 0 0 4 54000 "BA1 6NS" "BA16NS" 1165 3 3 1 "E01014409" "Bath and North East Somerset 004A" 3 1000 0 0 0 0 4 54000 "BA1 6SH" "BA16SH" 1227 3 3 1 "E01014409" "Bath and North East Somerset 004A" 3 1000 1 0 7 0 7 54000 "BA1 6PA" "BA16PA" 1172 3 3 1 "E01014409" "Bath and North East Somerset 004A" 3 1000 0 0 0 0 9 54000 "BA1 6SE" "BA16SE" 1224 3 3 1 "E01014409" "Bath and North East Somerset 004A" 3 1000 1 0 7 0 7 54000 "BA1 6NY" "BA16NY" 1170 3 3 1 "E01014409" "Bath and North East Somerset 004A" 3 1000 0 1 0 14 14 54000 "BA1 6SR" "BA16SR" 1232 3 3 1 "E01014409" "Bath and North East Somerset 004A" 4 1000 0 0 0 0 9 54000 "BA1 6TB" "BA16TB" 1240 3 3 1 "E01014409" "Bath and North East Somerset 004A" 4 1000 0 0 0 0 4 54000 "BA1 6SF" "BA16SF" 1225 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 0 0 0 0 4 54000 "BA1 6SG" "BA16SG" 1226 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 0 1 0 14 14 54000 "BA1 7BB" "BA17BB" 1271 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 1 0 7 0 7 54000 "BA1 6SG" "BA16SG" 1226 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 0 0 0 0 1 54000 "BA1 6NR" "BA16NR" 1164 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 0 1 0 14 14 54000 "BA1 6NX" "BA16NX" 1169 3 3 1 "E01014409" "Bath and North East Somerset 004A" 5 1000 0 0 0 0 1 54000 "BA1 6FD" "BA16FD" 1095 3 3 1 "E01014409" "Bath and North East Somerset 004A" 6 1000 0 0 0 0 4 54000 "BA1 6NR" "BA16NR" 1164 3 3 1 "E01014409" "Bath and North East Somerset 004A" 7 1000 0 1 0 14 14 54000 "BA1 6SE" "BA16SE" 1224 3 3 1 "E01014409" "Bath and North East Somerset 004A" 7 1000 0 0 0 0 4 54000 "BA1 6FD" "BA16FD" 1095 3 3 1 "E01014409" "Bath and North East Somerset 004A" 7 1000 0 1 0 14 14 54000 "BA1 6NR" "BA16NR" 1164 3 3 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6SE" "BA16SE" 1224 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6TL" "BA16TL" 1246 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6NR" "BA16NR" 1164 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6TG" "BA16TG" 1244 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6NX" "BA16NX" 1169 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6SG" "BA16SG" 1226 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6SR" "BA16SR" 1232 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6TH" "BA16TH" 1245 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6SJ" "BA16SJ" 1228 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 500 . . . . . 54000 "BA1 6NU" "BA16NU" 1167 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6SR" "BA16SR" 1232 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6SE" "BA16SE" 1224 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6SG" "BA16SG" 1226 3 1 1 "E01014409" "Bath and North East Somerset 004A" 8 1000 . . . . . 54000 "BA1 6NT" "BA16NT" 1166 3 1 1 "E01014409" "Bath and North East Somerset 004A" 9 1000 0 0 0 0 1 54000 "BA1 6TH" "BA16TH" 1245 3 3 1 "E01014409" "Bath and North East Somerset 004A" 9 1000 0 0 0 0 1 54000 "BA1 6TG" "BA16TG" 1244 3 3 1 "E01014409" "Bath and North East Somerset 004A" 10 1000 0 0 0 0 4 54000 "BA1 6RD" "BA16RD" 1208 3 3 1 "E01014409" "Bath and North East Somerset 004A" 10 1000 1 0 2 0 2 54000 "BA1 6NR" "BA16NR" 1164 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1300 0 0 0 0 13 54000 "BA1 6SJ" "BA16SJ" 1228 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 0 0 0 4 54000 "BA1 6NS" "BA16NS" 1165 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 1 0 14 14 54000 "BA1 6SE" "BA16SE" 1224 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 1 0 14 14 54000 "BA1 6TH" "BA16TH" 1245 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 1 0 14 14 54000 "BA1 6NZ" "BA16NZ" 1171 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 1 0 14 14 54000 "BA1 7BB" "BA17BB" 1271 3 3 1 "E01014409" "Bath and North East Somerset 004A" 11 1000 0 1 0 14 14 54000 "BA1 6FD" "BA16FD" 1095 3 3 1 "E01014409" "Bath and North East Somerset 004A" 12 1000 0 0 0 0 1 54000 "BA1 6SE" "BA16SE" 1224 3 3 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SA" "" 1222 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6BZ" "" 1054 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6PB" "" 1173 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6TE" "" 1242 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SX" "" 1236 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SL" "" 1229 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6TP" "" 1247 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SZ" "" 1238 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6RA" "" 1207 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6TQ" "" 1248 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SW" "" 1235 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SQ" "" 1231 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6QY" "" 1205 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SP" "" 1230 1 1 1 "E01014409" "Bath and North East Somerset 004A" . . . . . . . 54000 "BA1 6SY" "" 1237 1 1 2 "E01014410" "Bath and North East Somerset 004B" 1 1000 0 0 0 0 13 54000 "BA1 6HA" "BA16HA" 1099 3 3 2 "E01014410" "Bath and North East Somerset 004B" 1 1000 0 1 0 14 14 54000 "BA1 6HW" "BA16HW" 1114 3 3 2 "E01014410" "Bath and North East Somerset 004B" 1 1000 0 1 0 14 14 54000 "BA1 6PD" "BA16PD" 1174 3 3 2 "E01014410" "Bath and North East Somerset 004B" 1 1000 0 1 0 14 14 54000 "BA1 6HR" "BA16HR" 1110 3 3 2 "E01014410" "Bath and North East Somerset 004B" 1 1000 0 1 0 14 14 54000 "BA1 6HD" "BA16HD" 1101 3 3 2 "E01014410" "Bath and North East Somerset 004B" 2 1000 1 0 3 0 3 54000 "BA1 6QW" "BA16QW" 1203 3 3 2 "E01014410" "Bath and North East Somerset 004B" 2 1000 0 0 0 0 1 54000 "BA1 6HG" "BA16HG" 1103 3 3 2 "E01014410" "Bath and North East Somerset 004B" 2 1000 1 0 11 0 11 54000 "BA1 6NN" "BA16NN" 1161 3 3 2 "E01014410" "Bath and North East Somerset 004B" 2 1000 0 1 0 14 14 54000 "BA1 6NE" "BA16NE" 1155 3 3 2 "E01014410" "Bath and North East Somerset 004B" 3 1000 1 0 11 0 11 54000 "BA1 6HD" "BA16HD" 1101 3 3 2 "E01014410" "Bath and North East Somerset 004B" 3 1000 0 0 0 0 1 54000 "BA1 6JP" "BA16JP" 1128 3 3 2 "E01014410" "Bath and North East Somerset 004B" 3 1000 0 0 0 0 1 54000 "BA1 6HD" "BA16HD" 1101 3 3 2 "E01014410" "Bath and North East Somerset 004B" 3 1000 0 0 0 0 1 54000 "BA1 6HR" "BA16HR" 1110 3 3 2 "E01014410" "Bath and North East Somerset 004B" 3 1000 1 0 11 0 11 54000 "BA1 6NN" "BA16NN" 1161 3 3 2 "E01014410" "Bath and North East Somerset 004B" 4 1000 1 0 11 0 11 54000 "BA1 6PD" "BA16PD" 1174 3 3 2 "E01014410" "Bath and North East Somerset 004B" 4 1000 1 0 11 0 11 54000 "BA1 6NW" "BA16NW" 1168 3 3 2 "E01014410" "Bath and North East Somerset 004B" 4 1000 1 0 11 0 11 54000 "BA1 6JN" "BA16JN" 1127 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 1000 0 0 0 0 9 54000 "BA1 6PE" "BA16PE" 1175 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 500 0 0 0 0 9 54000 "BA1 6HG" "BA16HG" 1103 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 1000 0 0 0 0 9 54000 "BA1 6NN" "BA16NN" 1161 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 1000 0 0 0 0 9 54000 "BA1 6HR" "BA16HR" 1110 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 1000 0 0 0 0 9 54000 "BA1 6RE" "BA16RE" 1209 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 1300 0 0 0 0 9 54000 "BA1 6QZ" "BA16QZ" 1206 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 500 0 0 0 0 9 54000 "BA1 6NP" "BA16NP" 1162 3 3 2 "E01014410" "Bath and North East Somerset 004B" 5 500 0 0 0 0 9 54000 "BA1 6JP" "BA16JP" 1128 3 3 2 "E01014410" "Bath and North East Somerset 004B" 6 1000 0 0 0 0 13 54000 "BA1 6HR" "BA16HR" 1110 3 3 end label values crime_num crime_num label def crime_num 1 "Anti-social behaviour", modify label def crime_num 2 "Bicycle theft", modify label def crime_num 3 "Burglary", modify label def crime_num 4 "Criminal damage and arson", modify label def crime_num 7 "Other theft", modify label def crime_num 9 "Public order", modify label def crime_num 11 "Shoplifting", modify label def crime_num 13 "Vehicle crime", modify label def crime_num 14 "Violence and sexual offences", modify label values merge1 _merge label values merge2 _merge label def _merge 1 "Master only (1)", modify label def _merge 3 "Matched (3)", modify
Comment