Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    How to replace missing values of one variable with those of the adjacent variable

    Hello everyone,

    I am working with the following data with variables HHID (household ID) and some variables s1, s2, s3...s147

    I want to write a loop where instead of missing values for s3 (as in line 1 of the data), I want to replace s4 value to be s3, and the next available value for example s45 to be s4. I wrote the following code:

    Code:
    forval i = 1/144{
    replace s`i' = s`i+1' if s`i' ==.
}
    Naturally it didn't work. Is there anyway to fix this code?

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input long HHID int(s1 s2 s3 s4 s5 s6)
39846680 1377  554    .  407    .    .
39849080 1122   25    .    .    .    .
39849597 1858 1333    .    . 1075  586
39860292 1717 1711 1712 1713  473  268
39860383 1789  869    .  807  874    .
39864269 1750 1746  860  800  800  860
39865883  177  395  251    .   41   42
39868537  194  968  967 1336    . 1335
39870715 1704  683  697 1560 1559    .
39871735  744 1607    .    .    .  733
39953078    .    .    . 1128    .    .
40102812  488  142    .  142    . 1309
40104157 1723 1729 1068 1943    . 1611
40104161 1739 1743 1743 1742 1735 1449
40104320 1870 1871 1521 1932    . 1933
39884541 1457 1456 1282    .    . 1281
39884589 1704  684  704  722    .    .
39885252 1744 1753 1752 1747  812    .
39885589 1704  683  696    . 1943    .
39885717    .    . 1501    . 1500 1463
39885987 1370    .  620  486  190    .
39886070 1414  716  481  713  775  776
39886077  757 1282    .    . 1281 1310
39886620 1734 1733  585    .    .    .
39886640 1291 1290 1294    . 1293 1292
39886694 1684  485 1309    .  142    .
39887177 1870 1871 1521 1932    . 1933
39887300 1704  682  719  677  618  473
39887620 1367 1860 1735 1449  426 1681
39888105    . 1899 1568 1569 1570 1571
39888238 1701 1082  473  268  511    .
39888754 1784 1879 1880 1881 1882  804
39889051  628  455  585 1733 1734    .
39889269    .    .    .    .    .    .
39889499 1062 1091    . 1465    .    .
39889668 1717 1711 1712 1713  473  268
39890325 1784  972  935  903 1591  900
39890706 1897 1898  695 1851 1894 1893
39891038  744 1607    .    .    .  733
39891136    . 1086  728 1068 1085 1718
39891607 1087    . 1086  728  681 1698
39891622 1370    .  620  486  190    .
39891797 1717 1711 1725 1082  473  472
39891936 1870 1871 1521 1932    . 1933
39892365 1719 1713  473  472 1823 1824
39892651 1370    .  620  486  190  166
39893968 1099 1098    .  454 1437 1438
39894099    .    . 1448 1014    .    .
39894781 1676 1746 1750 1741 1749  804
39894802  190  486  620 1370    .    .
39895298  620  486  190  166  578    .
39895432 1415    .    .    .    .    .
39895783  763 1399  493  492   75  407
39896001 1087    . 1086  728  681 1698
39896197 1785 1609 1610 1611 1943    .
39896267 1704  683  696    . 1943    .
39896504 1702 1728    . 1729 1068 1943
39896661 1717 1711 1712   17  677  719
39896722 1718 1085 1068 1943    . 1611
39897204    .  585 1733 1734  447 1231
39897215  166  578    .  593  192    .
39897737 1865 1893 1894    .    .    .
39898739 1558    .    .    .    .    .
39898868 1870 1877    .    .    .    .
39899564 1370    .  620  486  190    .
39899894 1893 1865 1858 1333    .    .
39900010 1723 1729 1068 1943    . 1611
39900272 1704  683  696    . 1943    .
39900281 1723 1729 1708 1691    . 1690
39900531  758 1321 1343 1766    . 1883
39901062 1283 1276  680 1708 1709 1710
39901104  188    . 1682    . 1681  426
39901674  304  337    .  794  793  283
39902297    .  711  679    . 1708 1709
39902426 1704  683  696    . 1943    .
39902451 1704  682  719  713  481  717
39902515    . 1086  728  681 1698 1998
39902529 1704  682  719  677  618  473
39902574 1360    . 1359    .    . 1933
39902642    .  585 1733 1734  447 1231
39902861  165 1324 1323 1322 1320 1319
39903075 1717 1718 1719 1713  473  268
39903175 1719 1713  473  268  511    .
39903403  620  486  190  166  578    .
39904048 1717 1711 1725 1082  473  268
39904521    .    .    .  711  679    .
39905609 1704  683  696    . 1943    .
39906047 1701 1082  473  268  511    .
39906539  752  609 1039    . 1040    .
39906566  620  486  190  166  578    .
39906747 1784  972  935  935  972 1784
39907007 1558    .    .    .    .    .
39907257    . 1899 1568 1569 1570 1571
39907855 1715  775  776  682  719  677
39908362    . 1894 1893 1865    .    .
39908696 1717 1711 1712 1713  473  268
39908873 1704  682  719  677  618  473
39908979    . 1086  728  681 1708 1709
39910067 1865 1893 1894 1851    .  695
39910390 1723 1729 1068 1943    . 1611
end
    Tags: None
  • #2
    I do not understand what you want to do at all.

    But to mechanically make your loop work, it has to be:

    Code:
    forval i = 1/5 {
    replace s`i' = s`=`i'+1' if s`i' ==.
}

    Comment

    • #3
      With your data example (thanks!) consider also this:


      Code:
      reshape long s, i(HHID) j(which)
