Assign values from another row

[Dominik Stehno]

Hello,

I have this data about commuting to the kindergartens. ZUJ is the ID of the town/village. Rok = year, DETI = number of children in the specific town/village, MS_kapacita = number of places in the kindergartens in this specific town/village. I have also ZUJs of the towns/villages that can be reached by car within 10 minutes (min10_1, min10_2, ...) and I need to create variables with the number of children in these near towns/villages (DETI_min10_1, DETI_min10_2,...) and also number of places in the kindergartens in these near towns/villages (MS_kapacita_min10_1, MS_kapacita_min10_2, ...).
Variable ZUJ contains all the towns/villages. So, basically I need to find the value of the "min10_1" in ZUJ column and assign information from this row to the row where the value of "min10_1" is.

I hope that I described the problem understandably. Sorry that the naming of variables is not in English.

Thank you very much in advance for your advise!

[Clyde Schechter]

The following code is untested because you did not provide example data in a usable way. So it may contain typos or other errors. In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 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.

Also, this code requires version 16.0 or later because it uses -frame-s. The Forum FAQ states that if you are not using the current version of Stata you should say so in your post; so I am assuming you have version 16.

frame put ZUJ min10_* rok DETI MS_kapacita, into(nearby)

forvalues i = 1/3 { // REPLACE 3 WITH ACTUAL NUMBER OF min10_* VARIABLES
    frlink 1:1 ZUJ rok, frame(nearby min10_`i' rok)
    frget DETI_min10_`i' = DETI, from(nearby)
    frget MS_kapacita_min10_`i' = MS_kapacita, from(nearby)
    drop nearby
}

[Dominik Stehno]

Thank you Clyde! Sorry, I did not read the Forum FAQ earlier. Yes, I have version 16.

I tried your code and Stata said this: "invalid match variables for 1:1 or m:1 match
The variables you specified for matching do not uniquely identify the observations in frame
nearby. Each observation in the current frame default must link to one observation in
nearby."

Could you help me one more time? Thank you very much!

Data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long ZUJ float(rok DETI MS_kapacita) long(min10_1 min10_2 min10_3 min10_4 min10_5 min10_6 min10_7 min10_8 min10_9 min10_10 min10_11 min10_12 min10_13 min10_14 min10_15 min10_16)
500259 2001   87   50 500259 568431 568732 547000 599689 599603      .      .      .      .      .      .      . . . .
500259 2011   79   50 500259 568431 568732 547000 599689 599603      .      .      .      .      .      .      . . . .
500291 2001   80   50 500291 506711 599549      .      .      .      .      .      .      .      .      .      . . . .
500291 2011  118   69 500291 506711 599549      .      .      .      .      .      .      .      .      .      . . . .
500496 2001 3274 3415 500496 547026 554901 552089      .      .      .      .      .      .      .      .      . . . .
500496 2011 4127 3669 500496 547026 554901 552089      .      .      .      .      .      .      .      .      . . . .
500526 2001   63   70 500526 500852 501646 552089 547077 552020      .      .      .      .      .      .      . . . .
500526 2011  120   92 500526 500852 501646 552089 547077 552020      .      .      .      .      .      .      . . . .
500623 2001   36   50 500623 540196 552178 552194 540480 503444      .      .      .      .      .      .      . . . .
500623 2011   43   50 500623 540196 552178 552194 540480 503444      .      .      .      .      .      .      . . . .
500801 2001   28   25 500801 503304 568872 590185 501794 501841      .      .      .      .      .      .      . . . .
500801 2011   25   25 500801 503304 568872 590185 501794 501841      .      .      .      .      .      .      . . . .
500852 2001   93   89 500852 552020 552011 500526 501646 552089 547077      .      .      .      .      .      . . . .
500852 2011  119  110 500852 552020 552011 500526 501646 552089 547077      .      .      .      .      .      . . . .
500861 2001   51   40 500861 552194      .      .      .      .      .      .      .      .      .      .      . . . .
500861 2011   55   40 500861 552194      .      .      .      .      .      .      .      .      .      .      . . . .
500879 2001   16   20 500879 589829 590185 502235 503657 589403      .      .      .      .      .      .      . . . .
500879 2011   38   20 500879 589829 590185 502235 503657 589403      .      .      .      .      .      .      . . . .
501476 2001   84   75 501476 504785 505218      .      .      .      .      .      .      .      .      .      . . . .
501476 2011   84   75 501476 504785 505218      .      .      .      .      .      .      .      .      .      . . . .
501646 2001   70   77 501646 552089 547077 500526 552020 500852      .      .      .      .      .      .      . . . .
501646 2011  151   96 501646 552089 547077 500526 552020 500852      .      .      .      .      .      .      . . . .
501751 2001   74   59 501751 552232 589870 505111 504505 505269 505081 589381 590011 552364 503657      .      . . . .
501751 2011   56   59 501751 552232 589870 505111 504505 505269 505081 589381 590011 552364 503657      .      . . . .
501794 2001   47   70 501794 552119 568872 503304 512800 500801 519146 589284 512982 503738      .      .      . . . .
501794 2011   72   70 501794 552119 568872 503304 512800 500801 519146 589284 512982 503738      .      .      . . . .
501841 2001   44   60 501841 505650 503304 500801 514152 568872 503738      .      .      .      .      .      . . . .
501841 2011   79   45 501841 505650 503304 500801 514152 568872 503738      .      .      .      .      .      . . . .
502146 2001  170  185 502146 554944      .      .      .      .      .      .      .      .      .      .      . . . .
502146 2011  168  193 502146 554944      .      .      .      .      .      .      .      .      .      .      . . . .
502235 2001   40   50 502235 503657 552364 505111 589829 505269 500879 554901 503304 590011      .      .      . . . .
502235 2011   75   68 502235 503657 552364 505111 589829 505269 500879 554901 503304 590011      .      .      . . . .
502405 2001   24   25 502405 569844 505862 569003 552160 552348 505161 505188 552356      .      .      .      . . . .
502405 2011   27   25 502405 569844 505862 569003 552160 552348 505161 505188 552356      .      .      .      . . . .
502545 2001   56   50 502545 552151 554901 505013 505161      .      .      .      .      .      .      .      . . . .
502545 2011  108   78 502545 552151 554901 505013 505161      .      .      .      .      .      .      .      . . . .
502839 2001   21   60 502839 552178 552038 505081 503444 568961      .      .      .      .      .      .      . . . .
502839 2011   41   60 502839 552178 505081 503444 568961      .      .      .      .      .      .      .      . . . .
503142 2001   17   25 503142      .      .      .      .      .      .      .      .      .      .      .      . . . .
503142 2011   24   28 503142      .      .      .      .      .      .      .      .      .      .      .      . . . .
503304 2001   29   30 503304 500801 568872 501794 501841 590185      .      .      .      .      .      .      . . . .
503304 2011   45   30 503304 500801 568872 501794 501841 590185      .      .      .      .      .      .      . . . .
503410 2001    5    0 537756 537411      .      .      .      .      .      .      .      .      .      .      . . . .
503410 2011    6    0 537756 537411      .      .      .      .      .      .      .      .      .      .      . . . .
503444 2001  361  329 503444 552178 552186 504441 502839 505013 500623 552160      .      .      .      .      . . . .
503444 2011  373  348 503444 552178 552186 504441 502839 505013 500623 552160      .      .      .      .      . . . .
503622 2001   18   25 503622 589560 552194      .      .      .      .      .      .      .      .      .      . . . .
503622 2011   32   38 503622 589560 552194      .      .      .      .      .      .      .      .      .      . . . .
503657 2001  107  180 503657 505111 502235 590011 589829 505269 552232 500879 552364 501751      .      .      . . . .
503657 2011  134  180 503657 505111 502235 590011 589829 505269 552232 500879 552364 501751      .      .      . . . .
503738 2001   49   30 503738 512800 512982 514152 517607 501794 505650      .      .      .      .      .      . . . .
503738 2011   51   50 503738 512800 512982 514152 517607 501794 505650      .      .      .      .      .      . . . .
503916 2001   24   30 503916 558869      .      .      .      .      .      .      .      .      .      .      . . . .
503916 2011   23   28 503916 558869      .      .      .      .      .      .      .      .      .      .      . . . .
503941 2001   90   93      .      .      .      .      .      .      .      .      .      .      .      .      . . . .
504246 2001   32    0 500623 503444 552178 552194 540471 540480 540196      .      .      .      .      .      . . . .
504246 2011   21    0 500623 503444 552178 552194 540471 540480 540196      .      .      .      .      .      . . . .
504301 2001   15    0 574864 575372 571245 575739 575437      .      .      .      .      .      .      .      . . . .
504301 2011   17    0 574864 575372 571245 575739 575437      .      .      .      .      .      .      .      . . . .
504441 2001   52   50 504441 505013 552151 547018 554901 503444 505081 552178      .      .      .      .      . . . .
504441 2011   83   70 504441 505013 552151 547018 554901 503444 505081 552178      .      .      .      .      . . . .
504505 2001   70  120 504505 552232 505081 505111 501751 505269 590011 503657      .      .      .      .      . . . .
504505 2011   77  120 504505 552232 505081 505111 501751 505269 590011 503657      .      .      .      .      . . . .
504785 2001   57   47 504785 501476 505501 554103      .      .      .      .      .      .      .      .      . . . .
504785 2011   63   70 504785 501476 505501      .      .      .      .      .      .      .      .      .      . . . .
504807 2001   13    0 571245 547891 571385 574864      .      .      .      .      .      .      .      .      . . . .
504807 2011   15    0 571245 571873 547891 571385 574864      .      .      .      .      .      .      .      . . . .
504921 2001   14    0 571245 574864 575372 571385 575739 571164      .      .      .      .      .      .      . . . .
504921 2011    9    0 571245 574864 575372 571385 575739 571164      .      .      .      .      .      .      . . . .
504955 2001    4    0 571491 575232 572420 574899 575399 575658      .      .      .      .      .      .      . . . .
504955 2011    7    0 572446 571491 575232 572420 574899 575399 575658      .      .      .      .      .      . . . .
505005 2001   18    0 571768 571962 572268 571539 572217      .      .      .      .      .      .      .      . . . .
505005 2011   19   60 505005 571768 571962 572268 571539 572217      .      .      .      .      .      .      . . . .
505013 2001   41   65 505013 504441 552151 554901 502545 503444      .      .      .      .      .      .      . . . .
505013 2011   56   60 505013 504441 552151 554901 502545 503444      .      .      .      .      .      .      . . . .
505030 2001   13    0 571491 572420 575232      .      .      .      .      .      .      .      .      .      . . . .
505030 2011    8    0 571491 572446 572420 575232      .      .      .      .      .      .      .      .      . . . .
505081 2001   72  110 505081 552232 504505 502839 505111 501751 504441 505269      .      .      .      .      . . . .
505081 2011   57   90 505081 552232 504505 502839 505111 501751 504441 505269      .      .      .      .      . . . .
505099 2001    8   26 505099 573965 574481 574074      .      .      .      .      .      .      .      .      . . . .
505099 2011    6   26 505099 573965 574481 574074      .      .      .      .      .      .      .      .      . . . .
505111 2001   56   60 505111 590011 503657 552232 501751 504505 502235 505269 589403 590029 505081 589829 552364 . . .
505111 2011   62   60 505111 590011 503657 552232 501751 504505 502235 505269 589403 590029 505081 589829 552364 . . .
505145 2001   64   50 505145 577863 572691 572586      .      .      .      .      .      .      .      .      . . . .
505145 2011  106   75 505145 577863 572691 572586      .      .      .      .      .      .      .      .      . . . .
505161 2001  137  140 505161 569003 502405 552011 505862 547018 552160 569844 502545      .      .      .      . . . .
505161 2011  142  145 505161 569003 502405 552011 505862 547018 552160 569844 502545      .      .      .      . . . .
505188 2001  456  355 505188 569844 552356 502405 552348 552011 552313 554103      .      .      .      .      . . . .
505188 2011  569  416 505188 569844 552356 502405 552348 552011 552313      .      .      .      .      .      . . . .
505218 2001   80   72 505218 505293 501476 539961      .      .      .      .      .      .      .      .      . . . .
505218 2011   74   65 505218 505293 501476 539961      .      .      .      .      .      .      .      .      . . . .
505269 2001   43   50 505269 552364 552232 505111 501751 502235 504505 503657 505081      .      .      .      . . . .
505269 2011   65   70 505269 552364 552232 505111 501751 502235 504505 503657 505081      .      .      .      . . . .
505293 2001   82   70 505293 552381 552372 505218 536687      .      .      .      .      .      .      .      . . . .
505293 2011   89   70 505293 552381 552372 505218 536687      .      .      .      .      .      .      .      . . . .
505366 2001   51    0 568392 569771 514471 552411 547450 552445      .      .      .      .      .      .      . . . .
505366 2011   84    0 568392 569771 514471 552411 547450 552445      .      .      .      .      .      .      . . . .
505391 2001   11    0 577871 582646 578193 578274      .      .      .      .      .      .      .      .      . . . .
505391 2011   14    0 577871 582646 578193 578274      .      .      .      .      .      .      .      .      . . . .
505501 2001   58   25 505501 552348 505587 504785 505862 552356      .      .      .      .      .      .      . . . .
end

[Ken Chui]

While you're waiting, feel free to try this version:

preserve
keep ZUJ rok DETI MS_kapacita
rename DETI DETI_using
rename MS_kapacita MS_kapacita_using
save tempusing, replace
restore

rename ZUJ ZUJ_original

foreach x of numlist 1/16{
rename min10_`x' ZUJ
merge m:1 ZUJ rok using tempusing
drop if ZUJ_original == .
drop _merge
rename DETI_using DETI_min10_`x'
rename MS_kapacita_using MS_kapacita_min10_`x'
rename ZUJ min10_`x'
}

rename ZUJ_original ZUJ
erase tempusing.dta

[Clyde Schechter]

Sorry about that. I had the roles of the data in the two frames reversed in my code. This will work:

frame put ZUJ rok DETI MS_kapacita, into(crosswalk)
isid ZUJ rok, sort

forvalues i = 1/16 {
    frlink m:1 min10_`i' rok, frame(crosswalk ZUJ rok)
    frget min10_`i'_DETI = DETI, from(crosswalk)
    frget min10_`i'_MS_kapacita = MS_kapacita, from(crosswalk)
    drop crosswalk
}

Note: In your example data, there are many missing values in the min10_* variables, and, consequently, there are messages generated about unmatched observations when the -frlink- commands execute. That is not a problem, don't worry about it. There will be corresponding missing values in the newly created variables.

[Dominik Stehno]

Hello Ken,

It worked! Great idea with the merge inside the loop. You helped me a lot! Thank You!

[Dominik Stehno]

Thanks a lot Clyde!