Extracting Substrings from Variables Using the Table Command

[Jared Greathouse]

My mentor suggests using Excel to make tables for a systematic review we're doing, but this to me is unacceptable, so I must learn the table command. I have some experience with using it, but there's a particular issue I've ran across, namely extracting the occurrence of substrings in a variable and counting them. Let's look at my sample data. Note that I use tabsplit by Nick Cox

* Example generated by -dataex-. For more info, type help dataex
clear
input str64 design
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID" "SCM""'
`""SCM""'      
`""BA""'       
`""CHT""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""XS""'       
`""DID""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""CHT""'      
`""DID" "SCM""'
`""CBA""'      
`""BA""'       
`""ITS""'      
`""CHT""'      
`""BA""'       
`""BA""'       
`""DID" "IVE""'
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""DID" "RDD""'
`""CBA""'      
`""CHT""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""XS""'       
`""BA""'       
`""DID" "IVE""'
`""DID" "SCM""'
`""DID""'      
`""XS""'       
`""ITS""'      
`""DID""'      
`""XS""'       
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""XS""'       
`""SCM""'      
`""DID""'      
`""DID""'      
`""CBA""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""ITS""'      
`""ITS""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""BA""'       
`""XS" "BA""'  
`""ITS""'      
`""XS""'       
`""SCM""'      
`""XS""'       
`""PH""'       
end

cls

cap qui which sencode

if _rc {
    
    qui ssc inst sencode, replace
}

preserve
sencode design, replace
*Table 1
table design
restore

cap qui which tabsplit

if _rc {
    
    qui net inst tab_chi.pkg, replace
}
*Table 2
tabsplit design, parse(`"""') sort

The issue here is that some studies use multiple different designs. Table 1 produces

--------------------------
              |  Frequency
--------------+-----------
design        |           
  "DID"       |         41
  "BA"        |         13
  "XS"        |         15
  "DID" "SCM" |          7
  "SCM"       |          3
  "CHT"       |          6
  "CBA"       |          3
  "ITS"       |          7
  "DID" "IVE" |          2
  "DID" "RDD" |          1
  "XS" "BA"   |          1
  "PH"        |          1
  Total       |        100
--------------------------

Table two gives us

     design |      Freq.     Percent        Cum.
------------+-----------------------------------
        DID |         51       45.95       45.95
         XS |         16       14.41       60.36
         BA |         14       12.61       72.97
        SCM |         10        9.01       81.98
        ITS |          7        6.31       88.29
        CHT |          6        5.41       93.69
        CBA |          3        2.70       96.40
        IVE |          2        1.80       98.20
         PH |          1        0.90       99.10
        RDD |          1        0.90      100.00
------------+-----------------------------------
      Total |        111      100.00

I would like to use the table command to make table 2. The issue is that the tabsplit command doesn't allow me to extract the underlying dataset/matrix used to make the table.

So what I suppose I want, is to count the occurrence of the strings in quotes, make a table from these strings, and have the rows to the table be labelled by the string itself. How might I do this?

[Jared Greathouse]

I suppose one solution is to do

* Example generated by -dataex-. For more info, type help dataex
clear
input str64 design
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID" "SCM""'
`""SCM""'      
`""BA""'       
`""CHT""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""XS""'       
`""DID""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""CHT""'      
`""DID" "SCM""'
`""CBA""'      
`""BA""'       
`""ITS""'      
`""CHT""'      
`""BA""'       
`""BA""'       
`""DID" "IVE""'
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""DID" "RDD""'
`""CBA""'      
`""CHT""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""XS""'       
`""BA""'       
`""DID" "IVE""'
`""DID" "SCM""'
`""DID""'      
`""XS""'       
`""ITS""'      
`""DID""'      
`""XS""'       
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""XS""'       
`""SCM""'      
`""DID""'      
`""DID""'      
`""CBA""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""ITS""'      
`""ITS""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""BA""'       
`""XS" "BA""'  
`""ITS""'      
`""XS""'       
`""SCM""'      
`""XS""'       
`""PH""'       
end
split design, parse(" ")
cls
levelsof design1, matcell(A)

levelsof design2, matcell(B)

mat l A
mat l B

tabsplit design

where I could save the rows of the matrices as the distinct names... save these as temporary datasets, merge these together by row name, and then make the table that way.

[Jared Greathouse]

Presumably, there's a cleaner way to do this. I was too lazy to write the loop to automate the second matrix (or however many matrices we'd need), but this does as I desire.

* Example generated by -dataex-. For more info, type help dataex
clear
input str64 design
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID" "SCM""'
`""SCM""'      
`""BA""'       
`""CHT""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""XS""'       
`""DID""'      
`""XS""'       
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""CHT""'      
`""DID" "SCM""'
`""CBA""'      
`""BA""'       
`""ITS""'      
`""CHT""'      
`""BA""'       
`""BA""'       
`""DID" "IVE""'
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""BA""'       
`""XS""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""DID" "RDD""'
`""CBA""'      
`""CHT""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""BA""'       
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""XS""'       
`""BA""'       
`""DID" "IVE""'
`""DID" "SCM""'
`""DID""'      
`""XS""'       
`""ITS""'      
`""DID""'      
`""XS""'       
`""XS""'       
`""DID""'      
`""BA""'       
`""DID""'      
`""XS""'       
`""SCM""'      
`""DID""'      
`""DID""'      
`""CBA""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""ITS""'      
`""ITS""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""BA""'       
`""XS" "BA""'  
`""ITS""'      
`""XS""'       
`""SCM""'      
`""XS""'       
`""PH""'       
end

tempfile col1 col2

split design, parse(`"""')
drop design1 design3
cls
preserve
levelsof design2, matcell(A) loc(names) clean

