Counting observations meeting multiple conditions

[Andreas Denzer]

Dear member of this forum,

I’m using Stata 13.1 / SE on Windows 10 for analyzing some behavior of unemployed persons.
In my used dataset, I observe the persons choice of different job search channels. My observed persons are identified by a Person-ID and the choice of each used channel is identified by a dummy. Moreover, I have already created a variable indicating how many search channel the persons choose in total.


Thus, my dataset looks like the following:

Person-ID	channel 1	channel 2	channel 3	channel 4	channel 5	channel 6	channel 7	channel 8	channel 9	channel 10	Number of used channels
1001	1	1	1	0	0	0	0	0	0	0	3
5412	0	0	0	0	1	0	0	0	0	0	1
4816	1	1	0	0	0	0	0	0	0	1	3
1271	1	1	1	1	1	0	0	0	0	0	5
1100	1	1	1	1	1	1	1	1	1	1	10

My goal is to report the quantity of the different combinations used by the observed persons per number of used channels in total, i.e. I want to report how often my observed people choose a combination of search channels if they decide for one, two, three .... ten used channels in total.

For one channel used in total there are obviously ₁₀C₁ = 10 possible "combinations".
For two channels used in total: ₁₀C₂ = 45 , for instance {channel1 & channel2} , {channel1 & channel3}, {channel1 & channel4} ... {channel9 & channel10}
For three channels used in total: ₁₀C₃ = 120
For four channels used in total: ₁₀C₄ = 210
For five channels used in total: ₁₀C₅ = 252
For six channels used in total: ₁₀C₆ = 210
....
....
....
For ten channels used in total ₁₀C₁₀ = 1

I tried to solve this issue with looping and the command count, because I could not find any hint for a suitable command or algorithm here and on other boards. However when I realized the huge amount of different combinations, I hoped that maybe one of you clever guys has a better solution?


For the combination of two used channels in total, I used the following:

foreach i in channel1 channel2 channel3 channel4 channel5 channel6 channel7 channel8 channel9 channel10 {

    count if `i' == 1 & channel1 == 1 & number_channels_unempl == 2
   
    count if `i' == 1 & channel2== 1 & number_channels_unempl == 2

    count if `i' == 1 & channel3 == 1 & number_channels_unempl == 2

    count if `i' == 1 & channel4 == 1 & number_channels_unempl == 2

    count if `i' == 1 & channel5 == 1 & number_channels_unempl == 2

    count if `i' == 1 & channel6 == 1 & number_channels_unempl == 2
   
    count if `i' == 1 & channel7== 1 & number_channels_unempl == 2

    count if `i' == 1 & channel8 == 1 & number_channels_unempl == 2

    count if `i' == 1 & channel9 == 1 & number_channels_unempl == 2
   
    count if `i' == 1 & channel10 == 1 & number_channels_unempl == 2

    }

This is the result for one and two channels combined in one table:

For the combinations of three channels in total, I took the loop from above, added one channel condition per count command, copied it eight times and manipulated it by hand.
e.g. for one command:

count if `i' == 1 & channel1 == 1 & channel3 == 1 & number_channels_unempl == 3

This is the result:

Since its becoming really difficult and time consuming to specify all the conditions "per hand", I wanted to give a try in this forum.

Thanks in advance!

Bests
Andreas

[Mike Lacy]

I followed your description up to where you said "I want to report how often my observed people choose a combination of search channels if they decide for one, two, three .... ten used channels in total."

Taking that as your goal, here's my approach to the problem:

// Create example data
clear
set obs 100
gen byte totalused = 0
forval i = 1/10 {
    local threshold = runiform()  
    gen channel`i' = (runiform() > `threshold')
    qui replace totalused = totalused + channel`i'
}
//  Each combination of 0s and 1s corresponds to a distinct binary string.
gen str10 combstring = ""
forval i = 1/10 {
   qui replace combstring = cond(channel`i' == 1, combstring + "1", combstring + "0")
}
//
//  For each distinct observed value of the total number of channels used, display a table of the observed
// combinations and report the number of such that occurred.
levelsof totalused, local(total)
foreach t of local total  {
     tab combstring if (totalused == `t')
   di "Total used = `t',  distinct combinations = " r(r)
   di "-------------------"
}

Hope this is what you wanted.

[Andreas Denzer]

Dear Mike,

first of all, thank you very much for your help. I really appreciate the idea of combstring, which I have never seen / used before.

According to your example data/code:
1) The return function of the tabulate command just gives the number of different used combinations and the total observed frequency. However, I'm interested in the frequency per channel and that is not stored by the return function to my knowledge.
2) I'm interested in all possible combinations of the search channels per number of total used channels and unfortunately not only the actually used ones. tabulate just gives me the latter ones.

