LSOA deprivation scores into MSOA average deprivation score – foreach?

[Luka Crnjakovic]

I'm looking into satisfaction with GP practices in England and I intend to use deprivation scores for the areas the practices are located in as one of the control variables. I’ve taken the data from the English Indices of Deprivation which are given for Lower layer Super Output Area (LSOA). These contain between 1,000 and 3,000 people, but as the mean number of patients per practice in the dataset is 7,068 patients I thought a more appropriate level for the control variable would be the Middle layer Super Output Area (MSOA) as each of those contain between 5,000 and 15,000 people.

I have the practice code, LSOA code, MSOA code, and deprivation score for LSOAs (please see a sample of the list below). I would now need to calculate the average deprivation score for each MSOA, from several LSOAs each MSOA contains, and then assign this score to each practice in that particular MSOA. And so on for 7,500 observations.

+------------------------------------------------------+
| p_code lsoa msoa dep_mu~l dep_mu~m |
|------------------------------------------------------|
1. | E83012 E01000292 E02000024 10.2398 . |
2. | E83017 E01000248 E02000024 10.16927 . |
3. | E83036 E01000293 E02000024 6.973502 . |
4. | E83042 E01000293 E02000024 6.973502 . |
5. | E83018 E01000129 E02000047 34.26831 . |
|------------------------------------------------------|
6. | E83028 E01000127 E02000047 23.70964 . |
7. | E83032 E01000125 E02000047 36.41887 . |
8. | E83658 E01000129 E02000047 34.26831 . |
+------------------------------------------------------+

From what I’ve read so far, I believe would need to use foreach for this. I’m having trouble writing the command to execute what I described above. Would anybody be able to help with a suggestion please?

[William Lisowski]

This may be what you need. If so, no looping needed.

clear
input str6 p_code str9 (lsoa msoa) float dep_l
E83012 E01000292 E02000024 10.2398 
E83017 E01000248 E02000024 10.16927
E83036 E01000293 E02000024 6.973502
E83042 E01000293 E02000024 6.973502
E83018 E01000129 E02000047 34.26831
E83028 E01000127 E02000047 23.70964
E83032 E01000125 E02000047 36.41887
E83658 E01000129 E02000047 34.26831
end
// tag one observation per lsoa
egen lsoa1 = tag(lsoa)
// take the mean of distinct lsoa
// - we will ignore the means computed using any remaining observations
bysort msoa lsoa1: egen dep_m = mean(dep_l)
// OK, now copy the means of distinct observations onto all the observations
bysort msoa (lsoa1): replace dep_m = dep_m[_N]
sort msoa p_code
list, sepby(msoa)

     +--------------------------------------------------------------+
     | p_code        lsoa        msoa      dep_l   lsoa1      dep_m |
     |--------------------------------------------------------------|
  1. | E83012   E01000292   E02000024    10.2398       1   9.127524 |
  2. | E83017   E01000248   E02000024   10.16927       1   9.127524 |
  3. | E83036   E01000293   E02000024   6.973502       1   9.127524 |
  4. | E83042   E01000293   E02000024   6.973502       0   9.127524 |
     |--------------------------------------------------------------|
  5. | E83018   E01000129   E02000047   34.26831       1   31.46561 |
  6. | E83028   E01000127   E02000047   23.70964       1   31.46561 |
  7. | E83032   E01000125   E02000047   36.41887       1   31.46561 |
  8. | E83658   E01000129   E02000047   34.26831       0   31.46561 |
     +--------------------------------------------------------------+

[Luka Crnjakovic]

Thanks for that suggestion, it looks exactly like what I need and is very helpful. Thanks for noticing the need to calculate the mean of distinct observations as well, which I failed to note above.

I guess I was overthinking things with the idea I needed a loop.