replace missing variables based on existing variable

[Daniel Sutcliffe]

Hello.

I have some data that, among other variables, contains organisational variables as follows:

ICB Region LTLA
QHM Y63 E06000004
QHM Y63 E06000004
E06000001
QHM Y63 E06000002
QHM Y63 E06000003
QHM Y63 E06000004
QHM Y63 E06000001
E06000003
QHM Y63 E06000002
QHM Y63 E06000001
QHM Y63 E06000002
QHM Y63 E06000003
QHM Y63 E06000004

You can see variable LTLA has missing region and ICB for some observations. However, we know what the region and ICB values should be, because for some observations with these LTLA values the corresponding region and ICB is not missing: for LTLA code E06000001 the corresponding region should be Y63 and region should be QHM: it is only missing for some observations. Where region is missing ICB will always be missing and vice versa. There is never a case where a LTLA code can have more than one region or ICB code, so for LTLA code E06000001 the region and ICB code will always be Y63 and QHM. There are no occasions where LTLA is missing, and there is always at least one occasion where the data is complete i.e. LTLA region and ICB for a given LTLA.

I want to fill in the blanks - the easiest way I know would be create a lookup, there are 7000 LTLA codes and 20 - 30 region and ICB codes and do a merge. However. I was wondering if it would be possible to do this in code - as it would make ii more straightforward when the .do file I am creating is ran again as I wouldn't need to check if the lookup table had been updated - I can fill in the blanks directly from the data.

can anyone advise if there is a relatively simple way to replace the blanks using code?

[Clyde Schechter]

by LTLA (ICB REGION), sort: replace ICB = ICB[_N]
by LTLA ICB (REGION), sort: replace REGION = REGION[_N]

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

[Daniel Sutcliffe]

Apologies. I get the following error when I write the code:

by LTLA21CD ( ICB22CD Region22CD ), sort: replace ICB22CD = ICB22CD [_N]
weights not allowed
r(101);

( I simplified the variable names for the post)