In terms of clarification: My goal is to have a command or algorithm, that (1) Stata recognizes all different possible combinations of search channels per number of total channels used, (2) reports those frequencies and stores them (non-observed combinations in the dataset should have the frequency zero) and (3) output those results automatically.

I guess I need some multiple loop algorithm or something similar...

A similar result to the tables in my posted pictures would be the "first best" solution or paradise

Thanks for your help in advance!

Bests
Andreas

[Nick Cox]

Here's some technique, but first

ssc inst tuples 
help tuples

Some basic stuff:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int personid byte(channel1 channel2 channel3 channel4 channel5 channel6 channel7 channel8 channel9 channel10 numberofusedchannels)
1001 1 1 1 0 0 0 0 0 0 0  3
5412 0 0 0 0 1 0 0 0 0 0  1
4816 1 1 0 0 0 0 0 0 0 1  3
1271 1 1 1 1 1 0 0 0 0 0  5
1100 1 1 1 1 1 1 1 1 1 1 10
end

egen nused = rowtotal(channel*)

gen allused = ""

quietly forval j = 1/10 {
      replace allused = allused + "`j' " if channel`j'
}
replace allused = trim(allused)

egen concat = concat(channel*)

ssc inst groups

groups nused allused concat

  +-------------------------------------------------------------+
  | nused                allused       concat   Freq.   Percent |
  |-------------------------------------------------------------|
  |     1                      5   0000100000       1     20.00 |
  |     3                 1 2 10   1100000001       1     20.00 |
  |     3                  1 2 3   1110000000       1     20.00 |
  |     5              1 2 3 4 5   1111100000       1     20.00 |
  |    10   1 2 3 4 5 6 7 8 9 10   1111111111       1     20.00 |
  +-------------------------------------------------------------+

.

[Robert Picard]

Maybe I'm missing something but this is a simple exercise in grouping observations.

clear
set seed 123456
set obs 10000
gen long id = _n
forval i = 1/10 {
    local threshold = runiform()  
    gen channel`i' = (runiform() > `threshold')
}
egen nchannels = rowtotal(channel*)
tab nchannels

* the number of persons using the same combination
bysort nchannels channel*: gen ncases = _N
by nchannels channel*: keep if _n == 1

* the number of different combinations of channels observed
by nchannels: gen ncombo = _N
by nchannels: egen ncasestot = total(ncases)

* as a proportion of the total numbers of combinations possible
gen ncomboprop = ncombo / comb(10,nchannels)

* if desired, reduce to one obs per number of channels
by nchannels: keep if _n == 1
keep n*
list

[Andreas Denzer]

Dear Nick,
Dear Robert,

thank your very much for your replies and suggestions!

With the aid of the proposed tuples command I solved the problem.

Here is my code for those interested. It may be look a little complicated and there may be a more sophisticated solution, but it does it's work:

The code for getting the number of observations per tulple/combination.

preserve

*Generate duplicates of dummies with less length
gen m0 = channel1 // Method 1
gen m1 = channel2 // Method 2
gen m2 = channel3 // Method 3
gen m3 = channel4 // Method 4
gen m4 = channel5 // Method 5
gen m5 = channel6 // Method 6
gen m6 = channel7 // Method 7
gen m7 = channel8 // Method 8
gen m8 = channel9 // Method 9
gen m9 = channel10 // Method 10