gen ismissing = missing(s)

bysort HHID (ismissing which) : replace which = _n 
drop ismissing 

reshape wide s, i(HHID) j(which)

list in 1/10 

     +---------------------------------------------------+
     |     HHID     s1     s2     s3     s4     s5    s6 |
     |---------------------------------------------------|
  1. | 39846680   1377    554    407      .      .     . |
  2. | 39849080   1122     25      .      .      .     . |
  3. | 39849597   1858   1333   1075    586      .     . |
  4. | 39860292   1717   1711   1712   1713    473   268 |
  5. | 39860383   1789    869    807    874      .     . |
     |---------------------------------------------------|
  6. | 39864269   1750   1746    860    800    800   860 |
  7. | 39865883    177    395    251     41     42     . |
  8. | 39868537    194    968    967   1336   1335     . |
  9. | 39870715   1704    683    697   1560   1559     . |
 10. | 39871735    744   1607    733      .      .     . |
     +---------------------------------------------------+

.

      Comment

      • #4
        Hi everyone,

        I ran into a similar issue but the code that Nick Cox and Joro Kolev does not work in this setting. I have merged an individual panel data to the corresponding household panel file. However, at some point between the survey some households split into 1, 2, or 3 units. This led to the surveyors appending the household unique ID with the suffix 1,2, or 3. This makes the merge really annoying because I end up not being able to merge my datasets completely. However when I sort the IDHH variables, I see that I just need to fill the missing values from the household information using just the IDHH below the individuals. I think this can get really confusing in words, so I have listed a sample below.

        For example, line 3 and 4 are the same household by has split, hence the change in IDHH from 010201090 to "0102010902", however, I just want variable HQ5 which is missing to be replaced to what is in the next line but only for _m == 1. And I want to repeat this all missing variables from the HH survey for the Individual. I can't split the ID since the merge does work for about 90% of the master data.


        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str10 IDHH int(HHSPLITID SURVEY HQ5 EWWAVES) byte _merge
