Trying to generate a table of counts of unique IDs to a specific format

[Kehinde Atoloye]

I am trying to generate a table of the below format.


Below is my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input int clustercode str8 uniquestrucid str10 uniquehhid str12 uniquegirlid byte(femalepregnant femalelivinghere) str8 state_name str10 lga_name str12 exposure
2224 "2224_33"  "2224_33_1"  ""             . . "KADUNA"   "SANGA"      "Control"     
2229 "2229_49"  "2229_49_43" ""             . . "KADUNA"   "SANGA"      "Control"     
2229 "2229_5"   ""           ""             . . "KADUNA"   "SANGA"      "Control"     
2236 "2236_18"  "2236_18_1"  "2236_18_1_2"  1 1 "KADUNA"   "SANGA"      "Control"     
2254 "2254_11"  "2254_11_12" "2254_11_12_1" 0 1 "KADUNA"   "SANGA"      "Control"     
2255 "2255_31"  ""           ""             . . "KADUNA"   "SANGA"      "Control"     
2256 "2256_26"  "2256_26_15" ""             . . "KADUNA"   "SANGA"      "Control"     
2260 "2260_13"  "2260_13_1"  "2260_13_1_1"  0 1 "KADUNA"   "SANGA"      "Control"     
2261 "2261_51"  ""           ""             . . "KADUNA"   "SANGA"      "Control"     
2261 "2261_31"  "2261_31_1"  ""             . . "KADUNA"   "SANGA"      "Control"     
2261 "2261_25"  "2261_25_1"  "2261_25_1_1"  0 1 "KADUNA"   "SANGA"      "Control"     
2261 "2261_29"  "2261_29_1"  ""             . . "KADUNA"   "SANGA"      "Control"     
2262 "2262_32"  "2262_32_22" "2262_32_22_1" 0 1 "KADUNA"   "SANGA"      "Control"     
2262 "2262_35"  "2262_35_25" "2262_35_25_3" 0 1 "KADUNA"   "SANGA"      "Control"     
2263 "2263_21"  "2263_21_16" ""             . . "KADUNA"   "SANGA"      "Control"     
2153 "2153_5"   ""           ""             . . "KADUNA"   "IGABI"      "Intervention"
2156 "2156_39"  ""           ""             . . "KADUNA"   "IGABI"      "Intervention"
2156 "2156_6"   "2156_6_1"   "2156_6_1_4"   0 1 "KADUNA"   "IGABI"      "Intervention"
2156 "2156_47"  "2156_47_1"  "2156_47_1_1"  0 1 "KADUNA"   "IGABI"      "Intervention"
2156 "2156_25"  "2156_25_1"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2156 "2156_61"  ""           ""             . . "KADUNA"   "IGABI"      "Intervention"
2157 "2157_85"  "2157_85_1"  "2157_85_1_1"  0 1 "KADUNA"   "IGABI"      "Intervention"
2157 "2157_87"  "2157_87_1"  "2157_87_1_3"  0 1 "KADUNA"   "IGABI"      "Intervention"
2157 "2157_81"  "2157_81_1"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2157 "2157_27"  "2157_27_1"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2157 "2157_63"  ""           ""             . . "KADUNA"   "IGABI"      "Intervention"
2157 "2157_82"  "2157_82_1"  "2157_82_1_1"  1 1 "KADUNA"   "IGABI"      "Intervention"
2158 "2158_16"  "2158_16_1"  "2158_16_1_1"  1 1 "KADUNA"   "IGABI"      "Intervention"
2160 "2160_36"  "2160_36_6"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2160 "2160_56"  "2160_56_1"  "2160_56_1_1"  0 1 "KADUNA"   "IGABI"      "Intervention"
2160 "2160_1"   "2160_1_1"   "2160_1_1_1"   0 1 "KADUNA"   "IGABI"      "Intervention"
2160 "2160_1"   "2160_1_3"   ""             . . "KADUNA"   "IGABI"      "Intervention"
2161 "2161_40"  "2161_40_1"  "2161_40_1_1"  0 1 "KADUNA"   "IGABI"      "Intervention"
2161 "2161_36"  "2161_36_2"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2161 "2161_47"  "2161_47_1"  "2161_47_1_1"  0 1 "KADUNA"   "IGABI"      "Intervention"
2165 "2165_14"  "2165_14_2"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2165 "2165_8"   "2165_8_2"   ""             . . "KADUNA"   "IGABI"      "Intervention"
2165 "2165_18"  "2165_18_2"  ""             . . "KADUNA"   "IGABI"      "Intervention"
2165 "2165_5"   "2165_5_3"   ""             . . "KADUNA"   "IGABI"      "Intervention"
2111 "2111_30"  "2111_30_1"  ""             . . "KADUNA"   "SABON_GARI" "Intervention"
2116 "2116_36"  ""           ""             . . "KADUNA"   "SABON_GARI" "Intervention"
2117 "2117_21"  "2117_21_4"  "2117_21_4_1"  0 1 "KADUNA"   "SABON_GARI" "Intervention"
1104 "1104_76"  "1104_76_1"  "1104_76_1_2"  0 1 "NASARAWA" "DOMA"       "Intervention"
1104 "1104_31"  "1104_31_1"  ""             . . "NASARAWA" "DOMA"       "Intervention"
1104 "1104_24"  "1104_24_1"  "1104_24_1_1"  0 1 "NASARAWA" "DOMA"       "Intervention"
1104 "1104_47"  "1104_47_1"  "1104_47_1_2"  0 1 "NASARAWA" "DOMA"       "Intervention"
1104 "1104_30"  ""           ""             . . "NASARAWA" "DOMA"       "Intervention"
1108 "1108_111" "1108_111_1" "1108_111_1_1" 0 1 "NASARAWA" "DOMA"       "Intervention"
1108 "1108_49"  "1108_49_1"  "1108_49_1_2"  0 1 "NASARAWA" "DOMA"       "Intervention"
1110 "1110_7"   "1110_7_1"   ""             . . "NASARAWA" "DOMA"       "Intervention"
end
label values femalepregnant femalepregnant
label def femalepregnant 0 "No", modify
label def femalepregnant 1 "Yes", modify
label values femalelivinghere femalelivinghere
label def femalelivinghere 1 "Yes", modify

