How to check and delete missing variables per row?

[Geert van Hooff]

Hi all,

Is there an option in stata where you can check if there is more than 50% missing variables per row? And ultimately delete them with a command? As you can see this dataset contains a lot of missing variables.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 cusip double(indcode perc2012 perc2011 perc2010 perc2009 perc2008 perc2007 perc2006 perc2005 perc2004 perc2003 perc2002) float(BHOLDER_INSTTOTAL BHOLDER_INST)
"00036020" 122 .24641723185777664  .2557187080383301  .31095239520072937 .08826593309640884 .13342519477009773 .22401223704218864  .22725877165794373  .20331937074661255  .17584679275751114 .08498767763376236  .06626297533512116 2.0164673   .1833152
"00036110" 101 .48147690668702126  .3437000811100006   .3406209610402584  .4810544177889824 .17274834960699081  .2962655536830425   .2576181888580322   .5361181348562241   .5221223346889019  .5554310530424118  .22294123470783234 4.2100973   .3827361
"00040010" 105                  .                  .                   .                  .                  .                  .                   .                   .                   .                  .  .05495549738407135  .0549555   .0549555
"00078810" 120                  .                  .                   .                  .                  .                  .                   .                   .                   .                  . .056170426309108734 .05617043  .05617043
"00080S10" 103                  .                  .                   .                  . .09402354806661606 .14038486406207085  .20932302623987198                   .                   .                  .                   .  .4437314  .14791048
"00081T10" 122  .4015965946018696  .6074439808726311    .392490167170763  .5347161255776882  .6640250161290169  .2560748718678951   .1165144294500351                   .                   .                  .                   .  2.972861  .42469445
"00083310"   .                  .                  .                   .                  . .22516696155071259 .18096590414643288                   .                   .                   .                  .                   .  .4061329  .20306644
"00086T10" 121                  . .43622110038995743   .5281192138791084  .5324973091483116  .5231840312480927  .5267514102160931  .31626952439546585  .23262345790863037  .23187987506389618 .06724825501441956   .1275533065199852  3.522348  .35223475
"00088610" 131                  .                  .                   .                  .                  .                  .                   .                   .                   . .06115280091762543                   .  .0611528   .0611528
"00088630" 131                  .                  .   .3187168762087822  .2805864065885544 .19214610010385513 .11236406117677689   .2139357626438141                   .                   .                  .                   . 1.1177492  .22354984
"00088U10" 108                  . .08180171996355057   .1309933289885521  .2781215123832226 .09104244410991669                  .  .06300012022256851                   .                   .                  .                   .  .6449592  .12899183
"00089C10" 122                  .                  .                   .                  .                  .                  .  .34930384904146194    .297583881765604   .3096800558269024 .35407254844903946  .35267652571201324  1.663317   .3326634
"00091810" 120                  .                  .                   .                  .                  . .39411313831806183  .26547861099243164   .2835339307785034     .31459841132164   .357064425945282                   . 1.6147885   .3229577
"00091910" 120                  .                  .                   .                  .                  . .21703457832336426  .09703806042671204                   .                   .                  .                   .  .3140726   .1570363
"00094910" 120                  .                  .                   .                  . .05085185915231705                  .                   .                   .                   .                  .                   . .05085186  .05085186
"00095510" 123                  .                  .                   .                  .                  .                  .                   .  .19686740636825562                   .  .1134926825761795  .09127970039844513  .4016398  .13387993
"00095710" 109 .37785232439637184  .3631378784775734   .3145303279161453   .338495597243309  .2921207547187805 .21689961105585098  .17439444363117218  .15840039402246475  .15152940154075623 .21194232627749443  .09789355099201202 2.6971965   .2451997
"00103110" 118  .2789914347231388 .24497883766889572  .10291831940412521  .3695842921733856  .3751503601670265 .20585967600345612   .3114125430583954   .1493246853351593   .1649678349494934 .22131332010030746  .14539645612239838  2.569898  .23362707
"00104H10" 122                  .                  .                   .                  .                  . .09548656642436981  .13586799055337906                   .                   .                  .                   . .23135456  .11567728
"00104Q10" 115  .5472665205597878  .4352050721645355   .4276002012193203  .5230916440486908  .4994755871593952  .6567288339138031   .6468057334423065   .2864946275949478  .09725165367126465 .14005932211875916  .23991475254297256  4.499894   .4090813
"00105810" 116                  .                  .                   .                  . .12149924784898758 .05354413017630577                   .                   .                   .                  .                   . .17504337  .08752169
"00108410" 122                  .  .2039307989180088   .3806115463376045  .1945168823003769 .18288297206163406  .3303457498550415   .2797962538897991  .13863275200128555   .1666983850300312 .36600272357463837   .2962082326412201  2.539626  .25396264
"00119R10" 120                  . .11706467717885971                   .                  .                  .                  .                   .                   .                   .                  .                   . .11706468  .11706468
"00120410" 118 .11598477512598038 .07181637734174728  .17421216517686844 .14464525878429413 .06885799765586853  .0666659027338028  .11617846414446831                   .  .06256788969039917 .07539800554513931  .09299829602241516  .9893252  .09893251
"00122810" 118 .22089028358459473                  .                   .                  .                  .                  .                   .                   .                   .                  .                   .  .2208903   .2208903
"00125010" 102                  .                  .                   .                  .                  .                  .                   .                   .                   .  .2108890563249588  .13810455054044724  .3489936   .1744968
"00128210" 127  .2767552025616169 .33045973256230354   .2905251048505306  .5734524913132191                  .                  .                   .                   .                   .                  .                   . 1.4711925   .3677981
"00129610" 110                  .                  .                   .                  .                  .                  .                   .                   .                   . .06840427964925766  .06696505099534988 .13536933 .067684665
"00130H10" 135 .06195978447794914                  .                   . .05033133178949356 .05463548004627228 .09430377930402756                   .                   .                   .                  .  .06558817625045776  .3268186  .06536371
"00130R10" 100                  .                  .                   .                  .                  .                  .                   .                   .                   . .06562443822622299  .06773585081100464  .1333603  .06668015
"00154710" 124 .10585945844650269 .18419701978564262  .22604694217443466 .12194329500198364  .1940658874809742 .33450231701135635  .23336708173155785   .0993426963686943  .06273455172777176 .10715029388666153  .11599999666213989 1.7852097   .1622918
"00162Q10" 120                  .  .6476330384612083   .6147425770759583  .2698267325758934                  .                  .                   .                   .                   .                  .                   . 1.5322024  .51073414
"00162Q20" 120                  . .22253333032131195  1.1383800357580185                  .                  .                  .                   .                   .                   .                  .                   . 1.3609134   .6804567
"00162Q30" 120                  .                  .  .20232303068041801                  .                  .                  .                   .                   .                   .                  .                   . .20232303  .20232303
"00162Q40" 120                  .   .682889997959137   .6869500279426575                  .                  .                  .                   .                   .                   .                  .                   .   1.36984     .68492
"00162Q60" 120                  . .22551999986171722   .7641999870538712                  .                  .                  .                   .                   .                   .                  .                   .    .98972     .49486
"00162Q80" 120                  . .10611333698034286                   .                  .                  .                  .                   .                   .                   .                  .                   . .10611334  .10611334
"00162Q86" 120                  . .32214535400271416                   .                  .                  .                  .                   .                   .                   .                  .                   .  .3221453   .3221453
"00163T10" 120                  .  .2861783057451248   .3378242664039135  .2802824229001999 .06473246961832047  .2213795743882656  .11333564668893814  .12465951219201088  .07206574082374573 .06887172907590866  .08670127391815186  1.656031   .1656031
"00163U10" 112 .49610795080661774  .4651320278644562    .739099457859993  .5593134351074696 .35491112247109413                  .                   .                   .                   .                  .                   .  2.614564   .5229128
"00164V10" 118 .31918662413954735                  .                   .                  .                  .                  .                   .                   .                   .                  .                   .  .3191866   .3191866
"00166910" 113                  .                  .                   .                  .                  .                  .                   .                   .  .16362358629703522 .15280987322330475  .12011689320206642 .43655035  .14551678
"00171210" 108                  .                  .   .5122120641171932 .46589940786361694  .3926825560629368 .38582104071974754   .2942247800529003                   .                   .                  .                   .   2.05084    .410168
"00173510" 120                  .                  .                   .                  .                  .                  .   .2508862614631653  .39698296785354614   .6733496785163879 .05264517292380333  .08920496702194214 1.4630692   .2926138
"00174410" 100  .4069442041218281 .15989738702774048  .34658439084887505  .3213559165596962  .4152107983827591  .1993461772799492  .41040733084082603   .2580714821815491  .33411529287695885                  .  .07183875888586044  2.923772  .29237717
"00176510" 103                  .  .1492711901664734  .12438006326556206 .25571858137845993 .21078897267580032  .1653359830379486  .39105046167969704   .3782307468354702  .45889168232679367   .316543523222208  .15272915363311768 2.6029406  .26029405
"00180C10" 130                  .                  .                   .                  .                  .                  .                   .                   .  .16691526770591736  .1770172268152237  .13097891211509705  .4749114   .1583038
"00181310" 110                  .                  .                   .                  .                  .                  .                   .                   .                   .                  .  .09983225911855698 .09983226  .09983226
"00184X10" 108  .4334893450140953                  .                   .                  .                  .                  .                   .                   .                   .                  .                   . .43348935  .43348935
"00185310" 111                  .                  .                   .                  .                  .                  .                   .                   .                   . .05893699452280998                   .   .058937    .058937
"00185E10" 108                  . .09096497297286987  .15377697348594666 .08846566081047058 .18602434545755386 .14524566754698753   .1549915447831154  .11403512209653854  .12190894410014153 .05273371562361717   .0543806329369545 1.1625276  .11625276
"00187E20" 124 .10865964740514755                  .                   .                  .                  .                  .                   .                   .                   .                  .                   . .10865965  .10865965
"00194110" 108                  .                  .                   .                  .                  .                  .  .13502897322177887                   .  .05818198621273041                  .                   . .19321096  .09660548
"00194710" 118                  .                  .                   .                  .                  .                  .                   .                   .                   .                  .  .28294500708580017   .282945    .282945
"00196310" 122                  .                  .                   .                  . .25343670696020126 .23458678275346756  .39349765330553055  .06393203884363174 .059957440942525864                  .                   . 1.0054107  .20108213
"00198410" 105                  .                  .                   .                  .                  .                  .  .08678952604532242  .08617023378610611  .08907587081193924  .0648631826043129                   .  .3268988   .0817247
"00202J10" 116                  .                  .                   .                  .                  .                  .  .14876310527324677  .15923766791820526  .20307238772511482  .2096589207649231   .2902066186070442 1.0109388  .20218775
"00202J20" 112 .06641069054603577 .17106220126152039  .23819910734891891 .22895488142967224  .3351166620850563                  .                   .                   .                   .                  .                   . 1.0397435   .2079487
"00204C10" 116                  .                  .                   .                  .                  .                  .                   .                   .                   . .49533040076494217                   .  .4953304   .4953304
"00207R10" 130  .3760843463242054  .2985900640487671  .26795201748609543 .29540420696139336   .276550967246294 .17961276322603226  .31731225177645683    .415902778506279  .32764529064297676 .26732877641916275   .3601515479385853  3.382535   .3075032
"00208310" 116                  .                  .                   . .05360269546508789 .13782570138573647 .06406360119581223   .1227055974304676  .21905764937400818  .12710413336753845 .10131803154945374  .08860142529010773  .9142788  .11428486
"00208B10" 130                  .                  .                   .                  .                  .                  . .057562898844480515                   .  .06302232295274735                  .                   . .12058522  .06029261
"00208J10" 112 .13075272738933563 .20024779811501503  .28673679381608963 .27169156819581985 .18069465458393097 .14434372633695602  .05209270119667053                   .                   .                  .                   .   1.26656  .18093714
"00209A10" 131                  .                  .                   .                  .                  .                  .                   .                   .  .08855769038200378                  .                   .  .0885577   .0885577
"00209H10" 103                  .                  .                   .                  .                  .                  . .056290511041879654 .057795923203229904  .05745059624314308 .08463238179683685                   .  .2561694  .06404235
"00211E10" 108  .3058236613869667 .21075893938541412                   .                  .                  .                  .                   .                   .                   .                  .                   .  .5165826   .2582913
"00211V10" 100 .11450185626745224 .06141842156648636   .0616530179977417 .06707769632339478                  .                  .                   .                   .                   .                  .                   .   .304651  .07616275
"00211W10" 104                  .                  .   .3510790131986141   .289696779102087                  .                  .                   .                   .                   .                  .                   .  .6407758   .3203879
"00211Y10" 116                  .                  . .054167237132787704                  .                  .                  .                   .                   .                   .                  .                   . .05416724  .05416724
"00213T10" 124 .28290779143571854                  .                   .                  .                  .                  .                   .                   .                   .                  .                   . .28290778  .28290778
"00234610" 116 .06076706945896149 .05726949870586395  .24471966549754143                  .                  .                  .                   .                   .   .0513819120824337                  .                   .  .4141381  .10353453
"00244410" 130 .07137610763311386 .05177071690559387  .07595158368349075                  .                  .  .1031266450881958  .10146573185920715                   .  .07043175399303436  .0654558315873146                   .  .5395784  .07708263
"00246W10" 118 .24203500151634216 .18778974935412407  .14974644035100937 .17001963406801224 .14952390640974045 .16994599997997284   .1911482959985733  .19112436473369598   .1406393051147461 .18951864540576935                   . 1.7814913  .17814913
"00247410" 117 .31833815574645996 .30553488805890083  .36456454172730446 .17524703592061996  .2415109947323799 .22607554495334625   .4440891444683075  .35122718289494514   .2908710613846779 .37059780955314636   .2992136552929878   3.38727   .3079336
"00252W10" 100                  .                  .                   .                  .                  .                  .                   .                   .   .3146659880876541   .511942233890295  .15913812816143036  .9857463   .3285821
"00253520" 117                  .                  .  .35951733216643333  .2201980985701084 .36433522775769234  .2344447746872902  .19529768824577332  .23394019901752472  .09446387737989426  .2310609370470047  .21722110360860825 2.1504793  .23894213
"00253530" 117 .08734682202339172 .13530506938695908  .18475867062807083 .17682477831840515 .17673927545547485 .17905884236097336  .19837160408496857  .18016315251588821  .17978741973638535 .17987674474716187  .16362924501299858 1.8418617  .16744196
"00253A30" 105                  .                  .                   .                  .                  .                  .                   .                   .   .1782325655221939  .0926910862326622                   . .27092364  .13546182
"00253G10" 120                  .                  .                   .                  .                  .                  .   .2972639910876751  .43511873483657837                   .                  .                   .  .7323827   .3661914
"00256410" 118                  .                  .                   .                  .                  .                  .                   . .059811804443597794                   .                  .                   .  .0598118   .0598118
"00256710" 116  .5609782747924328   .396969448775053   .4609239473938942  .4703131653368473 .29444318264722824 .28287216648459435  .16991473734378815  .09870688617229462                   .                  .                   .  2.735122   .3418902
"00289310" 118                  .                  .                   .                  .                  . .16557276993989944   .1444535031914711  .14112552627921104   .1261809840798378   .071675606071949                   .  .6490084  .12980168
"00289620" 132  .2677963934838772 .17489458620548248  .21562758833169937  .2664298117160797 .16160546243190765 .18929845094680786   .2520131319761276                   .   .1250208504498005 .06251450628042221  .15173747390508652 1.8669384  .18669383
"00292070" 105                  .                  .                   .                  .                  .                  .  .06959444284439087                   .                   .                  .                   . .06959444  .06959444
"00292080" 105                  .                  .                   .                  .                  . .12130999937653542  .13506000116467476                   .                   .                  .                   .    .25637    .128185
"00301110" 120                  .                  .                   .                  .                  .                  .  .09527493268251419                   .                   .                  .                   . .09527493  .09527493
"00301310" 120                  .                  .                   .                  .                  .                  .                   .                   .                   .                  .  .10368594527244568 .10368595  .10368595
"00301L10" 120 .07476597279310226                  .                   .                  .                  .                  .                   .                   .                   .                  .                   . .07476597  .07476597
"00301T10" 120  .5899973511695862  .5716321170330048                   .                  .                  .                  .                   .                   .                   .                  .                   . 1.1616294   .5808147
"00305P10" 120 .24490812420845032 .13362125307321548                   .                  .                  .                  .                   .                   .                   .                  .                   .  .3785294   .1892647
"00306K10" 120  .3785855919122696  .4086820036172867                   .                  .                  .                  .                   .                   .                   .                  .                   .  .7872676   .3936338
"00339010" 105                  .                  .                   .                  .                  .                  .  .07091276347637177  .06024676561355591                   .                  .                   . .13115953 .065579765
"00339B10" 116                  .                  .                   .                  .                  .                  .   .2822223901748657  .29032324999570847  .21051092445850372 .18800681829452515  .19309575855731964 1.1641592  .23283184
"00350L10" 105                  . .11963292956352234  .11949818581342697 .09129088371992111 .17412736266851425                  .                   .                   .                   .                  .                   .  .5045494  .12613735
"00350P10" 105                  .                  .                   .                  .                  .                  .                   .                   .  .06918302178382874 .06970874965190888  .05981999263167381 .19871177 .066237256
"00365410" 112 .37427421659231186 .33406421914696693  .32147902250289917 .21027255058288574 .08630549907684326                  .                   .                   .                   .                  .                   . 1.3263955   .2652791
"00368710" 126                  .                  .                   .  .1610715053975582  .4633748307824135                  .                   .                   .                   .                  .                   .  .6244463   .3122232
"00368720" 118 .39438607543706894 .28884515538811684                   .                  .                  .                  .                   .                   .                   .                  .                   .  .6832312   .3416156
"00371F20" 108                  .                  .                   .                  .                  .                  .                   .                   .                   . .18326181173324585  .14216016232967377   .325422    .162711
"00371N40" 116                  .                  .                   .                  .                  .                  .                   .  .34883875399827957  .05691923573613167                  .                   .   .405758    .202879
end

[Joseph Coveney]

You can get a count of the number of variables with missing values on each row using egen with its rowmiss() fcn. You can then divide that tally by the number of variables that you're monitoring and then drop those rows with a value that is more than 50%.

At Stata's command line, type

help egen

for more information.

[Carlo Lazzaro]

Geert:
elaborating a bit on Joseph's helpful advice, you may want to try:

. egen flag=rownonmiss(indcode perc* BHOLDER_INSTTOTAL BHOLDER_INST)

. egen flag_2=rowmiss(indcode perc* BHOLDER_INSTTOTAL BHOLDER_INST)

. g check=flag_2/( flag+flag_2)

. drop if check>.5

[Nick Cox]

In wanting to know the fraction of missing values in each observations, it may help to know that c(k) holds the number of variables in the dataset in memory.

Note also In the same vein, missings (Stata Journal) is one of several tools providing information on missings.

See http://www.statalist.org/forums/foru...aging-missings