*Generate tuples / combinations of the ten different methods
tuples m0 m1 m2 m3 m4 m5 m6 m7 m8 m9

gen channels = "" // Generate string variable displaying the tuple / combination
gen obs =. // Generate variable of number per observation per combination
gen nused = . // Generate variable of number of search methods used for each cobination (=^ number_methods_unempl)

*Fill in the information of the tuples / combinations
qui forval i = 1/`ntuples' {
       replace channels = "`tuple`i''" in `i'
       replace nused = wordcount(channels) if channels != ""
}

*Use substrings to split up the method combination
gen sub0 = substr(channels,1,2)
gen sub1 = substr(channels,4,2)
gen sub2 = substr(channels,7,2)
gen sub3 = substr(channels,10,2)
gen sub4 = substr(channels,13,2)
gen sub5 = substr(channels,16,2)
gen sub6 = substr(channels,19,2)
gen sub7 = substr(channels,22,2)
gen sub8 = substr(channels,25,2)
gen sub9 = substr(channels,28,2)

*Generate dummies indicating if methods are part of the tuple / combination
gen M0 = 1 if sub0 == "m0"
replace M0= 0 if sub0 != "m0"

gen M1 = 1 if sub0 == "m1" | sub1  == "m1"
replace M1 = 0 if sub0 != "m1" & sub1 != "m1"

gen M2 = 1 if sub0 == "m2" | sub1  == "m2" | sub2  == "m2"
replace M2 = 0 if sub0 != "m2" & sub1 != "m2" & sub2 != "m2"

gen M3 = 1 if sub0 == "m3" | sub1  == "m3" | sub2  == "m3" | sub3  == "m3"
replace M3 = 0 if sub0 != "m3" & sub1 != "m3" & sub2 != "m3" & sub3 != "m3"

gen M4 = 1 if sub0 == "m4" | sub1  == "m4" | sub2  == "m4" | sub3  == "m4" | sub4 == "m4"
replace M4 = 0 if sub0 != "m4" & sub1 != "m4" & sub2 != "m4" & sub3 != "m4" & sub4 != "m4"

gen M5 = 1 if sub0 == "m5" | sub1  == "m5" | sub2  == "m5" | sub3  == "m5" | sub4 == "m5" | sub5 == "m5"
replace M5 = 0 if sub0 != "m5" & sub1 != "m5" & sub2 != "m5" & sub3 != "m5" & sub4 != "m5" & sub5 != "m5"

gen M6 = 1 if sub0 == "m6" | sub1  == "m6" | sub2  == "m6" | sub3  == "m6" | sub4 == "m6" | sub5 == "m6" | sub6 == "m6"
replace M6 = 0 if sub0 != "m6" & sub1 != "m6" & sub2 != "m6" & sub3 != "m6" & sub4 != "m6" & sub5 != "m6" & sub6 != "m6"

gen M7 = 1 if sub0 == "m7" | sub1  == "m7" | sub2  == "m7" | sub3  == "m7" | sub4 == "m7" | sub5 == "m7" | sub6 == "m7" | sub7 =="m7"
replace M7 = 0 if sub0 != "m7" & sub1 != "m7" & sub2 != "m7" & sub3 != "m7" & sub4 != "m7" & sub5 != "m7" & sub6 != "m7" & sub7 != "m7"

gen M8 = 1 if sub0 == "m8" | sub1  == "m8" | sub2  == "m8" | sub3  == "m8" | sub4 == "m8" | sub5 == "m8" | sub6 == "m8" | sub7 =="m8" | sub8 == "m8"
replace M8 = 0 if sub0 != "m8" & sub1 != "m8" & sub2 != "m8" & sub3 != "m8" & sub4 != "m8" & sub5 != "m8" & sub6 != "m8" & sub7 != "m8" & sub8 != "m8"