Below is my code so far:
[CODE]
* Step 1: Count the number of unique structures per cluster, LGA, and state
egen num_structures = count(uniquestrucid), by(state_name exposure lga_name clustercode)

* Step 2: Count the number of unique households per cluster, LGA, and state
egen num_households = count(uniquehhid), by(state_name exposure lga_name clustercode)

* Step 3: Count the number of unique females aged 15-19 per cluster, LGA, and state
egen num_females_15_19 = count(uniquegirlid), by(state_name exposure lga_name clustercode)

* Step 4: Count the number of **eligible females (15-19, not pregnant, and living there)**
egen num_eligible_females = count(uniquegirlid) if femalepregnant == 0 & femalelivinghere == 1, by(state_name exposure lga_name clustercode)

* Step 5: Collapse data to keep only unique counts at the required levels
collapse (sum) num_structures num_households num_females_15_19 num_eligible_females, by(state_name exposure lga_name clustercode)

* Step 6: Sort data to maintain table structure
sort state_name exposure lga_name clustercode

* Step 7: Display the table
list state_name exposure lga_name clustercode num_structures num_households num_females_15_19 num_eligible_females, sepby(state_name exposure lga_name)
[CODE]

The count egen function is not counting unqiuely. I tried nval and unique to no avail.

[Nick Cox]

count() just counts non-missing values, as is documented in the help.

. clear

. set obs 4
Number of observations (_N) was 0, now 4.

. gen whatever = 42

. list

     +----------+
     | whatever |
     |----------|
  1. |       42 |
  2. |       42 |
  3. |       42 |
  4. |       42 |
     +----------+

. egen count = count(whatever)

. list

     +------------------+
     | whatever   count |
     |------------------|
  1. |       42       4 |
  2. |       42       4 |
  3. |       42       4 |
  4. |       42       4 |
     +------------------+