Here is the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gp_code double age str3(ICB22CD Region22CD) str9 LTLA21CD double(dup sex weight pop)
"Y04273"   85 "QMF" "Y56" "E09000025" 0 0  -80.48707580566406   2
"K81640"   85 ""    ""    "E06000038" 0 0  -74.44090270996094   3
"C82111"   85 "QK1" "Y60" "E07000130" 0 1  -66.11626434326172   1
"Y00265"   85 "QNQ" "Y59" "E06000039" 0 0 -60.351715087890625  14
"J83063"   85 ""    ""    "E06000030" 0 0  -47.49614715576172  12
"F85707"   85 ""    ""    "E09000010" 0 0  -47.37305450439453   4
"E87677"   85 "QRV" "Y56" "E09000033" 0 0  -43.25244903564453  21
"C82111" 8084 "QK1" "Y60" "E07000130" 0 0  -43.09446716308594   1
"L81617"   85 "QOX" "Y58" "E06000022" 0 0  -41.17196273803711   4
"M86621"   85 ""    ""    "E08000026" 0 0  -40.41737365722656   2
"H85664"   85 "QWE" "Y56" "E09000032" 0 0  -39.06047821044922   2
"E84669"   85 ""    ""    "E09000005" 0 0  -36.09199523925781  15
"C82111" 6569 "QK1" "Y60" "E07000130" 0 0 -30.224124908447266   3
"E81065"   85 "QHG" "Y61" "E06000032" 0 0 -27.425527572631836  19
"M85167"   85 "QHL" "Y60" "E08000025" 0 0  -26.50629234313965  22
"D82088"   85 "QMM" "Y61" "E07000148" 0 0 -25.339052200317383   3
"F84660"   85 "QMF" "Y56" "E09000025" 0 0  -23.59719467163086   6
"J82080"   85 "QRL" "Y59" "E06000045" 0 0 -23.400047302246094   8
"A86027"   85 "QHM" "Y63" "E08000021" 0 1  -22.28987693786621   2
"C84633"   85 ""    ""    "E06000018" 0 1 -20.702932357788086   5
"A86027" 8084 "QHM" "Y63" "E08000021" 0 0 -19.691404342651367   5
"G82140"   85 "QKS" "Y59" "E07000106" 0 0 -19.650732040405273  10
"E85687"   85 "QRV" "Y56" "E09000009" 0 0 -17.488271713256836   2
"Y02463"   85 "QHG" "Y61" "E06000032" 0 1  -17.34430694580078   2
"Y02812"   85 ""    ""    "E09000017" 0 0 -15.942633628845215  10
"Y02421"   85 ""    ""    "E06000020" 0 0 -15.901025772094727   1
"E86612"   85 "QRV" "Y56" "E09000017" 0 0 -15.674466133117676   7
"J83063"   85 ""    ""    "E06000030" 0 1 -15.506391525268555   7
"L81617" 6569 "QOX" "Y58" "E06000022" 0 0 -13.608677864074707   8
"B83069"   85 ""    ""    "E08000032" 0 0 -13.128689765930176   8
"C82111" 6064 "QK1" "Y60" "E07000130" 0 0 -12.991333961486816   6
"J82080" 6064 "QRL" "Y59" "E06000045" 0 1 -12.601534843444824  36
"E81615"   85 "QHG" "Y61" "E06000055" 0 0  -12.27522087097168   6
"Y02509"   85 ""    ""    "E08000036" 0 1 -11.797416687011719   1
"Y02585"   85 ""    ""    "E09000031" 0 0 -11.749293327331543   0
"C82111" 8084 "QK1" "Y60" "E07000130" 0 1 -11.712852478027344   1
"F84713"   85 ""    ""    "E09000025" 0 1 -11.592805862426758   2
"C82111" 6064 "QK1" "Y60" "E07000130" 0 1 -11.588991165161133  17
"A86027"   85 "QHM" "Y63" "E08000021" 0 0  -11.02145004272461   3
"M83670" 6064 "QNC" "Y60" "E07000195" 0 1 -10.690250396728516  31
"M83670" 5559 "QNC" "Y60" "E07000195" 0 1 -10.479612350463867  32
"E82058" 6064 "QM7" "Y61" "E07000241" 0 0 -10.427844047546387 246
"E82058" 6064 "QM7" "Y61" "E07000241" 0 1 -10.259727478027344 242
"F81704"   85 "QH8" "Y61" "E07000075" 0 0  -9.752131462097168  23
"P84684"   85 "QOP" "Y62" "E08000003" 0 0   -9.11583423614502  10
"C83653"   85 "QK1" "Y60" "E07000133" 0 1  -8.939094543457031   5
"J82199" 6064 "QRL" "Y59" "E06000044" 0 1  -8.571514129638672  28
"D81005"   85 "QUE" "Y61" "E07000008" 0 0   -8.51644229888916 106
"E85119"   85 "QRV" "Y56" "E09000009" 0 0   -8.37378978729248  24
"D82088" 7074 "QMM" "Y61" "E07000148" 0 1   -8.16024398803711   9
"M85684"   85 "QHL" "Y60" "E08000025" 0 0 -7.9119672775268555   5
"K83009" 6064 "QPM" "Y60" "E06000062" 0 1   -7.63890266418457 257
"Y02646"   85 "QJG" "Y61" "E07000071" 0 1  -6.898565769195557   1
"J82663" 6064 "QRL" "Y59" "E06000045" 0 1  -6.608785629272461  41
"E86030" 6064 "QRV" "Y56" "E09000017" 0 1  -6.596933841705322  38
"E87677" 8084 "QRV" "Y56" "E09000033" 0 0  -6.307487487792969  22
"C82111" 5559 "QK1" "Y60" "E07000130" 0 1  -6.147696018218994  30
"F83005"   85 "QMJ" "Y56" "E09000007" 0 0  -6.116572856903076  13
"E86030" 6569 "QRV" "Y56" "E09000017" 0 0  -6.075657844543457  36
"Y02509"   85 ""    ""    "E08000036" 0 0  -5.828390598297119   2
"C82671"   85 "QK1" "Y60" "E06000016" 0 0  -5.793486595153809  12
"E82058" 5559 "QM7" "Y61" "E07000241" 0 1  -5.660310745239258 335
"E82058" 4549 "QM7" "Y61" "E07000241" 0 1   -5.27916145324707 294
"E83668"   85 "QMJ" "Y56" "E09000003" 0 0  -5.265721321105957  15
"J82199" 6569 "QRL" "Y59" "E06000044" 0 0  -5.077542304992676  15
"M91621"   85 "QUA" "Y60" "E08000030" 0 0  -5.055865287780762  15
"D81637" 6064 "QUE" "Y61" "E07000012" 0 0 -4.8438191413879395  76
"E86609"   85 "QRV" "Y56" "E09000017" 0 0  -4.790482044219971   6
"F86642"   85 "QMF" "Y56" "E09000026" 0 0  -4.789851665496826  13
"C82111" 6569 "QK1" "Y60" "E07000130" 0 1   -4.70037841796875   9
"K81074" 6064 "QNQ" "Y59" "E06000040" 0 0  -4.584527015686035 222
"F82647"   85 "QMF" "Y56" "E09000002" 0 0  -4.571387767791748  10
"D81005" 6064 "QUE" "Y61" "E07000008" 0 1  -4.470441818237305 166
"Y03597"   85 "QHL" "Y60" "E08000025" 0 0  -4.434232234954834  12
"M83670" 6569 "QNC" "Y60" "E07000195" 0 1  -4.399631023406982  25
"A82620"   85 "QHM" "Y63" "E07000030" 0 0  -4.315690994262695  13
"K81074" 6064 "QNQ" "Y59" "E06000040" 0 1  -4.079836845397949 204
"F84671"   85 ""    ""    "E09000025" 0 1  -3.950807571411133   3
"K83009" 6064 "QPM" "Y60" "E06000062" 0 0 -3.7878081798553467 281
"E82058" 5559 "QM7" "Y61" "E07000241" 0 0  -3.515435218811035 262
"Y02973"   85 "QMF" "Y56" "E09000016" 0 0  -3.377824306488037   6
"B82047" 6569 "QOQ" "Y63" "E06000014" 0 1 -3.2311606407165527 156
"E82058" 6569 "QM7" "Y61" "E07000241" 0 1 -3.1290903091430664 244
"Y00186"   85 "QOP" "Y62" "E08000001" 0 0 -3.1146881580352783   1
"E86030" 6064 "QRV" "Y56" "E09000017" 0 0  -2.675084114074707  37
"M85739"   85 "QHL" "Y60" "E08000025" 0 0  -2.380293369293213  13
"A86007"   85 "QHM" "Y63" "E08000021" 0 0 -2.3514721393585205  22
"K81074" 5559 "QNQ" "Y59" "E06000040" 0 1 -2.2982900142669678 283
"K84065"   85 "QU9" "Y59" "E07000177" 0 0 -2.0054702758789063  30
"F86030"   85 "QMF" "Y56" "E09000031" 0 0 -1.9452468156814575  10
"L81617" 6569 "QOX" "Y58" "E06000022" 0 1 -1.5682507753372192  13
"E83039" 6569 "QMJ" "Y56" "E09000003" 0 1 -1.5227411985397339  82
"K84078"   85 "QU9" "Y59" "E07000178" 0 1  -.9192015528678894  12
"K83009" 5559 "QPM" "Y60" "E06000062" 0 1  -.7577022314071655 277
"F81717"   85 ""    ""    "E07000074" 0 0  -.7469560503959656   9
"F82665"   85 ""    ""    "E09000026" 0 0  -.7444072961807251   8
"C84023" 6569 "QT1" "Y60" "E06000018" 0 1 -.42470598220825195  47
"Y03664"   85 "QMJ" "Y56" "E09000003" 0 1  -.3642313480377197  13
"K81622" 6064 "QU9" "Y59" "E06000041" 0 0 -.31412598490715027  54
"D81005"   85 "QUE" "Y61" "E07000008" 0 1 -.31140029430389404  49
"F82639"   85 ""    ""    "E09000016" 0 0 -.13041850924491882  19
"D81005" 6064 "QUE" "Y61" "E07000008" 0 0 -.11328431963920593 180
"B83617"   85 "QWO" "Y63" "E08000032" 0 0 -.09035468101501465   4
"E82058" 6569 "QM7" "Y61" "E07000241" 0 0  .06318636983633041 220
"Y04273" 8084 "QMF" "Y56" "E09000025" 0 0   .2568659782409668   3
"G82719"    4 "QKS" "Y59" "E06000035" 0 0  .42276298674175455  58
"Y02622" 6569 "QOP" "Y62" "E08000006" 0 0   .4316377639770508   9
"A86007"    4 "QHM" "Y63" "E08000021" 0 0   .4805622323053875  44
"F82653"    4 ""    ""    "E09000016" 0 0   .4865892658083382  53
"G82106"    4 "QKS" "Y59" "E06000035" 0 0   .4887954538111897 144
"D81618"    4 "QUE" "Y61" "E06000031" 0 0  .49075121730191806  55
"P81687"    4 "QE1" "Y62" "E07000126" 0 0  .49173568816081814  36
"E82126"    4 ""    ""    "E07000242" 0 0   .5052070448399678  68
"Y01281"    4 "QRL" "Y59" "E07000090" 0 0   .5275531925956364  88
"M84047"    4 "QWU" "Y60" "E07000221" 0 0   .5341103728044682  69
"M83013"    4 "QNC" "Y60" "E07000193" 0 0   .5372457595080377  90
"C83628"    4 ""    ""    "E07000141" 0 0   .5396616150134889  25
"F82002"    4 "QMF" "Y56" "E09000016" 0 0   .5397351075030137  94
"F81165"    4 "QM7" "Y61" "E07000072" 0 0   .5397899212880802 104
"H81656"    4 "QXU" "Y59" "E07000208" 0 0   .5485985156471634 226
"M84069"    4 "QWU" "Y60" "E07000222" 0 0   .5561715270755502 101
"D81041"    4 "QUE" "Y61" "E07000012" 0 0   .5569592412746048 142
"F82643"    4 ""    ""    "E09000016" 0 0   .5607334091124383  27
"E82626"    4 "QM7" "Y61" "E07000099" 0 0   .5622884017091465  52
"C82663"    4 ""    ""    "E07000134" 0 0   .5705252520021133  42
"H81107"    4 "QXU" "Y59" "E07000207" 0 0   .5713180847009584 188
"K82028"    4 "QU9" "Y59" "E06000060" 0 0   .5720413974772797 208
"M85782"    4 "QHL" "Y60" "E08000025" 0 0     .57699870272184  38
"Y00589"    4 "QH8" "Y61" "E07000070" 0 0   .5779175116319389 102
"K81069"    4 "QU9" "Y59" "E06000041" 0 0   .5802825259193249 393
"M89030"    4 "QHL" "Y60" "E08000029" 0 0   .5847612896312618  91
"F81061"    4 "QH8" "Y61" "E07000075" 0 0   .5860789977451591 124
"F81045"    4 "QH8" "Y61" "E07000066" 0 0   .5876574437662793  68
"E82121"    4 "QM7" "Y61" "E07000242" 0 0    .592008703356577 127
"G83029"    4 "QKK" "Y56" "E09000004" 0 0   .5920179854121667 173
"F81066"    4 "QH8" "Y61" "E07000075" 0 0   .5977225152305756 129
"K81103"    4 "QU9" "Y59" "E06000037" 0 0   .5992618085480766 151
"B82029"    4 "QOQ" "Y63" "E07000166" 0 0   .6008979713839951  50
"H81079"    4 ""    ""    "E07000212" 0 0   .6024055223632451  90
"M89028"    4 ""    ""    "E08000029" 0 0   .6028716496239892 257
"P87627"    4 "QOP" "Y62" "E08000006" 0 0   .6029178689131394  70
"H85063"    4 "QWE" "Y56" "E09000029" 0 0   .6155868434535371 117
"M91637"    4 "QUA" "Y60" "E08000030" 0 0   .6174441331199436  99
"N81126"    4 ""    ""    "E06000050" 0 0   .6209029020565262  88
"E82130"    4 "QM7" "Y61" "E07000242" 0 0   .6217421450333195 178
"J82628"    4 "QNQ" "Y59" "E07000089" 0 0   .6240139567872124 178
"K83079"    4 "QPM" "Y60" "E06000061" 0 0   .6261714808442974 125
"F81040"    4 "QH8" "Y61" "E07000070" 0 0   .6284902376447735  65
"B81082"    4 "QOQ" "Y63" "E06000011" 0 0   .6288488764099236 143
"B81104"   85 "QOQ" "Y63" "E06000010" 0 0   .6299454569816589   5
"B81619"    4 "QOQ" "Y63" "E06000011" 0 0   .6330397818053017  78
"K82035"    4 "QU9" "Y59" "E06000060" 0 0    .634444122438036  95
"J82079"    4 "QRL" "Y59" "E07000084" 0 0   .6347944722893467 211
"F81013"    4 "QH8" "Y61" "E07000066" 0 0   .6363585941506062 282
"L81617"    4 "QOX" "Y58" "E06000022" 0 0     .63805381072291  16
"K84003"    4 "QU9" "Y59" "E07000177" 0 0   .6396473066613236 142
"H81613"    4 "QXU" "Y59" "E07000208" 0 0    .643256536573082  94
"B81044"    4 ""    ""    "E06000011" 0 0   .6441935276167641 126
"F81717"    4 ""    ""    "E07000074" 0 0   .6444921577015381  19
"K81006"    4 "QNQ" "Y59" "E06000036" 0 0    .645205768246914 587
"C81046"    4 "QJ2" "Y60" "E07000036" 0 0   .6463712582886926 109
"K82012"    4 "QU9" "Y59" "E06000060" 0 0   .6482391287345162 170
"G82226"    4 "QKS" "Y59" "E06000035" 0 0   .6518456084075814  97
"G84006"    4 "QKK" "Y56" "E09000006" 0 0   .6523700636472779 254
"G84030"    4 "QKK" "Y56" "E09000006" 0 0   .6527803839812051 157
"N81043"    4 "QYG" "Y62" "E06000049" 0 0   .6549348140197602 121
"J83063"    4 ""    ""    "E06000030" 0 0   .6563708969907456 646
"G82088"    4 "QKS" "Y59" "E07000107" 0 0   .6573921522921677 171
"B82047" 6064 "QOQ" "Y63" "E06000014" 0 1   .6578231453895569 152
"E82627"    4 ""    ""    "E07000242" 0 0   .6582279192728349  71
"B85005"    4 "QWO" "Y63" "E08000034" 0 0   .6583237192539826 148
"E82066"    4 "QM7" "Y61" "E07000096" 0 0   .6587574166168023  93
"G83024"    4 "QKK" "Y56" "E09000004" 0 0   .6600020524165306 161
"F81142"    4 "QH8" "Y61" "E07000069" 0 0   .6638133484767228 157
"G82793"    4 ""    ""    "E07000110" 0 0   .6653490512048188  57
"D81607"    4 "QUE" "Y61" "E07000012" 0 0   .6659635003661996  68
"G82711"    4 "QKS" "Y59" "E06000035" 0 0   .6673226530492936 138
"C82051"    4 "QK1" "Y60" "E07000132" 0 0   .6680883653388024 252
"G83009"    4 "QKK" "Y56" "E09000004" 0 0      .6690484278293 331
"F81162"    4 ""    ""    "E07000067" 0 0   .6693459119812851  53
"G82140" 6569 "QKS" "Y59" "E07000106" 0 1   .6703066825866699  63
"F81076"    4 "QH8" "Y61" "E07000074" 0 0   .6725876030499001 101
"B81121"    4 ""    ""    "E06000011" 0 0   .6731471238329354  61
"P82643"    4 "QOP" "Y62" "E08000001" 0 0   .6761836165368271 230
"L84078"    4 "QR1" "Y58" "E07000082" 0 0   .6767601735206566 118
"G82100"    4 "QKS" "Y59" "E06000035" 0 0   .6770387879793854 143
"C82628"    4 "QK1" "Y60" "E07000132" 0 0   .6771589581311761  63
"F81185"    4 ""    ""    "E07000070" 0 0   .6776764613786203 122
"F81034"    4 "QM7" "Y61" "E07000077" 0 0   .6845075195239869 170
"F81648"    4 ""    ""    "E07000066" 0 0   .6855685004954224   7
"E82028"    4 ""    ""    "E07000096" 0 0   .6867290248284493 237
"K83022"    4 "QPM" "Y60" "E06000062" 0 0   .6868640157109757 214
"D82600"    4 "QMM" "Y61" "E07000145" 0 0   .6894241059567834  31
"G82751"    4 ""    ""    "E07000110" 0 0    .689429071665535  98
"P81757"    4 "QE1" "Y62" "E07000122" 0 0   .6905124365241128  70
"P81155"    4 "QE1" "Y62" "E06000008" 0 0   .6922654081089745 118
"K82042"    4 ""    ""    "E06000060" 0 0   .6925514329330712  95
"H81664"    4 ""    ""    "E07000217" 0 0   .6940264776188775  45
"F81737"    4 "QH8" "Y61" "E07000068" 0 0   .6942474134605675  81
"D81040"    4 ""    ""    "E07000011" 0 0   .6950812705532532  37
end
label values age ageband

------------------ copy up to and including the previous line ------------------

[Nick Cox]

Take out the space before [_N] ???

[Daniel Sutcliffe]

Yes, that worked thank you.