mat rownames A = `r(levels)'
local rownames : rowfullnames A
local c : word count `rownames'

local names : colfullnames A
local newnames : subinstr local names "_cons" "cons", word

// rename columns of matrix
matrix colnames A = `newnames'

// convert to dataset
clear
svmat A, names(col)

// add matrix row names to dataset
gen rownames = ""
forv i = 1/`c' {
    qui replace rownames = "`:word `i' of `rownames''" in `i'
}
sa `col1', replace
restore


preserve
levelsof design4, matcell(B) loc(names) clean

mat rownames B = `r(levels)'
local rownames : rowfullnames B
local c : word count `rownames'

local names : colfullnames B
local newnames : subinstr local names "_cons" "cons", word

// rename columns of matrix
mat colnames B = `newnames'

// convert to dataset
clear
svmat B, names(col)

// add matrix row names to dataset
g rownames = ""
forv i = 1/`c' {
    qui replace rownames = "`:word `i' of `rownames''" in `i'
}
rename c1 c2
sa `col2', replace
restore

u `col1', clear

cls

fmerge 1:1 rownames using "`col2'", keepusing(c2) nogen

egen totals =  rowtotal(c1 c2)

drop c1 c2

rename (rownames totals) (Designs Totals)

[William Lisowski]

Starting with your example data, the following apparently reproduces your results.

split design, parse(`"""')
drop design
generate seq = _n
reshape long design, i(seq) j(col)
replace design = trim(design)
drop if missing(design)
rename design Designs
generate count = 1
collapse (sum) Totals=count, by(Designs)
list

. list

     +------------------+
     | Designs   Totals |
     |------------------|
  1. |      BA       14 |
  2. |     CBA        3 |
  3. |     CHT        6 |
  4. |     DID       51 |
  5. |     ITS        7 |
     |------------------|
  6. |     IVE        2 |
  7. |      PH        1 |
  8. |     RDD        1 |
  9. |     SCM       10 |
 10. |      XS       16 |
     +------------------+

[Jared Greathouse]

I've managed to simply the code drastically since I posted this, but, I specifically need to export the table to word. Of course I didn't specify this at first, but this works as a great start.

Presumably, the table command could be used with putdocx to accomplish the latter task. William Lisowski

[William Lisowski]

Presumably, the table command could be used with putdocx to accomplish the latter task.

Seems likely, given that adding just three commands

putdocx begin
putdocx table des = data(Designs Totals), varnames layout(autofitcontents)
putdocx save example, replace

to then end of the code in post #4 produces example.docx containing the proof of concept.

[Jared Greathouse]

Thank you. I'll see if it works then

[Nick Cox]

Good question. A quick hack at tabsplit adds a saving() option to save the table variables to a new dataset.

*! 2.1.0 NJC 7 June 2022
*! 2.0.0 NJC 18 May 2004
* 1.4.0 NJC 16 February 2001
* 1.3.0 NJC 17 July 2000
* 1.2.2 NJC 23 March 1999
* 1.1.0 NJC 11 August 1998
* 1.0.0 NJC 29 July 1998
program tabsplit
    version 8  
    syntax varname(string) [if] [in] [ , noTrim Parse(passthru) CHARacters *saving(str asis) ]

    marksample touse, strok

    qui {
        count if `touse'
        if r(N) == 0 error 2000
        
        tempvar data newdata
        tempname stub
        if "`trim'" != "" gen `data' = `varlist' if `touse'
        else gen `data' = trim(`varlist') if `touse'
        
        if "`characters'" != "" {
            compress `data'
            local vartype: type `data'
            local len = substr("`vartype'",4,.)
            forval i = 1/`len' {
                gen `stub'`i' = substr(`data',`i',1)
            }
        }
        else split `data', `parse' gen(`stub') `trim'  

        preserve
        
        local label : variable label `varlist'
        if `"`label'"' == "" local label "`varlist'"
        
        stack `stub'* if `touse', into(`newdata') clear
        if "`trim'" == "" replace `newdata' = trim(`newdata')
        
        label var `newdata' `"`label'"'
    }                
        
    tab `newdata', `options'

    if `"`saving'"' != "" {
        rename `newdata' _value
        di _n  
        save `saving'
    }