gen M9 = 1 if sub0 == "m9" | sub1 == "m9" | sub2  == "m9" | sub3  == "m9" | sub4  == "m9" | sub5 == "m9" | sub6 == "m9" | sub7 == "m9" | sub8 =="m9" | sub9 == "m9"
replace M9 = 0 if sub0 != "m9" & sub1 != "m9" & sub2 != "m9" & sub3 != "m9" & sub4 != "m9" & sub5 != "m9" & sub6 != "m9" & sub7 != "m9" & sub8 != "m9" & sub9 != "m9"


*Fill in the information of observation per tuple / combination --- ! ONLY for observations of the year 2005
forval i = 1/`ntuples' {
       qui count if syear == 2005 & m0 == M0[`i'] & m1 == M1[`i'] & m2 == M2[`i'] & m3 == M3[`i'] & m4 == M4[`i'] & m5 == M5[`i'] & m6 == M6[`i'] & m7 == M7[`i'] & m8 == M8[`i'] & m9 == M9[`i'] & number_methods_unempl == nused[`i']      
       replace obs = r(N) in `i'
}      


sort nused
*list nused channels sub0 M0 sub1 M1 sub2 M2 sub3 M3 sub4 M4 sub5 M5 sub6 M6 sub7 M7 sub8 M8 sub9 M9 obs in 1/`ntuples'
*list nused channels sub0 M0 sub1 M1 sub2 M2 sub3 M3 sub4 M4 sub5 M5 sub6 M6 sub7 M7 sub8 M8 sub9 M9 obs in 1/100

*Generate the column displays for the tables (^= transfering the information of the sub variables)
forval i = 1/10 {
local j = `i'-1
    gen r`i' = 1 if sub`j' == "m0"
    replace r`i' = 2 if sub`j' == "m1"
    replace r`i' = 3 if sub`j' == "m2"
    replace r`i' = 4 if sub`j' == "m3"
    replace r`i' = 5 if sub`j' == "m4"
    replace r`i' = 6 if sub`j' == "m5"    
    replace r`i' = 7 if sub`j' == "m6"    
    replace r`i' = 8 if sub`j' == "m7"
    replace r`i' = 9 if sub`j' == "m8"
    replace r`i' = 10 if sub`j' == "m9"
}

*Save the computed information in a seperate dataset
savesome nused channels m0 m1 m2 m3 m4 m5 m6 m6 m7 m8 m9 sub0 M0 sub1 M1 sub2 M2 sub3 M3 sub4 M4 sub5 M5 sub6 M6 sub7 M7 sub8 M8 sub9 M9 r1 r2 r3 r4 r5 r6 r7 r8 r9 obs using "${tables}freq_overview.dta" , replace


*Keep only the information necessary for the tables
keep nused channels m0 m1 m2 m3 m4 m5 m6 m6 m7 m8 m9 sub0 M0 sub1 M1 sub2 M2 sub3 M3 sub4 M4 sub5 M5 sub6 M6 sub7 M7 sub8 M8 sub9 M9 r1 r2 r3 r4 r5 r6 r7 r8 r9 obs


** Create tables

* For one or two methods used
do ${do}freq_one.do

* For three methods used
do ${do}freq_three.do

* For four methods used
do ${do}freq_four.do

* For five methods used
do ${do}freq_five.do

* For six methods used
do ${do}freq_six.do

* For seven methods used
do ${do}freq_seven.do

* For eight methods used
do ${do}freq_eight.do

* For nine methods used
do ${do}freq_nine.do

* For ten methods used
do ${do}freq_ten.do


restore

As an example the code for the earlier posted "3 channels" table:

preserve


*freq_three
tempname freq_three