See SJ 8-4 for a discussion of counting distinct values, and more recent updates for more functionality.


As explained in the 2008 paper, egen's tag() and total() functions can be used to count distinct values.

I've heard of unique (from SSC, as you're asked to explain, but not of nval (source not given). In either case, a report that a command didn't work as you wanted is not one I can answer.

[Hemanshu Kumar]

Building on #2, here is how you might produce the table you need:

encode exposure, gen(exposure_num)
encode state_name, gen(state)
encode lga_name, gen(lga)

egen byte struc_tag = tag(exposure_num state lga uniquestrucid)
egen byte hh_tag = tag(exposure_num state lga uniquehhid)
egen byte girl_tag = tag(exposure_num state lga uniquegirlid)

gen byte eligible_girl = (girl_tag & femalepregnant == 0 & femalelivinghere == 1)

table (exposure_num state lga) (), ///
    stat(sum struc_tag hh_tag girl_tag eligible_girl) nototals    
    
collect label levels var ///
    struc_tag "No. of unique structures visited" ///
    hh_tag "No. of unique households visited" ///
    girl_tag "No. of females 15-19 identified" ///
    eligible_girl "No. of eligible females identified" ///
    , replace

collect style header exposure_num, title(hide)
collect style header state, title(hide)
collect style header lga, title(hide)

This produces:

. collect preview

------------------------------------------------------------------------------------------------------------------------------------------------------------
               |  No. of unique structures visited   No. of unique households visited   No. of females 15-19 identified   No. of eligible females identified
---------------+--------------------------------------------------------------------------------------------------------------------------------------------
Control        |                                                                                                                                            
  KADUNA       |                                                                                                                                            
    SANGA      |                                15                                 12                                 6                                    5
Intervention   |                                                                                                                                            
  KADUNA       |                                                                                                                                            
    IGABI      |                                23                                 20                                10                                    8
    SABON_GARI |                                 3                                  2                                 1                                    1
  NASARAWA     |                                                                                                                                            
    DOMA       |                                 8                                  7                                 5                                    5
------------------------------------------------------------------------------------------------------------------------------------------------------------

[Kehinde Atoloye]

Thank you very much for the assistance. Great thanks too to Nick for pointing us in the right direction. I had tried tag() initially, I guess I was missing something about it. However, after trying Hemanshu's code, I was getting something strange in the excel output after I exported it using collect export. dots were being added at the end of each number which are supposed to be whole numbers, though these dots did not show in Stata's screen output. Below is a screenshot of the excel output. Any idea what could have caused this? the dot shows with or without using nformat(%5.0f sum) with the export command. Could it be because I did not use a collect layout with it? What is the appropriate layout to use for it? Thanks.

[Hemanshu Kumar]

Try adding this before the collect export command:

collect style cell result#var, nformat(%5.0f)

[Nick Cox]

#2 should have included these details:

SJ-23-4 dm0042_5  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct, distinctgen if installed)  N. J. Cox and G. M. Longton
        Q4/23   SJ 23(4):1096
        comments out (and thus removes) a call to clear Mata at the
        close of work, which was frustrating some other projects
        also using Mata

SJ-23-2 dm0042_4  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct, distinctgen if installed)  N. J. Cox and G. M. Longton
        Q2/23   SJ 23(2):595--596
        most important change is addition of distinctgen command

SJ-20-4 dm0042_3  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q4/20   SJ 20(4):1028--1030
        sort() option has been added

SJ-15-3 dm0042_2  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q3/15   SJ 15(3):899
        improved table format and display of large numbers of
        observations

SJ-12-2 dm0042_1  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q2/12   SJ 12(2):352
        options added to restrict output to variables with a minimum
        or maximum of distinct values

SJ-8-4  dm0042  . . . . . . . . . . . .  Speaking Stata: Distinct observations
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q4/08   SJ 8(4):557--568
        shows how to answer questions about distinct observations
        from first principles; provides a convenience command