end

Demo:

* Example generated by -dataex-. For more info, type help dataex
clear
input str64 design
`""DID""'      
`""BA""'      
`""XS""'      
`""DID""'      
`""DID" "SCM""'
`""SCM""'      
`""BA""'      
`""CHT""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""XS""'      
`""DID""'      
`""DID""'      
`""XS""'      
`""DID""'      
`""XS""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'      
`""CHT""'      
`""DID" "SCM""'
`""CBA""'      
`""BA""'      
`""ITS""'      
`""CHT""'      
`""BA""'      
`""BA""'      
`""DID" "IVE""'
`""XS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""BA""'      
`""XS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""BA""'      
`""DID" "RDD""'
`""CBA""'      
`""CHT""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""BA""'      
`""BA""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID" "SCM""'
`""XS""'      
`""DID""'      
`""BA""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""XS""'      
`""BA""'      
`""DID" "IVE""'
`""DID" "SCM""'
`""DID""'      
`""XS""'      
`""ITS""'      
`""DID""'      
`""XS""'      
`""XS""'      
`""DID""'      
`""BA""'      
`""DID""'      
`""XS""'      
`""SCM""'      
`""DID""'      
`""DID""'      
`""CBA""'      
`""ITS""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""DID""'      
`""ITS""'      
`""ITS""'      
`""DID""'      
`""DID" "SCM""'
`""DID""'      
`""DID""'      
`""DID""'      
`""CHT""'      
`""BA""'      
`""XS" "BA""'  
`""ITS""'      
`""XS""'      
`""SCM""'      
`""XS""'      
`""PH""'      
end

tabsplit design, saving(design, replace) sort

use design, clear

list

. tabsplit design, saving(design, replace) sort

     design |      Freq.     Percent        Cum.
------------+-----------------------------------
      "DID" |         51       45.95       45.95
       "XS" |         16       14.41       60.36
       "BA" |         14       12.61       72.97
      "SCM" |         10        9.01       81.98
      "ITS" |          7        6.31       88.29
      "CHT" |          6        5.41       93.69
      "CBA" |          3        2.70       96.40
      "IVE" |          2        1.80       98.20
       "PH" |          1        0.90       99.10
      "RDD" |          1        0.90      100.00
------------+-----------------------------------
      Total |        111      100.00


file design.dta saved

.
. use design, clear

.
. list

     +-----------------+
     | _stack   _value |
     |-----------------|
  1. |      1    "DID" |
  2. |      1     "BA" |
  3. |      1     "XS" |
  4. |      1    "DID" |
  5. |      1    "DID" |
     |-----------------|
  6. |      1    "SCM" |
  7. |      1     "BA" |
  8. |      1    "CHT" |
  9. |      1    "DID" |
 10. |      1    "DID" |
     |-----------------|
 11. |      1    "CHT" |
 12. |      1     "XS" |
 13. |      1    "DID" |
 14. |      1    "DID" |
 15. |      1     "XS" |
     |-----------------|
 16. |      1    "DID" |
 17. |      1     "XS" |
 18. |      1    "DID" |
 19. |      1    "DID" |
 20. |      1    "DID" |
     |-----------------|
 21. |      1     "XS" |
 22. |      1    "CHT" |
 23. |      1    "DID" |
 24. |      1    "CBA" |
 25. |      1     "BA" |
     |-----------------|
 26. |      1    "ITS" |
 27. |      1    "CHT" |
 28. |      1     "BA" |
 29. |      1     "BA" |
 30. |      1    "DID" |
     |-----------------|
 31. |      1     "XS" |
 32. |      1    "DID" |
 33. |      1    "DID" |
 34. |      1    "DID" |
 35. |      1    "DID" |
     |-----------------|
 36. |      1    "DID" |
 37. |      1     "BA" |
 38. |      1     "XS" |
 39. |      1    "DID" |
 40. |      1    "DID" |
     |-----------------|
 41. |      1    "DID" |
 42. |      1     "BA" |
 43. |      1    "DID" |
 44. |      1    "CBA" |
 45. |      1    "CHT" |
     |-----------------|
 46. |      1    "ITS" |
 47. |      1    "DID" |
 48. |      1    "DID" |
 49. |      1     "BA" |
 50. |      1     "BA" |
     |-----------------|
 51. |      1    "DID" |
 52. |      1    "DID" |
 53. |      1    "DID" |
 54. |      1    "DID" |
 55. |      1     "XS" |
     |-----------------|
 56. |      1    "DID" |
 57. |      1     "BA" |
 58. |      1    "DID" |
 59. |      1    "DID" |
 60. |      1    "DID" |
     |-----------------|
 61. |      1    "DID" |
 62. |      1    "DID" |
 63. |      1     "XS" |
 64. |      1     "BA" |
 65. |      1    "DID" |
     |-----------------|
 66. |      1    "DID" |
 67. |      1    "DID" |
 68. |      1     "XS" |
 69. |      1    "ITS" |
 70. |      1    "DID" |
     |-----------------|
 71. |      1     "XS" |
 72. |      1     "XS" |
 73. |      1    "DID" |
 74. |      1     "BA" |
 75. |      1    "DID" |
     |-----------------|
 76. |      1     "XS" |
 77. |      1    "SCM" |
 78. |      1    "DID" |
 79. |      1    "DID" |
 80. |      1    "CBA" |
     |-----------------|
 81. |      1    "ITS" |
 82. |      1    "DID" |
 83. |      1    "DID" |
 84. |      1    "DID" |
 85. |      1    "DID" |
     |-----------------|
 86. |      1    "ITS" |
 87. |      1    "ITS" |
 88. |      1    "DID" |
 89. |      1    "DID" |
 90. |      1    "DID" |
     |-----------------|
 91. |      1    "DID" |
 92. |      1    "DID" |
 93. |      1    "CHT" |
 94. |      1     "BA" |
 95. |      1     "XS" |
     |-----------------|
 96. |      1    "ITS" |
 97. |      1     "XS" |
 98. |      1    "SCM" |
 99. |      1     "XS" |
