Identifying and assigning values

[Ashani Abayasekara]

Hi everyone,

I'm trying to match postcodes to MMM regions (an indicator of rurality of location, ranging from 1-7) in the attached dataset. The issue is that in some cases the same postcode is assigned different MMM codes (variable mmm2023). In such cases I want to assign the MMM which accounts for the largest area (given by the variable mmm2023area). For example, the postcode 2105 is assigned values of both 1 and 2 for its MMM. For this postcode, since the highest value of 0.7 in mmm2023area is when mmm2023=1, I want all observations with the postcode 2105 to be assigned 1 for mmm2023.

If anyone could explain the code to achieve this what would be great.

Many thanks,
Ashani

* Example generated by -dataex-. For more info, type help dataex
clear
input int postcode byte mmm2023 float mmm2023area
2100 1  2.9
2100 1  1.1
2100 1  6.7
2100 1  2.2
2101 1  2.5
2101 1  3.9
2101 1  2.2
2101 1 18.1
2102 1  4.1
2103 1  4.6
2104 1  3.8
2105 2   .3
2105 2   .2
2105 1   .5
2105 2   .5
2105 1   .7
2106 1  3.8
2107 1   .3
2107 1  5.2
2107 1   .5
2107 1   .5
2107 1  1.4
2108 1  2.7
2108 2   .1
2108 2   .1
2110 1  3.6
2110 1   .7
2111 1   .3
2111 1   .3
2111 1  3.5
2111 1   .4
2111 1   .2
2112 1  1.5
2112 1   .7
2112 1  7.1
2113 1  5.4
2113 1  1.1
2113 1  6.8
2114 1  1.5
2114 1  1.3
2114 1  3.6
2114 1   .7
2114 1   .3
2115 1  3.9
2116 1  3.8
2117 1  1.8
2117 1  1.5
2117 1  1.4
2117 1  2.5
2118 1  8.6
2119 1  5.1
2119 1  1.7
2120 1  3.8
2120 1  3.9
2120 1  6.1
2121 1  6.8
2121 1  2.3
2122 1  5.2
2122 1  3.9
2125 1  9.3
2126 1  8.3
2127 1  6.6
2127 1   .9
2127 1   .6
2128 1  2.7
2130 1  1.2
2131 1  3.4
2132 1  2.4
2133 1  2.6
2134 1  2.4
2135 1  6.6
2136 1   .7
2136 1  2.5
2136 1   .2
2137 1  5.1
2137 1   .5
2137 1   .5
2137 1   .2
2137 1    1
2138 1  2.5
2138 1    1
2138 1   .2
2140 1  1.7
2140 1    2
2141 1  2.1
2141 1    3
2141 1  6.8
2142 1  3.4
2142 1   .5
2142 1   .7
2142 1  3.7
2142 1   .6
2142 1  2.2
2143 1  1.2
2143 1  1.1
2143 1    2
2144 1  8.6
2145 1  1.2
2145 1  2.3
2145 1   .3
2145 1    9
2145 1    2
2145 1  1.8
2145 1  2.9
2145 1  3.1
2146 1  4.7
2146 1  1.3
2147 1  9.6
2147 1  2.7
2147 1  3.7
2148 1  2.6
2148 1    2
2148 1 16.1
2148 1  2.6
2148 1   15
2148 1  2.7
2148 1  1.7
2150 1  5.2
2150 1   .6
2151 1  5.3
2151 1  5.5
2152 1  4.3
2153 1  4.7
2153 1  2.8
2153 1  4.4
2153 1 13.7
2154 1 18.8
2155 1 12.8
2155 1  9.6
2155 1  2.7
2155 1  8.1
2155 1  3.2
2156 2 20.8
2156 1 24.7
2156 1 10.2
2156 1  7.2
2157 5  5.6
2157 5 63.4
2157 2 79.4
2158 1  4.4
2158 2  3.3
2158 1 34.1
2159 1 23.6
2159 2 22.4
2159 2 12.1
2159 2  2.8
2159 5 66.6
2160 1  1.7
2160 1  6.7
2161 1    1
2161 1  5.9
2161 1  2.7
2161 1  1.6
2162 1    4
2162 1  1.8
2163 1    1
2163 1  3.9
2163 1  1.4
2164 1 11.2
2164 1   .4
2164 1  8.9
2165 1  4.4
2165 1  1.5
2165 1  3.2
2165 1    2
2166 1    5
2166 1  2.6
2166 1  2.9
2166 1  1.8
2166 1  2.9
2167 1    7
2168 1  3.2
2168 1  1.2
2168 1    1
2168 1  1.3
2168 1  1.2
2168 1  3.2
2168 1   .9
2168 1   .9
2170 1  7.1
2170 1  2.5
2170 1  7.1
2170 1 12.5
2170 1  1.8
2170 1  9.2
2170 1  6.4
2170 1  4.6
2170 1  3.1
2171 1    1
2171 1  1.5
2171 1  1.1
2171 1  1.3
2171 1  2.6
2171 1  6.2
2171 1  1.1
2171 1  6.7
2172 1  1.6
2172 1  1.7
2172 1   .4
2173 1 60.5
end

[Hemanshu Kumar]

You just need

bysort postcode (mmm2023area): gen byte wanted = mmm2023[_N]

To inspect what this achieved:

. egen byte is_different = max(wanted!=mmm2023), by(postcode)
. list postcode mmm2023 mmm2023area wanted if is_different, sepby(postcode) noobs

  +----------------------------------------+
  | postcode   mmm2023   mmm202~a   wanted |
  |----------------------------------------|
  |     2105         2         .2        1 |
  |     2105         2         .3        1 |
  |     2105         2         .5        1 |
  |     2105         1         .5        1 |
  |     2105         1         .7        1 |
  |----------------------------------------|
  |     2108         2         .1        1 |
  |     2108         2         .1        1 |
  |     2108         1        2.7        1 |
  |----------------------------------------|
  |     2156         1        7.2        1 |
  |     2156         1       10.2        1 |
  |     2156         2       20.8        1 |
  |     2156         1       24.7        1 |
  |----------------------------------------|
  |     2157         5        5.6        2 |
  |     2157         5       63.4        2 |
  |     2157         2       79.4        2 |
  |----------------------------------------|
  |     2158         2        3.3        1 |
  |     2158         1        4.4        1 |
  |     2158         1       34.1        1 |
  |----------------------------------------|
  |     2159         2        2.8        5 |
  |     2159         2       12.1        5 |
  |     2159         2       22.4        5 |
  |     2159         1       23.6        5 |
  |     2159         5       66.6        5 |
  +----------------------------------------+

[Ashani Abayasekara]

Thanks so much!