Display and drop observations by variable

[Ana Vasconcelos]

Hello,

I have a dataset with several variables (I send an example below). I wouldlike to know the commands to:

1 - display the frequency of observations "inapplicable" by variable (for al variables in the dataset)
2 - Drop all the variables that have more than 400 observations with "inapplicable"

Thank you very much

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(childpno nch14resp nch3resp nch8resp)
-8 2 1 0
-8 0 0 0
-8 0 0 0
-8 3 0 0
-8 0 0 0
-8 0 0 0
-8 1 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 2 1 0
-8 1 0 0
-8 1 0 0
-8 0 0 0
-8 1 0 0
-8 0 0 0
-8 0 0 0
-8 1 0 0
 4 2 0 0
-8 1 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 2 0 1
-8 1 0 0
-8 2 0 0
-8 0 0 0
-8 3 0 0
-8 3 1 0
-8 2 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 1 0 0
-8 0 0 0
-8 1 1 0
-8 2 0 0
-8 2 1 0
-8 2 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 3 0 0
-8 0 0 0
-8 0 0 0
-8 3 0 0
-8 0 0 0
-8 1 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 0 0 0
-8 3 1 0
-8 2 0 0
-8 0 0 0
 3 0 0 0
-8 2 0 0
-8 0 0 0
-8 0 0 0
-8 3 0 0
-8 0 0 0
-8 0 0 0
-8 2 0 0
-8 0 0 0
-8 2 0 0
-8 0 0 0
-8 1 0 0
-8 1 0 0
-8 0 0 0
-8 1 1 0
-8 0 0 0
-8 1 0 0
-8 1 1 0
-8 0 0 0
-8 0 0 0
-8 1 0 0
-8 0 0 0
-8 0 0 0
-8 1 0 0
-8 1 0 0
-8 0 0 0
-8 1 0 0
end
label values childpno c_childpno
label def c_childpno -8 "inapplicable", modify
label values nch14resp c_nch14resp
label values nch3resp c_nch3resp
label values nch8resp c_nch8resp

[Hemanshu Kumar]

Sorry, I don't think I understand. Since "inapplicable" is a characteristic of the observation (or the childpno) rather than any of these variables, will the frequency of "inapplicable" not be the same for all variables?

[Nick Cox]

Search the forum for mention of countvalues from SSC.

[Clyde Schechter]

On the assumption that all of the relevant variables have value labels and that the value label you are interested in is always exactly "inapplicable", the following will do it:

foreach v of varlist _all {
    display _newline(3)  `"`v'"'
    local lbl: value label `v'
    count if `v' == "inapplicable":`lbl'
    if r(N) > 400 {
        drop `v'
    }
}

Added: Crossed with #2 and #3.

[Ana Vasconcelos]

It´s not working. It appears the following meassge:
pidp
invalid syntax
r(198);

end of do-file

r(198);

It may be because I also have numeric variables.
I send again an example of the dataset with more variables. The goal is to know the number of observations where it is writen "inapplicable" by each variable in the sample. After that I would like to drop the varibales that have more than 400 observations with the information "inapplicable"
Thank you very much.

* Example generated by -dataex-. For more info, type help dataex
clear
input long(pidp hidp) byte(memorig childpno nch14resp nch3resp nch8resp)
  68513407   71114404 1 -8 2 1 0
  69394015   75595604 7 -8 0 0 0
  69406247   75629604 7 -8 0 0 0
  69451131   75711204 7 -8 3 0 0
  69854367   76098804 7 -8 0 0 0
 136376727  138264404 1 -8 0 0 0
 136591607  139468004 1 -8 1 0 0
 136591615  139468004 1 -8 0 0 0
 137389927  143949204 7 -8 0 0 0
 137436167  143996804 7 -8 0 0 0
 137779567  144350404 7 -8 0 0 0
 204068691  204462404 1 -8 2 1 0
 204595687  207454404 1 -8 1 0 0
 204595691  207454404 1 -8 1 0 0
 205007775  209712004 1 -8 0 0 0
 205420535  211724804 7 -8 1 0 0
 205604807  211949204 7 -8 0 0 0
 205786367  212119204 7 -8 0 0 0
 205958407  212398004 7 -8 1 0 0
 206224967  212554404 7  4 2 0 0
 272165931  272979204 1 -8 1 0 0
 340061887  340360404 1 -8 0 0 0
 340434527  342550004 1 -8 0 0 0
 341325331  347330404 1 -8 0 0 0
 341419847  347514004 7 -8 0 0 0
 341537487  347629604 7 -8 0 0 0
 341691167  347888004 7 -8 0 0 0
 341764607  347949204 7 -8 0 0 0
 342008727  348214404 7 -8 0 0 0
 342014847  348228004 7 -8 0 0 0
 342222247  348357204 7 -8 0 0 0
 342222251  348357204 7 -8 0 0 0
 342222259  348357204 7 -8 0 0 0
 408041487  408292404 1 -8 0 0 0
 408205367  409251204 1 -8 0 0 0
 409638807  415854004 7 -8 0 0 0
 409653091  415874404 7 -8 0 0 0
 409693207  415922004 7 -8 2 0 1
 409733327  415996804 7 -8 1 0 0
 409779571  416030804 7 -8 2 0 0
 409847567  416132804 7 -8 0 0 0
 409897211  416200804 7 -8 3 0 0
 410126371  416493204 7 -8 3 1 0
 410131127  416500004 7 -8 2 0 0
 410230407  416568004 7 -8 0 0 0
 476000691  476006804 1 -8 0 0 0
 478404487  484275604 7 -8 0 0 0
 545284531  551337204 1 -8 0 0 0
 545532047  551854004 7 -8 0 0 0
 545532055  551854004 7 -8 1 0 0
 545781607  551996804 7 -8 0 0 0
 546314047  552595204 7 -8 1 1 0
 546524847  552690404 7 -8 2 0 0
 546761499  552942004 7 -8 2 1 0
 546766931  552962404 7 -8 2 0 0
 612193131  613292004 1 -8 0 0 0
 612621527  616059604 1 -8 0 0 0
 612677971  616433604 1 -8 0 0 0
 613438887  620194004 7 -8 0 0 0
 613518455  620234804 7 -8 0 0 0
 613783651  620479604 7 -8 3 0 0
 613806087  620520404 7 -8 0 0 0
 614375931  620982804 7 -8 0 0 0
 680330491  681917604 1 -8 3 0 0
 680954047  685202004 1 -8 0 0 0
 680962211  685276804 1 -8 1 0 0
 681007099  685542004 1 -8 0 0 0
 681551087  687554804 7 -8 0 0 0
 681585767  687561604 7 -8 0 0 0
 681717687  687772404 7 -8 0 0 0
 681717691  687772404 7 -8 3 1 0
 681792487  687847204 7 -8 2 0 0
 748594327  751481604 1 -8 0 0 0
 749419847  755656804 7  3 0 0 0
 750244007  756336804 7 -8 2 0 0
 750505807  756534004 7 -8 0 0 0
 817670087  824071604 7 -8 0 0 0
 817992411  824642804 7 -8 3 0 0
 884224415  885197484 1 -8 0 0 0
 884536527  886896804 1 -8 0 0 0
 884616767  887325204 1 -8 2 0 0
 885250527  890582404 1 -8 0 0 0
 885392651  891051604 7 -8 2 0 0
 885861167  891650004 7 -8 0 0 0
 886011447  891826804 7 -8 1 0 0
 886012127  891833604 7 -8 1 0 0
 886305207  892166804 7 -8 0 0 0
 886346691  892200804 7 -8 1 1 0
 886428967  892289204 7 -8 0 0 0
 886558847  892384404 7 -8 1 0 0
 952242767  953455204 1 -8 1 1 0
 953100931  958018004 1 -8 0 0 0
 953649019  959643204 7 -8 0 0 0
 953905367  959962804 7 -8 1 0 0
 954174651  960377604 7 -8 0 0 0
 954463647  960608804 7 -8 0 0 0
1022476567 1028574804 7 -8 1 0 0
1089592567 1095439204 7 -8 1 0 0
1089592571 1095439204 7 -8 0 0 0
1089644247 1095507204 7 -8 1 0 0
end
label values memorig c_memorig
label def c_memorig 1 "ukhls gb 2009-10", modify
label def c_memorig 7 "ukhls emboost 2009-10", modify
label values childpno c_childpno
label def c_childpno -8 "inapplicable", modify
label values nch14resp c_nch14resp
label values nch3resp c_nch3resp
label values nch8resp c_nch8resp

[Hemanshu Kumar]

The problem is that Clyde's code expects a value label with each variable, while you have some variables, like pidp, that have no value labels attached.

The solution depends on the structure of your variables. For instance, if -8 always represents "inapplicable" across your variables, the simplest modification to Clyde's code is just:

foreach v of varlist _all {    
    display _newline(3)  `"`v'"'
    count if `v' == -8
    if r(N) > 400 {
        drop `v'
    }
}