"010201060"  1 1 3  . 2
"0102010601" 1 2 3  . 2
"010201090"  0 1 . 11 1
"0102010902" 2 2 2  . 2
"010201110"  1 1 2  . 2
"0102011101" 1 2 3  . 2
"010202050"  1 1 3  . 2
"0102020501" 1 2 3  . 2
"010202080"  1 1 3  . 2
"0102020801" 1 2 3  . 2
"010202090"  1 1 3  . 2
"0102020901" 1 2 3  . 2
"0102021201" 1 2 3  . 2
"0102021202" 2 2 3  . 2
"0102021203" 3 2 3  . 2
"010202130"  1 1 2  . 2
"0102021301" 1 2 3  . 2
"010203010"  1 1 3  . 2
"0102030101" 1 2 3  . 2
"010203040"  1 1 3  . 2
"0102030401" 1 2 3  . 2
"0102030801" 1 2 3  . 2
"0102030802" 2 2 3  . 2
"010203100"  0 1 . 11 1
"0102031001" 1 2 2  . 2
"010203180"  1 1 3  . 2
"0102031801" 1 2 3  . 2
"010203200"  1 1 .  . 2
"0102032001" 1 2 3  . 2
"010204020"  1 1 1  . 2
"0102040201" 1 2 3  . 2
"010204030"  1 1 .  . 2
"0102040301" 1 2 3  . 2
"010204050"  1 1 2  . 2
"0102040501" 1 2 3  . 2
"010204080"  0 1 . 11 1
"0102040801" 1 2 3  . 2
"010204100"  1 1 3  . 2
"0102041001" 1 2 2  . 2
"010204110"  1 1 3  . 2
"0102041101" 1 2 3  . 2
"010204130"  0 1 . 11 1
"0102041302" 2 2 3  . 2
"0102041303" 3 2 3  . 2
"010204140"  1 1 3  . 2
"0102041401" 1 2 3  . 2
"010204160"  1 1 3  . 2
"0102041601" 1 2 3  . 2
"010204170"  0 1 . 11 1
"0102041701" 1 2 3  . 2
"010204180"  1 1 2  . 2
"0102041801" 1 2 3  . 2
"010204190"  1 1 3  . 2
"0102041901" 1 2 3  . 2
"010205030"  1 1 3  . 2
"0102050301" 1 2 3  . 2
"0102050501" 1 2 3  . 2
"0102050502" 2 2 3  . 2
"010205060"  1 1 3  . 2
"0102050601" 1 2 3  . 2
"010205080"  1 1 2  . 2
"0102050801" 1 2 3  . 2
"010205100"  1 1 3  . 2
"0102051001" 1 2 3  . 2
"010205160"  0 1 . 11 1
"0102051602" 2 2 3  . 2
"010206130"  1 1 3  . 2
"0102061301" 1 2 3  . 2
"010207020"  0 1 . 11 1
"0102070202" 2 2 3  . 2
"0102070203" 3 2 3  . 2
"0102070204" 4 2 3  . 2
"010207040"  0 1 . 11 1
"0102070402" 2 2 3  . 2
"010207050"  0 1 . 11 1
"0102070502" 2 2 3  . 2
"010207140"  1 1 2  . 2
"0102071401" 1 2 3  . 2
"010208020"  1 1 2  . 2
"0102080201" 1 2 3  . 2
"010208080"  1 1 3  . 2
"0102080801" 1 2 3  . 2
"010208150"  1 1 3  . 2
"0102081501" 1 2 3  . 2
"010301030"  1 1 2  . 2
"0103010301" 1 2 3  . 2
"010301080"  1 1 3  . 2
"0103010801" 1 2 3  . 2
"010301110"  1 1 3  . 2
"0103011101" 1 2 3  . 2
"010301140"  1 1 2  . 2
"0103011401" 1 2 1  . 2
"010302040"  1 1 3  . 2
"0103020401" 1 2 3  . 2
"0103020601" 1 2 3  . 2
"0103020602" 2 2 3  . 2
"010302090"  1 1 2  . 2
"0103020901" 1 2 3  . 2
"010302100"  1 1 2  . 2
"0103021001" 1 2 2  . 2
end
label values SURVEY SURVEY
label def SURVEY 1 "survey 1", modify
label def SURVEY 2 "survey 2", modify
label values EWWAVES EWWAVES
label def EWWAVES 11 "both 11", modify
label values _merge _merge
label def _merge 1 "Master only (1)", modify
label def _merge 2 "Using only (2)", modify

        Comment

        Previous Next
        Working...
        X