I ve been trying to figure this one out for a while now. I have a data file with 25 indicator variables for various health conditions for each study participant (n=300 in dataex example). x=1 if the person has the condition, x=0 if the person does not have that condition. I would like to export the number of x=1's for each condition into one excel file. Basically, for each medical condition DX, I would like to run -tab DX- and send the DX=1's to an excel table for each of the DX health conditions.
as an example, here is the output from two -tab- command for congestive heart failure and diabetes:
.- tab chf-
chf | Freq. Percent Cum.
------------+-----------------------------------
0 | 146,033 95.22 95.22
1 | 7,330 4.78 100.00
------------+-----------------------------------
Total | 153,363 100.00
-tab dm_elix-
dm_elix | Freq. Percent Cum.
------------+-----------------------------------
0 | 47,730 31.12 31.12
1 | 105,633 68.88 100.00
------------+-----------------------------------
Total | 153,363 100.00
I would like to do this -tab- command for each health condition and report the name of the condition (CHF), frequency (7,330) and percent (4.78) and sent to an excel sheet as 1 table.
The final table would look like this (fictitious values):
| Medical condition | Number of people | percent from total number of participants |
| CHF | 7,330 | 4.78% |
| DM | 105633 | 68.9% |
| DX | xx | xx |
I have tried using tab2xl but that puts each -tab- output into a different sheet in excel and also include the x=0 counts
I dont know how to specify the 2nd row (counts of x=1) in a matrix -putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N))-
any feed back would be appreciated.
thanks
Vishal
here is truncated data file that represents my starting point from -dataex-:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float ID byte(chf arrhyth valvedx pulmcirc pvd htn paralysis neurologic pulmdx dm_elix) 1 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0 0 0 1 0 0 4 0 1 0 0 0 1 0 1 1 0 5 0 0 0 0 0 0 0 0 0 0 6 0 1 0 0 0 0 0 0 0 0 7 0 0 0 0 0 1 0 0 0 0 8 0 0 0 0 0 1 0 1 1 1 9 0 0 0 0 0 0 0 0 0 0 10 0 0 0 0 0 1 0 0 0 1 11 0 0 0 0 0 0 0 0 1 0 12 0 0 0 0 0 0 0 1 0 0 13 0 0 0 0 0 1 0 1 0 0 14 0 0 0 0 0 1 0 0 0 1 15 0 0 0 0 0 0 0 1 1 0 16 1 1 0 0 0 1 0 1 0 0 17 0 0 0 0 0 0 0 1 0 0 18 0 0 0 0 0 1 0 1 0 0 19 0 0 0 0 0 0 0 1 0 0 20 0 0 0 0 0 1 0 1 0 0 21 0 0 0 0 0 1 0 1 1 0 22 0 0 0 0 0 0 0 0 0 0 23 0 0 0 0 0 0 0 0 0 0 24 0 0 0 0 0 1 0 1 1 0 25 0 0 0 0 0 1 0 0 0 1 26 0 0 0 0 0 1 0 1 1 0 27 0 0 0 0 0 0 0 0 0 0 28 0 0 0 0 0 1 0 0 1 0 29 0 0 0 0 0 0 0 0 0 0 30 0 0 0 0 0 0 0 1 0 0 31 0 0 0 0 0 1 0 1 0 1 32 0 1 0 0 1 1 0 0 1 0 33 0 0 0 0 0 0 0 1 0 0 34 0 0 0 0 0 1 0 1 0 0 35 0 0 0 0 0 0 0 1 0 0 36 0 0 0 0 0 1 0 1 1 0 37 0 1 0 0 0 0 0 0 0 0 38 0 0 0 0 0 1 0 0 0 0 39 0 0 0 0 0 0 0 1 0 0 40 0 1 0 0 0 0 0 1 0 0 41 0 0 0 0 0 0 0 0 0 0 42 0 0 0 0 0 0 0 1 0 0 43 0 0 0 0 0 1 0 1 0 0 44 0 1 0 0 0 1 0 0 0 1 45 0 0 0 0 0 1 0 0 0 0 46 0 0 0 0 0 0 0 0 0 0 47 0 0 0 0 0 0 0 1 0 0 48 0 0 0 0 0 1 0 0 0 0 49 0 0 0 0 0 0 0 0 0 0 50 0 0 0 0 0 0 0 0 0 0 51 1 1 1 0 1 1 0 1 0 1 52 0 0 0 0 0 0 0 0 0 1 53 0 1 0 0 0 1 0 0 0 0 54 0 0 0 0 0 0 0 1 0 0 55 0 0 0 0 0 0 0 0 0 0 56 0 0 0 0 0 1 0 0 0 1 57 0 0 0 0 0 0 0 1 0 0 58 1 1 0 0 0 1 0 1 0 1 59 0 0 0 0 0 1 0 0 0 0 60 0 0 0 0 0 0 0 1 0 0 61 0 0 0 0 0 0 0 0 0 0 62 0 1 0 0 0 1 0 1 0 1 63 0 0 0 0 0 0 0 1 1 0 64 1 1 0 0 0 1 0 1 1 0 65 0 0 0 0 0 1 0 0 0 0 66 0 0 0 0 0 1 0 1 0 0 67 0 0 0 0 0 1 0 0 0 1 68 0 1 0 0 0 0 0 1 0 0 69 0 0 0 0 0 0 0 1 0 0 70 0 0 0 0 0 0 0 1 0 0 71 0 0 0 0 0 0 0 0 0 1 72 0 0 0 0 0 0 0 1 0 0 73 0 0 0 0 0 0 0 1 0 0 74 0 0 0 0 0 0 0 1 0 0 75 0 0 0 0 0 1 0 1 0 1 76 0 0 0 0 0 0 0 1 1 0 77 0 1 0 0 0 1 0 0 0 1 78 0 0 0 0 0 0 0 1 0 1 79 0 0 0 0 0 0 0 0 0 0 80 0 0 0 0 0 0 0 1 0 0 81 0 1 0 0 0 0 0 0 0 1 82 0 1 0 0 0 1 0 1 0 0 83 0 0 0 0 0 0 0 0 0 0 84 0 0 0 0 0 0 0 1 0 0 85 0 0 0 0 0 0 0 1 0 0 86 0 0 0 0 0 0 0 0 0 0 87 0 0 0 0 0 0 0 0 0 0 88 0 0 0 0 0 0 0 0 0 0 89 1 0 0 0 0 1 0 1 1 0 90 0 0 0 0 0 0 0 0 0 0 91 0 0 0 0 0 1 0 0 0 0 92 0 0 0 0 0 0 0 1 0 0 93 0 0 0 0 0 0 0 0 0 0 94 0 0 0 0 0 0 0 0 0 0 95 0 0 0 0 0 0 0 1 0 0 96 1 1 0 0 0 0 0 0 0 0 97 0 0 0 0 0 0 0 0 0 0 98 0 0 0 0 0 0 0 1 1 0 99 0 0 0 0 0 1 0 0 0 1 100 0 0 0 0 0 0 0 0 0 0 101 0 0 0 0 0 0 0 0 0 1 102 0 0 0 0 0 0 0 1 0 0 103 0 0 0 0 0 0 0 1 0 0 104 0 0 0 0 0 0 0 0 0 0 105 0 0 0 0 0 0 0 0 0 0 106 0 0 0 0 0 0 0 0 0 0 107 0 0 0 0 0 0 0 1 0 0 108 0 0 0 0 0 0 0 0 0 0 109 0 0 0 0 0 0 0 1 0 0 110 0 0 0 0 0 0 0 1 0 0 111 0 1 0 0 0 1 0 1 1 0 112 0 0 0 0 0 0 0 0 0 0 113 0 1 1 0 0 1 0 1 0 0 114 0 0 0 0 0 0 0 0 0 0 115 0 1 0 0 0 0 0 1 1 0 116 0 0 0 0 0 0 0 0 0 0 117 0 0 0 0 0 0 0 1 1 0 118 0 0 0 0 0 0 0 1 0 0 119 0 0 0 0 0 1 0 0 0 0 120 0 1 0 0 0 1 0 0 1 0 121 0 0 0 0 0 1 0 1 0 0 122 0 0 0 0 0 0 0 0 0 0 123 0 0 0 0 0 1 0 0 0 1 124 1 1 0 0 0 1 0 1 0 0 125 0 0 0 0 0 0 0 1 0 0 126 0 0 0 0 0 0 0 0 0 0 127 0 0 0 0 0 0 0 0 0 1 128 0 1 0 0 0 1 0 1 0 0 129 0 0 0 0 0 1 0 1 0 0 130 0 0 0 0 0 0 0 0 1 0 131 0 0 0 0 1 1 0 1 0 1 132 0 1 0 0 0 1 0 1 1 0 133 0 0 0 0 0 0 0 1 0 0 134 0 1 0 0 0 1 0 1 0 0 135 0 0 0 0 0 0 0 1 0 0 136 0 0 0 0 0 0 0 1 0 0 137 0 0 0 0 0 0 0 0 0 0 138 0 0 0 0 0 0 0 1 0 0 139 0 0 0 0 0 0 0 0 0 0 140 0 1 0 0 0 0 0 1 0 0 141 0 0 0 0 0 0 0 1 0 0 142 0 0 0 0 0 0 0 1 0 0 143 0 1 0 0 0 0 0 1 0 0 144 0 0 0 0 0 0 0 1 1 0 145 0 0 0 0 0 0 0 0 0 0 146 0 0 0 0 0 0 0 1 0 0 147 0 1 0 0 0 0 0 0 0 1 148 0 0 0 0 0 1 0 1 0 1 149 0 0 0 0 0 0 0 0 0 0 150 1 0 0 0 0 0 0 0 0 0 151 0 0 0 0 0 1 0 0 0 1 152 0 0 0 0 0 1 0 0 0 1 153 0 0 0 0 0 0 0 0 0 0 154 0 1 0 0 1 1 0 1 1 0 155 1 1 0 0 1 1 0 1 1 1 156 0 0 0 0 0 0 0 1 0 0 157 0 0 0 0 0 0 0 1 0 0 158 0 0 0 0 0 0 0 1 0 0 159 0 0 0 0 0 0 0 0 0 0 160 0 0 0 0 0 0 0 1 0 0 161 0 0 0 0 0 0 0 0 1 0 162 0 0 0 0 0 0 0 1 0 0 163 0 0 0 0 0 0 0 0 0 0 164 0 0 0 0 0 0 0 1 1 0 165 0 0 0 0 0 0 0 0 0 0 166 0 0 0 0 0 1 0 1 0 0 167 0 0 0 0 0 0 0 1 0 0 168 0 0 0 0 0 0 0 0 0 0 169 0 1 0 0 0 0 0 1 1 0 170 0 0 0 0 0 0 0 0 0 0 171 0 1 1 0 0 1 0 1 0 1 172 0 0 0 0 0 0 0 1 1 0 173 0 0 0 0 0 0 0 0 0 0 174 0 0 0 0 0 0 0 1 0 0 175 0 1 0 0 0 0 0 0 0 0 176 0 0 0 0 0 1 0 0 0 0 177 0 0 0 0 0 1 0 0 0 0 178 0 1 0 0 0 0 0 1 0 0 179 0 1 0 0 0 1 0 1 1 0 180 0 0 0 0 0 0 0 0 0 0 181 0 0 0 0 0 1 0 0 0 0 182 0 0 0 0 0 0 0 0 0 0 183 0 0 0 0 0 0 0 0 0 0 184 0 0 0 0 0 1 0 0 0 1 185 0 0 0 0 0 1 0 1 0 1 186 0 0 0 0 0 0 0 0 1 0 187 0 1 0 0 0 0 0 0 0 0 188 0 0 0 0 0 0 0 0 0 0 189 0 0 0 0 0 0 0 1 1 0 190 0 0 0 0 0 1 0 1 1 0 191 0 1 0 0 0 0 0 1 1 0 192 0 0 0 0 0 0 0 0 1 0 193 0 0 0 0 0 0 0 1 0 0 194 0 0 0 0 0 1 0 1 0 1 195 0 0 0 0 0 0 0 0 0 0 196 0 1 0 0 0 0 0 1 0 1 197 0 0 0 0 0 0 0 0 0 0 198 0 1 0 1 0 0 0 1 1 0 199 0 0 0 0 0 0 0 0 0 0 200 0 0 0 0 0 1 0 0 1 0 201 0 0 0 0 0 0 0 0 0 0 202 0 0 0 0 0 0 0 1 0 0 203 1 0 0 0 0 1 0 1 0 0 204 0 0 0 0 0 1 0 0 0 1 205 0 0 1 0 0 1 0 0 0 1 206 0 0 0 0 0 1 0 1 0 1 207 0 1 0 0 0 1 0 0 1 0 208 0 0 0 0 0 0 0 0 0 0 209 0 0 0 0 0 0 0 1 0 0 210 0 0 0 0 0 0 0 0 0 0 211 1 1 0 0 0 0 0 0 0 1 212 0 0 0 0 0 0 0 0 1 0 213 0 0 0 0 0 0 0 1 0 0 214 0 1 0 0 1 0 0 0 1 0 215 0 0 0 0 0 1 0 1 0 1 216 0 0 0 0 0 0 0 1 0 0 217 0 0 0 0 0 0 0 1 0 0 218 0 0 0 0 0 0 0 1 0 0 219 0 0 0 0 0 0 0 0 0 0 220 0 0 0 0 0 1 0 0 0 0 221 0 0 0 0 0 1 0 1 1 0 222 0 0 0 0 0 0 0 1 0 0 223 0 0 0 0 0 0 0 0 0 0 224 0 0 0 0 0 0 0 0 1 0 225 0 0 0 0 0 0 0 1 0 0 226 0 0 0 0 0 0 0 1 0 0 227 0 0 0 0 0 1 0 0 0 0 228 1 1 0 0 0 1 0 0 0 1 229 0 0 0 0 0 0 0 1 0 0 230 0 0 1 0 0 0 0 1 0 0 231 0 0 0 0 0 0 0 1 0 0 232 0 0 0 0 0 1 0 1 0 0 233 0 0 0 0 0 1 0 0 0 0 234 0 0 0 0 0 1 0 1 0 1 235 0 0 0 0 0 0 0 0 0 0 236 0 1 1 0 0 1 0 1 1 0 237 0 0 0 0 0 0 0 0 0 0 238 0 0 0 0 0 0 0 1 0 0 239 0 0 0 0 0 0 0 0 0 0 240 0 0 0 0 0 1 0 0 0 0 241 1 1 0 1 0 1 0 1 1 0 242 0 0 0 0 0 0 0 1 0 0 243 0 1 0 0 0 1 0 0 0 0 244 0 0 0 0 0 0 0 0 1 0 245 0 0 0 0 0 0 0 0 0 0 246 0 0 0 0 0 1 0 0 1 0 247 0 0 0 0 0 0 0 0 0 0 248 0 0 0 0 0 0 0 0 0 0 249 1 0 0 0 0 0 0 1 1 1 250 0 0 0 0 0 1 0 1 0 0 251 0 0 0 0 0 0 0 1 0 0 252 0 0 0 0 0 0 0 1 1 0 253 0 0 0 1 0 1 0 1 1 1 254 0 0 0 1 0 0 0 1 0 0 255 0 1 0 0 0 1 0 1 1 0 256 0 0 0 1 0 0 0 1 0 0 257 0 0 0 0 0 0 0 0 0 0 258 0 1 0 0 0 1 0 1 1 1 259 0 0 0 0 0 0 0 1 0 0 260 0 0 0 0 0 0 0 0 0 0 261 0 0 0 0 0 0 0 0 0 0 262 0 1 0 0 0 0 0 1 1 1 263 0 0 0 0 0 0 0 0 0 0 264 0 1 1 0 1 1 0 1 1 0 265 0 1 0 0 0 1 0 1 0 1 266 0 0 0 0 0 0 0 1 1 0 267 0 1 0 0 0 1 0 1 0 0 268 0 0 0 0 0 0 0 0 0 0 269 0 0 0 0 0 1 0 1 0 0 270 0 0 0 0 0 0 0 1 0 0 271 1 0 0 0 0 0 0 1 0 0 272 0 0 0 0 0 1 0 1 0 0 273 0 0 0 0 0 0 0 0 0 0 274 0 0 0 0 0 0 0 1 0 0 275 0 0 0 0 0 0 0 0 0 0 276 0 1 0 0 0 1 0 1 0 0 277 0 0 0 0 0 0 0 0 0 0 278 0 0 0 0 0 0 0 0 0 0 279 0 1 0 0 0 0 0 0 0 0 280 0 1 0 0 0 1 0 1 0 0 281 0 0 0 0 0 0 0 0 0 0 282 0 1 0 0 0 0 0 1 0 0 283 0 0 0 0 0 0 0 1 0 0 284 0 0 0 0 0 0 0 1 1 0 285 0 0 0 0 0 1 0 1 1 0 286 0 1 0 0 0 1 0 0 0 0 287 0 0 0 0 0 0 0 0 0 0 288 0 0 0 0 0 0 0 0 0 0 289 0 0 0 0 0 1 0 1 0 0 290 0 0 0 0 0 1 0 1 1 1 291 0 0 0 0 0 0 0 0 0 0 292 0 0 0 0 0 0 0 0 0 0 293 0 0 0 0 0 0 0 1 0 0 294 0 1 0 0 0 1 0 1 0 0 295 0 0 0 0 0 1 0 0 0 1 296 0 1 0 0 0 0 0 0 0 0 297 0 1 0 0 0 1 0 0 0 0 298 0 0 0 0 1 1 0 1 0 0 299 0 0 0 0 0 0 0 0 0 0 300 0 0 0 0 0 0 0 0 0 0 end
0 Response to exporting multiple tables to 1 table in excel
Post a Comment