100. |      1     "PH" |
     |-----------------|
101. |      2          |
102. |      2          |
103. |      2          |
104. |      2          |
105. |      2    "SCM" |
     |-----------------|
106. |      2          |
107. |      2          |
108. |      2          |
109. |      2          |
110. |      2          |
     |-----------------|
111. |      2          |
112. |      2          |
113. |      2          |
114. |      2          |
115. |      2          |
     |-----------------|
116. |      2          |
117. |      2          |
118. |      2          |
119. |      2          |
120. |      2    "SCM" |
     |-----------------|
121. |      2          |
122. |      2          |
123. |      2    "SCM" |
124. |      2          |
125. |      2          |
     |-----------------|
126. |      2          |
127. |      2          |
128. |      2          |
129. |      2          |
130. |      2    "IVE" |
     |-----------------|
131. |      2          |
132. |      2          |
133. |      2          |
134. |      2          |
135. |      2    "SCM" |
     |-----------------|
136. |      2          |
137. |      2          |
138. |      2          |
139. |      2          |
140. |      2          |
     |-----------------|
141. |      2          |
142. |      2          |
143. |      2    "RDD" |
144. |      2          |
145. |      2          |
     |-----------------|
146. |      2          |
147. |      2          |
148. |      2          |
149. |      2          |
150. |      2          |
     |-----------------|
151. |      2          |
152. |      2          |
153. |      2          |
154. |      2    "SCM" |
155. |      2          |
     |-----------------|
156. |      2          |
157. |      2          |
158. |      2          |
159. |      2          |
160. |      2          |
     |-----------------|
161. |      2          |
162. |      2          |
163. |      2          |
164. |      2          |
165. |      2    "IVE" |
     |-----------------|
166. |      2    "SCM" |
167. |      2          |
168. |      2          |
169. |      2          |
170. |      2          |
     |-----------------|
171. |      2          |
172. |      2          |
173. |      2          |
174. |      2          |
175. |      2          |
     |-----------------|
176. |      2          |
177. |      2          |
178. |      2          |
179. |      2          |
180. |      2          |
     |-----------------|
181. |      2          |
182. |      2          |
183. |      2          |
184. |      2          |
185. |      2          |
     |-----------------|
186. |      2          |
187. |      2          |
188. |      2          |
189. |      2    "SCM" |
190. |      2          |
     |-----------------|
191. |      2          |
192. |      2          |
193. |      2          |
194. |      2          |
195. |      2     "BA" |
     |-----------------|
196. |      2          |
197. |      2          |
198. |      2          |
199. |      2          |
200. |      2          |
     +-----------------+

[Jared Greathouse]

This was actually my first idea. Since tabsplit does all the work up front, at that point it's just a matter of collapsing on the new data that tabsplit is based on.... And if I were working by my lonesome, I honestly think that's what I would do. "Tabsplit doesn't save this? Ehhh alright, just make it do so!"

But since I'm working with others, I wanted to use as little ado-gymnastics as possible. Either way though, these all work well. Thanks so much!