On the other hand, if you want to do this only for variables that have a value label, you could do this:

ds , has(vallabel)
local vars `r(varlist)'
foreach v of local vars {
    display _newline(3)  `"`v'"'
    local lbl: value label `v'
    count if `v' == "inapplicable":`lbl'
    if r(N) > 400 {
        drop `v'
    }
}

[Ana Vasconcelos]

Both codes worked perfectly. Thank you very much. One more question:
When I run the code below, how can I keep the variable "jbft_dv" in the dataset? I need to keep this variables although it has observations where is it writen "inapplicable.
I send the example of the dataset below where I add this last variable.
Thank you very much.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(jbft_dv memorig childpno nch14resp nch3resp nch8resp)
 2 1 -8 2 1 0
-8 7 -8 0 0 0
-8 7 -8 0 0 0
-8 7 -8 3 0 0
-8 7 -8 0 0 0
 1 1 -8 0 0 0
-8 1 -8 1 0 0
 2 1 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 0 0 0
 1 7 -8 0 0 0
 2 1 -8 2 1 0
 1 1 -8 1 0 0
 1 1 -8 1 0 0
-8 1 -8 0 0 0
 1 7 -8 1 0 0
-8 7 -8 0 0 0
-8 7 -8 0 0 0
 2 7 -8 1 0 0
 2 7  4 2 0 0
-8 1 -8 1 0 0
 1 1 -8 0 0 0
-8 1 -8 0 0 0
 2 1 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 0 0 0
-8 7 -8 0 0 0
-8 7 -8 0 0 0
-8 1 -8 0 0 0
 1 1 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 2 0 1
 2 7 -8 1 0 0
-8 7 -8 2 0 0
-8 7 -8 0 0 0
-8 7 -8 3 0 0
 1 7 -8 3 1 0
-8 7 -8 2 0 0
-8 7 -8 0 0 0
 1 1 -8 0 0 0
-8 7 -8 0 0 0
-8 1 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 1 0 0
-8 7 -8 0 0 0
 2 7 -8 1 1 0
 1 7 -8 2 0 0
-8 7 -8 2 1 0
-8 7 -8 2 0 0
-8 1 -8 0 0 0
 1 1 -8 0 0 0
-8 1 -8 0 0 0
 1 7 -8 0 0 0
 2 7 -8 0 0 0
-8 7 -8 3 0 0
-8 7 -8 0 0 0
-8 7 -8 0 0 0
 2 1 -8 3 0 0
-8 1 -8 0 0 0
 1 1 -8 1 0 0
 1 1 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 0 0 0
 1 7 -8 0 0 0
-8 7 -8 3 1 0
 2 7 -8 2 0 0
-8 1 -8 0 0 0
-8 7  3 0 0 0
 2 7 -8 2 0 0
 1 7 -8 0 0 0
-8 7 -8 0 0 0
-8 7 -8 3 0 0
-8 1 -8 0 0 0
 1 1 -8 0 0 0
-8 1 -8 2 0 0
 1 1 -8 0 0 0
-8 7 -8 2 0 0
-8 7 -8 0 0 0
 1 7 -8 1 0 0
-8 7 -8 1 0 0
 1 7 -8 0 0 0
 1 7 -8 1 1 0
-8 7 -8 0 0 0
 1 7 -8 1 0 0
 1 1 -8 1 1 0
-8 1 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 1 0 0
-8 7 -8 0 0 0
 1 7 -8 0 0 0
 1 7 -8 1 0 0
-8 7 -8 1 0 0
 1 7 -8 0 0 0
-8 7 -8 1 0 0
end
label values jbft_dv c_jbft_dv
label def c_jbft_dv -8 "inapplicable", modify
label def c_jbft_dv 1 "FT employee", modify
label def c_jbft_dv 2 "PT employee", modify
label values memorig c_memorig
label def c_memorig 1 "ukhls gb 2009-10", modify
label def c_memorig 7 "ukhls emboost 2009-10", modify
label values childpno c_childpno
label def c_childpno -8 "inapplicable", modify
label values nch14resp c_nch14resp
label values nch3resp c_nch3resp
label values nch8resp c_nch8resp

[Hemanshu Kumar]

Consider something like this:

local excludevars jbft_dv
ds, has(vallabel)
local vars `r(varlist)'
local vars: list vars - excludevars

foreach v of local vars {
...

You can list more variables you want to exclude in the local excludevars.

[Ana Vasconcelos]

I don´t understand..Suppose that I want to exclude the variable "jbft_dv" and also "qfhigh_dv". How can I run de code?
I tried the following but it didn´t work (now I consider that I want to drop of the variables with observations written "inapplicable".
I also send the code below whre I add the variable "qfhigh_dv".
Code that I tried to run:

local excludevars jbft_dvds , has(vallabel)
local vars `r(varlist)'
local vars: list vars - excludevars qfhigh_dv
foreach v of local vars {
display _newline(3) `"`v'"'
local lbl: value label `v'
count if `v' == "inapplicable":`lbl'
if r(N) > 400 {
drop `v'
}
}

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(jbft_dv qfhigh_dv memorig childpno nch14resp nch3resp nch8resp)
 2 13 1 -8 2 1 0
-8  6 7 -8 0 0 0
-8  2 7 -8 0 0 0
-8 16 7 -8 3 0 0
-8  7 7 -8 0 0 0
 1 96 1 -8 0 0 0
-8  3 1 -8 1 0 0
 2 13 1 -8 0 0 0
 1  3 7 -8 0 0 0
-8 96 7 -8 0 0 0
 1  1 7 -8 0 0 0
 2  3 1 -8 2 1 0
 1  5 1 -8 1 0 0
 1 96 1 -8 1 0 0
-8  3 1 -8 0 0 0
 1 96 7 -8 1 0 0
-8  7 7 -8 0 0 0
-8 96 7 -8 0 0 0
 2 14 7 -8 1 0 0
 2  3 7  4 2 0 0
-8  2 1 -8 1 0 0
 1 14 1 -8 0 0 0
-8 14 1 -8 0 0 0
 2  1 1 -8 0 0 0
 1  5 7 -8 0 0 0
 1  1 7 -8 0 0 0
 1  1 7 -8 0 0 0
 1  2 7 -8 0 0 0
-8 96 7 -8 0 0 0
 1  1 7 -8 0 0 0
-8  5 7 -8 0 0 0
-8 14 7 -8 0 0 0
-8 96 7 -8 0 0 0
-8  7 1 -8 0 0 0
 1  2 1 -8 0 0 0
 1 13 7 -8 0 0 0
 1  1 7 -8 0 0 0
-8  2 7 -8 2 0 1
 2 96 7 -8 1 0 0
-8 96 7 -8 2 0 0
-8 96 7 -8 0 0 0
-8 16 7 -8 3 0 0
 1  5 7 -8 3 1 0
-8 96 7 -8 2 0 0
-8  7 7 -8 0 0 0
 1  7 1 -8 0 0 0
-8 96 7 -8 0 0 0
-8  5 1 -8 0 0 0
 1  7 7 -8 0 0 0
 1  7 7 -8 1 0 0
-8  2 7 -8 0 0 0
 2  1 7 -8 1 1 0
 1 96 7 -8 2 0 0
-8  1 7 -8 2 1 0
-8  3 7 -8 2 0 0
-8  4 1 -8 0 0 0
 1  5 1 -8 0 0 0
-8 13 1 -8 0 0 0
 1  2 7 -8 0 0 0
 2  7 7 -8 0 0 0
-8 16 7 -8 3 0 0
-8  3 7 -8 0 0 0
-8 96 7 -8 0 0 0
 2  2 1 -8 3 0 0
-8 96 1 -8 0 0 0
 1  3 1 -8 1 0 0
 1  7 1 -8 0 0 0
 1 15 7 -8 0 0 0
-8 96 7 -8 0 0 0
 1  3 7 -8 0 0 0
-8  2 7 -8 3 1 0
 2  3 7 -8 2 0 0
-8 96 1 -8 0 0 0
-8 96 7  3 0 0 0
 2  1 7 -8 2 0 0
 1  2 7 -8 0 0 0
-8 96 7 -8 0 0 0
-8 13 7 -8 3 0 0
-8 96 1 -8 0 0 0
 1  1 1 -8 0 0 0
-8 10 1 -8 2 0 0
 1  2 1 -8 0 0 0
-8 13 7 -8 2 0 0
-8 13 7 -8 0 0 0
 1  3 7 -8 1 0 0
-8 96 7 -8 1 0 0
 1 13 7 -8 0 0 0
 1  7 7 -8 1 1 0
-8  3 7 -8 0 0 0
 1  2 7 -8 1 0 0
 1  2 1 -8 1 1 0
-8 96 1 -8 0 0 0
 1  7 7 -8 0 0 0
 1 13 7 -8 1 0 0
-8  1 7 -8 0 0 0
 1  7 7 -8 0 0 0
 1 13 7 -8 1 0 0
-8  2 7 -8 1 0 0
 1  2 7 -8 0 0 0
-8  2 7 -8 1 0 0
end
label values jbft_dv c_jbft_dv
label def c_jbft_dv -8 "inapplicable", modify
label def c_jbft_dv 1 "FT employee", modify
label def c_jbft_dv 2 "PT employee", modify
label values qfhigh_dv c_qfhigh_dv
label def c_qfhigh_dv 1 "Higher degree", modify
label def c_qfhigh_dv 2 "1st degree or equivalent", modify
label def c_qfhigh_dv 3 "Diploma in he", modify
label def c_qfhigh_dv 4 "Teaching qual not pgce", modify
label def c_qfhigh_dv 5 "Nursing/other med qual", modify
label def c_qfhigh_dv 6 "Other higher degree", modify
label def c_qfhigh_dv 7 "A level", modify
label def c_qfhigh_dv 10 "AS level", modify
label def c_qfhigh_dv 13 "GCSE/O level", modify
label def c_qfhigh_dv 14 "CSE", modify
label def c_qfhigh_dv 15 "Standard/o/lower", modify
label def c_qfhigh_dv 16 "Other school cert", modify
label def c_qfhigh_dv 96 "None of the above", modify
label values memorig c_memorig
label def c_memorig 1 "ukhls gb 2009-10", modify
label def c_memorig 7 "ukhls emboost 2009-10", modify
label values childpno c_childpno
label def c_childpno -8 "inapplicable", modify
label values nch14resp c_nch14resp
label values nch3resp c_nch3resp
label values nch8resp c_nch8resp

[Hemanshu Kumar]

local excludevars jbft_dv qfhigh_dv
ds, has(vallabel)
local vars `r(varlist)'
local vars: list vars - excludevars

foreach v of local vars {
...

Also, you might want to look at

help macro
help macrolists
help ds

to understand some of the techniques being employed here.

[Ana Vasconcelos]

Thank you very much. It worked. I´m going to look at the codes to understand the techniques employed