postfile `freq_three' str20 r_1 r_2 e_3 e_4 e_5 e_6 e_7 e_8 e_9 e_10 ///
using "${tables}freq_three.dta" , replace

local j = 175
forvalues i = 1(1)36 {

    local m1 = `j' - 1
    local m2 = `j' - 2
    local m3 = `j' - 3
    local m4 = `j' - 4
    local m5 = `j' - 5
    local m6 = `j' - 6
    local m7 = `j' - 7
        
    local r_1 = r1[`j']
    local r_2 = r2[`j']

    if sub0[`j'] != "m3" & sub1[`j'] != "m3" & sub2[`j'] != "m3" local e_3 = obs[`j']
    if sub0[`j'] == "m3" | sub1[`j'] == "m3" | sub2[`j'] == "m3" local e_3 = .
    if sub0[`j'] == "m4" | sub1[`j'] == "m4" | sub2[`j'] == "m4" local e_3 = .
    if sub0[`j'] == "m5" | sub1[`j'] == "m5" | sub2[`j'] == "m5" local e_3 = .
    if sub0[`j'] == "m6" | sub1[`j'] == "m6" | sub2[`j'] == "m6" local e_3 = .
    if sub0[`j'] == "m7" | sub1[`j'] == "m7" | sub2[`j'] == "m7" local e_3 = .
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" local e_3 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" local e_3 = .

    
    if sub0[`j'] != "m4" & sub1[`j'] != "m4" & sub2[`j'] != "m4" & sub3[`j'] != "m4" local e_4 = obs[cond(`e_3' ==., `j',`m1')]
    if sub0[`j'] == "m4" | sub1[`j'] == "m4" | sub2[`j'] == "m4" | sub3[`j'] == "m4" local e_4 = .
    if sub0[`j'] == "m5" | sub1[`j'] == "m5" | sub2[`j'] == "m5" | sub3[`j'] == "m5" local e_4 = .
    if sub0[`j'] == "m6" | sub1[`j'] == "m6" | sub2[`j'] == "m6" | sub3[`j'] == "m6" local e_4 = .
    if sub0[`j'] == "m7" | sub1[`j'] == "m7" | sub2[`j'] == "m7" | sub3[`j'] == "m7" local e_4 = .
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" | sub3[`j'] == "m8" local e_4 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" local e_4 = .

    
    if sub0[`j'] != "m5" & sub1[`j'] != "m5" & sub2[`j'] != "m5" & sub3[`j'] != "m5" & sub4[`j'] != "m5" & `e_3' ==. & `e_4' ==. local e_5 = obs[`j']
    if sub0[`j'] != "m5" & sub1[`j'] != "m5" & sub2[`j'] != "m5" & sub3[`j'] != "m5" & sub4[`j'] != "m5" & `e_3' ==. & `e_4' !=. local e_5 = obs[`m1']
    if sub0[`j'] != "m5" & sub1[`j'] != "m5" & sub2[`j'] != "m5" & sub3[`j'] != "m5" & sub4[`j'] != "m5" & `e_3' !=. & `e_4' !=. local e_5 = obs[`m2']
    if sub0[`j'] == "m5" | sub1[`j'] == "m5" | sub2[`j'] == "m5" | sub3[`j'] == "m5" | sub4[`j'] == "m5" local e_5 = .
    if sub0[`j'] == "m6" | sub1[`j'] == "m6" | sub2[`j'] == "m6" | sub3[`j'] == "m6" | sub4[`j'] == "m6" local e_5 = .
    if sub0[`j'] == "m7" | sub1[`j'] == "m7" | sub2[`j'] == "m7" | sub3[`j'] == "m7" | sub4[`j'] == "m7" local e_5 = .
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" | sub3[`j'] == "m8" | sub4[`j'] == "m8" local e_5 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" | sub4[`j'] == "m9" local e_5 = .
    
    
    if sub0[`j'] != "m6" & sub1[`j'] != "m6" & sub2[`j'] != "m6" & sub3[`j'] != "m6" & sub4[`j'] != "m6" & sub5[`j'] != "m6" & `e_3' ==. & `e_4' ==. & `e_5' ==. local e_6 = obs[`j']
    if sub0[`j'] != "m6" & sub1[`j'] != "m6" & sub2[`j'] != "m6" & sub3[`j'] != "m6" & sub4[`j'] != "m6" & sub5[`j'] != "m6" & `e_3' ==. & `e_4' ==. & `e_5' !=. local e_6 = obs[`m1']
    if sub0[`j'] != "m6" & sub1[`j'] != "m6" & sub2[`j'] != "m6" & sub3[`j'] != "m6" & sub4[`j'] != "m6" & sub5[`j'] != "m6" & `e_3' ==. & `e_4' !=. & `e_5' !=. local e_6 = obs[`m2']
    if sub0[`j'] != "m6" & sub1[`j'] != "m6" & sub2[`j'] != "m6" & sub3[`j'] != "m6" & sub4[`j'] != "m6" & sub5[`j'] != "m6" & `e_3' !=. & `e_4' !=. & `e_5' !=. local e_6 = obs[`m3']
    if sub0[`j'] == "m6" | sub1[`j'] == "m6" | sub2[`j'] == "m6" | sub3[`j'] == "m6" | sub4[`j'] == "m6" | sub5[`j'] == "m6" local e_6 = .
    if sub0[`j'] == "m7" | sub1[`j'] == "m7" | sub2[`j'] == "m7" | sub3[`j'] == "m7" | sub4[`j'] == "m7" | sub5[`j'] == "m7" local e_6 = .
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" | sub3[`j'] == "m8" | sub4[`j'] == "m8" | sub5[`j'] == "m8" local e_6 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" | sub4[`j'] == "m9" | sub5[`j'] == "m9" local e_6 = .

        
    if sub0[`j'] != "m7" & sub1[`j'] != "m7" & sub2[`j'] != "m7" & sub3[`j'] != "m7" & sub4[`j'] != "m7" & sub5[`j'] != "m7" & sub6[`j'] != "m7" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. local e_7 = obs[`j']
    if sub0[`j'] != "m7" & sub1[`j'] != "m7" & sub2[`j'] != "m7" & sub3[`j'] != "m7" & sub4[`j'] != "m7" & sub5[`j'] != "m7" & sub6[`j'] != "m7" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' !=. local e_7 = obs[`m1']
    if sub0[`j'] != "m7" & sub1[`j'] != "m7" & sub2[`j'] != "m7" & sub3[`j'] != "m7" & sub4[`j'] != "m7" & sub5[`j'] != "m7" & sub6[`j'] != "m7" & `e_3' ==. & `e_4' ==. & `e_5' !=. & `e_6' !=. local e_7 = obs[`m2']
    if sub0[`j'] != "m7" & sub1[`j'] != "m7" & sub2[`j'] != "m7" & sub3[`j'] != "m7" & sub4[`j'] != "m7" & sub5[`j'] != "m7" & sub6[`j'] != "m7" & `e_3' ==. & `e_4' !=. & `e_5' !=. & `e_6' !=. local e_7 = obs[`m3']
    if sub0[`j'] != "m7" & sub1[`j'] != "m7" & sub2[`j'] != "m7" & sub3[`j'] != "m7" & sub4[`j'] != "m7" & sub5[`j'] != "m7" & sub6[`j'] != "m7" & `e_3' !=. & `e_4' !=. & `e_5' !=. & `e_6' !=. local e_7 = obs[`m4']    
    if sub0[`j'] == "m7" | sub1[`j'] == "m7" | sub2[`j'] == "m7" | sub3[`j'] == "m7" | sub4[`j'] == "m7" | sub5[`j'] == "m7" | sub6[`j'] == "m7" local e_7 = .
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" | sub3[`j'] == "m8" | sub4[`j'] == "m8" | sub5[`j'] == "m8" | sub6[`j'] == "m8" local e_7 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" | sub4[`j'] == "m9" | sub5[`j'] == "m9" | sub6[`j'] == "m9" local e_7 = .


    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. local e_8 = obs[`j']
    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' !=. local e_8 = obs[`m1']
    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' !=. & `e_7' !=. local e_8 = obs[`m2']
    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' ==. & `e_4' ==. & `e_5' !=. & `e_6' !=. & `e_7' !=. local e_8 = obs[`m3']
    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' ==. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. local e_8 = obs[`m4']
    if sub0[`j'] != "m8" & sub1[`j'] != "m8" & sub2[`j'] != "m8" & sub3[`j'] != "m8" & sub4[`j'] != "m8" & sub5[`j'] != "m8" & sub6[`j'] != "m8" & sub7[`j'] != "m8" & `e_3' !=. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. local e_8 = obs[`m5']
    if sub0[`j'] == "m8" | sub1[`j'] == "m8" | sub2[`j'] == "m8" | sub3[`j'] == "m8" | sub4[`j'] == "m8" | sub5[`j'] == "m8" | sub6[`j'] == "m8" | sub7[`j'] == "m8" local e_8 = .
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" | sub4[`j'] == "m9" | sub5[`j'] == "m9" | sub6[`j'] == "m9" | sub7[`j'] == "m9" local e_8 = .


    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. & `e_8' ==. local e_9 = obs[`j']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. & `e_8' !=. local e_9 = obs[`m1']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' !=. & `e_8' !=. local e_9 = obs[`m2']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' !=. & `e_7' !=. & `e_8' !=. local e_9 = obs[`m3']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' ==. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. local e_9 = obs[`m4']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' ==. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. local e_9 = obs[`m5']
    if sub0[`j'] != "m9" & sub1[`j'] != "m9" & sub2[`j'] != "m9" & sub3[`j'] != "m9" & sub4[`j'] != "m9" & sub5[`j'] != "m9" & sub6[`j'] != "m9" & sub7[`j'] != "m9" & sub8[`j'] != "m9" & `e_3' !=. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. local e_9 = obs[`m6']
    if sub0[`j'] == "m9" | sub1[`j'] == "m9" | sub2[`j'] == "m9" | sub3[`j'] == "m9" | sub4[`j'] == "m9" | sub5[`j'] == "m9" | sub6[`j'] == "m9" | sub7[`j'] == "m9" | sub8[`j'] == "m9" local e_9 = .


    if `e_3' !=. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m7']
    if `e_3' ==. & `e_4' !=. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m6']
    if `e_3' ==. & `e_4' ==. & `e_5' !=. & `e_6' !=. & `e_7' !=. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m5']
    if `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' !=. & `e_7' !=. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m4']
    if `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' !=. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m3']
    if `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. & `e_8' !=. & `e_9' !=. local e_10 = obs[`m2']
    if `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. & `e_8' ==. & `e_9' !=. local e_10 = obs[`m1']
    if `e_3' ==. & `e_4' ==. & `e_5' ==. & `e_6' ==. & `e_7' ==. & `e_8' ==. & `e_9' ==. local e_10 = obs[`j']

    
    post `freq_three' ("`r_1'") (`r_2') (`e_3') (`e_4') (`e_5') (`e_6') (`e_7') (`e_8') (`e_9') (`e_10')
    
    local j = `j' - cond(`e_3' !=., 1,0) - cond(`e_4' !=., 1,0) - cond(`e_5' !=., 1,0) - cond(`e_6' !=., 1,0) - cond(`e_7' !=., 1,0) - cond(`e_8' !=., 1,0) - cond(`e_9' !=., 1,0) - cond(`e_10' !=., 1,0)
    }
postclose `freq_three'

use "${tables}freq_three.dta", clear
destring r_1, replace

listtab r_1 r_2 e_3 e_4 e_5 e_6 e_7 e_8 e_9 e_10 ///
using "${tables}freq_three.tex", rs(tabular) headlines("") footlines("") replace


egen sum_line = rowtotal(e_3 e_4 e_5 e_6 e_7 e_8 e_9 e_10)

egen sum_total = sum(sum_line)
global sum3 = sum_total[1]
drop sum_*
macro list sum3

restore

Many thanks to Mike, Nick & Robert.

Bests
Andreas