making a two-way table for dummy variable overlap frequencies

[Ethan Schoolman]

Hi -- I have data on whether or not farmers plant different kinds of crops; each crop is a dummy variable (yes or no). The crops are not mutually exclusive; farmers can and most do plant more than one. Is there an easy way to make a two-way table that shows, in each cell, the percent of respondents that plant each possible pair of crops. For instance: (these aren't the actual crops):

Apples Pears Oranges Grapes
Apples 100% 25% 35% 60%
Pears 100% 40% 25%
Oranges 100% 40%
Grapes 100%

And, ideally, code also for exporting the table into excel...

[Nick Cox]

Here is how to get a matrix with on the rows several indicator variables and in the columns the fraction of observations saying yes to various questions. The first matrix ignores missings and the second gives the fraction of 1s compared with that of 0s, 1s and missings. Not tested much.

webuse nlswork , clear

* install from SSC or SJ
distinct, min(2) max(2)

matrix wanted1 = J(7, 7, .)
matrix wanted2 = J(7, 7, .)

tokenize "msp nev_mar collgrad not_smsa c_city south union"

forval j = 1/7 {
    quietly count if ``j'' == 1
    local count = r(N)
    
    forval k = 1/7 {
        su ``k'' if ``j'' == 1, meanonly
        local sum = r(sum)
        matrix wanted1[`j', `k'] = 100 * r(mean)
        matrix wanted2[`j', `k'] = 100 * `sum'/`count'    
    }
}


mat rownames wanted1 = `*'
mat colnames wanted1 = `*'
mat rownames wanted2 = `*'
mat colnames wanted2 = `*'

mat li wanted1 , format(%3.1f)
mat li wanted2 , format(%3.1f)

su `*'

. webuse nlswork , clear
(National Longitudinal Survey.  Young Women 14-26 years of age in 1968)

.
. * install from SSC or SJ
. distinct, min(2) max(2)

---------------------------------
          |     total   distinct
----------+----------------------
      msp |     28518          2
  nev_mar |     28518          2
 collgrad |     28534          2
 not_smsa |     28526          2
   c_city |     28526          2
    south |     28526          2
    union |     19238          2
---------------------------------

.
. matrix wanted1 = J(7, 7, .)

. matrix wanted2 = J(7, 7, .)

.
. tokenize "msp nev_mar collgrad not_smsa c_city south union"

.
. forval j = 1/7 {
  2.     quietly count if ``j'' == 1
  3.         local count = r(N)
  4.        
.     forval k = 1/7 {
  5.                 su ``k'' if ``j'' == 1, meanonly
  6.                 local sum = r(sum)
  7.                 matrix wanted1[`j', `k'] = 100 * r(mean)
  8.                 matrix wanted2[`j', `k'] = 100 * `sum'/`count'    
  9.         }
 10. }

.
.
. mat rownames wanted1 = `*'

. mat colnames wanted1 = `*'

. mat rownames wanted2 = `*'

. mat colnames wanted2 = `*'

.
. mat li wanted1 , format(%3.1f)

wanted1[7,7]
               msp   nev_mar  collgrad  not_smsa    c_city     south     union
     msp     100.0       0.0      17.2      31.4      29.6      42.3      22.2
 nev_mar       0.0     100.0      18.3      21.6      48.3      35.9      25.1
collgrad      61.8      25.0     100.0      20.6      37.4      35.9      29.2
not_smsa      67.0      17.6      12.3     100.0       0.0      54.7      18.8
  c_city      50.0      31.0      17.6       0.0     100.0      40.8      27.7
   south      62.3      20.2      14.7      37.7      35.5     100.0      16.8
   union      58.6      20.7      24.3      22.7      40.4      29.7     100.0

. mat li wanted2 , format(%3.1f)

wanted2[7,7]
               msp   nev_mar  collgrad  not_smsa    c_city     south     union
     msp     100.0       0.0      17.2      31.4      29.6      42.3      15.4
 nev_mar       0.0     100.0      18.3      21.6      48.2      35.9      14.3
collgrad      61.8      25.0     100.0      20.6      37.4      35.8      22.9
not_smsa      67.0      17.5      12.3     100.0       0.0      54.7      12.7
  c_city      49.9      31.0      17.6       0.0     100.0      40.8      17.9
   south      62.3      20.1      14.7      37.7      35.5     100.0      11.5
   union      58.6      20.7      24.3      22.7      40.3      29.7     100.0

.
. su `*'

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
         msp |     28,518    .6029175    .4893019          0          1
     nev_mar |     28,518    .2296795    .4206341          0          1
    collgrad |     28,534    .1680451    .3739129          0          1
    not_smsa |     28,526    .2824441    .4501961          0          1
      c_city |     28,526     .357218    .4791882          0          1
-------------+---------------------------------------------------------
       south |     28,526    .4095562    .4917605          0          1
       union |     19,238    .2344319    .4236542          0          1

.
end of do-file

[Ethan Schoolman]

Thanks! I will give it a try and report back.

[Ethan Schoolman]

Appears to work perfectly, thank you.