Error while using putexcel to export t test results to excel

[Titir Bhattacharya]

Hi all,
Please consider the following data:

* Example generated by -dataex-. For more info, type help dataex
clear
input long airport_n double year str9 MSOACD float(meanage medianage ageUnder17_pct notdeprived_pct deprived_1_pct deprived_2_pct deprived_3_pct deprived_4_pct deprived_pct treatMSOA)
1 2011 "E02001610" 42.1 43 20.601795  47.43844 31.955845 17.520521 3.0285876  .05660911  52.56156 .
1 2011 "E02001651" 43.4 46  22.96087  61.49425 28.554144  9.165154  .7864489          0  38.50575 .
1 2011 "E02001665" 43.5 46   22.8683  60.78018  29.49394  8.803373  .8961518 .026357407  39.21982 .
1 2011 "E02001674" 45.6 47 17.267923  47.02869 31.326845 18.263319 3.1762295  .20491803  52.97131 .
1 2011 "E02001676" 43.5 45  21.29353   52.2673  30.31026 14.717582  2.625298   .0795545   47.7327 .
1 2011 "E02001678" 45.7 48  20.28349  56.33057  30.40976 11.418048 1.8416207          0  43.66943 .
1 2011 "E02001679" 40.3 40 22.270046 33.012287   31.3185 26.502823   8.30289   .8635005  66.98772 .
1 2011 "E02001680" 39.7 39 21.560307  23.10884 34.982445  30.06703 11.107565   .7341207  76.89116 .
1 2011 "E02001681" 44.6 46  19.72114  53.06123  30.82233 13.445378 2.4909964  .18007202  46.93877 .
1 2011 "E02001827" 43.5 45  20.82713  48.60357 31.574863 17.145073  2.404965   .2715283  51.39643 0
1 2011 "E02001828" 45.4 46 19.104046  46.03629 34.161095 17.414837 2.2604265  .12734798  53.96371 0
1 2011 "E02001829" 40.6 42 24.602175  58.53163  29.01915 11.375508  .9866512  .08705746  41.46837 0
1 2011 "E02001830" 45.6 48  18.41687  55.58928 32.119373 10.824482 1.3657056  .10116338  44.41072 0
1 2011 "E02001831" 38.9 39  23.98799  39.66811 31.620823  21.80041  6.137759   .7729029  60.33189 0
1 2011 "E02001832" 41.3 43 21.645445  52.63311   31.7428 13.267385 2.0075648   .3491417  47.36689 0
1 2011 "E02001833" 41.5 43 21.194904  45.19231  33.21678   18.0507  3.277972  .26223776  54.80769 0
1 2011 "E02001834" 36.4 35 26.307217 28.790323 34.032257  26.89516  9.314516   .9677419  71.20968 0
1 2011 "E02001835" 40.8 41  19.97309  52.37414 29.490847 14.845538   2.91762   .3718536  47.62586 0
1 2011 "E02001836" 41.8 44  20.61002  49.97277  32.08061 15.277778 2.4782135   .1906318  50.02723 0
1 2011 "E02001837" 34.4 32 29.749645 21.547096  31.94241 31.063934 13.836018  1.6105417   78.4529 0
1 2011 "E02001838" 41.9 44  20.60472  53.73134 30.694355 14.243998 1.1356262   .1946788  46.26866 1
1 2011 "E02001839" 40.3 40  22.13598  34.55516 33.096085  25.05338  6.619217   .6761566  65.44484 0
1 2011 "E02001840"   36 34  28.40041  24.95817 29.364195 31.344116  12.99498  1.3385388  75.04183 0
1 2011 "E02001841"   45 48 18.821959  56.54515  31.69014 10.687655  .9942005  .08285004  43.45485 1
1 2011 "E02001842" 39.1 40 22.980556  40.47619  34.52381 20.304234 4.5634923  .13227513  59.52381 0
1 2011 "E02001843" 39.8 40 21.518204 37.928493 34.353115  22.88979 4.6811647  .14743826  62.07151 0
1 2011 "E02001844" 42.6 42 17.877728  42.18435 31.128056  21.08096  5.001122   .6055169  57.81565 1
1 2011 "E02001845" 36.2 36  25.27893  42.56311 31.339806  21.39806  4.504854  .19417475  57.43689 0
1 2011 "E02001846" 36.9 36   25.0479 28.937183 30.247097  28.72879  10.86633  1.2206013  71.06282 0
1 2011 "E02001847" 43.6 45  18.72849  43.30971 32.596916 20.925386  3.042935  .12505211  56.69029 1
1 2011 "E02001848" 35.4 33 28.499685  29.35167   33.5167 26.719057  9.469548   .9430255  70.64833 0
1 2011 "E02001849" 35.6 34 24.324324  34.35067 32.482094 23.108067  9.156026   .9031454  65.64933 1
1 2011 "E02001850" 36.8 34  24.99694 36.972866  34.35763  23.07944  5.230467   .3595946  63.02713 0
1 2011 "E02001851" 39.1 39  22.13153  37.94872   30.5803 24.102564  6.720648   .6477733  62.05128 1
1 2011 "E02001852" 37.5 36 23.105135 33.829548 31.001116  26.31187  8.001489   .8559732 66.170456 0
1 2011 "E02001855"   38 37  26.28623  22.27455  33.90629  32.46998 10.689898   .6592889  77.72545 1
1 2011 "E02001856"   34 32 24.659304 36.105476 33.903217  22.45726  6.780643   .7534048  63.89452 0
1 2011 "E02001857" 34.5 32  24.60084  30.82397 33.052433  24.68165 10.131086  1.3108615 69.176025 1
1 2011 "E02001858" 34.3 31 22.465397 30.230326 34.516956 25.975687  7.869482  1.4075496  69.76968 0
1 2011 "E02001859" 29.6 25  28.41325  24.87142 34.937546 29.684055  9.515062   .9919177 75.128586 0
1 2011 "E02001860" 32.6 30 28.355524  23.21178  37.72791  28.19074  9.957924   .9116409  76.78822 0
1 2011 "E02001861" 30.3 27 31.876783 23.259493 36.329113  28.79747 10.443038   1.170886  76.74051 0
1 2011 "E02001862" 32.7 30 29.523424 23.314354  33.74288  30.29667  11.20767  1.4384178  76.68565 0
1 2011 "E02001863"   30 27 34.991707 16.340425  32.97872  33.87234 14.765958  2.0425532  83.65958 0
1 2011 "E02001865" 31.8 29  30.28723  20.09627  35.22864  31.04693  12.63538   .9927798  79.90373 0
1 2011 "E02001866" 30.3 27 32.798985  18.98292  33.88975  32.10404 13.354037  1.6692547  81.01708 0
1 2011 "E02001867" 29.7 26  36.57441 17.196531  32.70713 34.248554  14.16185  1.6859345  82.80347 0
1 2011 "E02001868" 34.6 31  30.71835  34.05385   35.8741 24.725067   5.04361    .303375  65.94615 1
1 2011 "E02001869" 30.7 28 34.521454  17.01603 35.881626 31.442663 13.933415   1.726264  82.98397 1
1 2011 "E02001870" 31.4 29 34.584774 24.783216  35.32867  29.11888  9.902098   .8671328  75.21678 1
1 2011 "E02001871" 39.3 38  23.74045 24.216766 31.950325  31.27293  11.17697  1.3830087  75.78323 1
1 2011 "E02001872" 37.1 35 26.490713        25 29.347826 33.280052 10.965473  1.4066496        75 1
1 2011 "E02001873" 31.3 29 28.963356  24.48721  35.04685 28.032413   10.9648  1.4687263  75.51279 0
1 2011 "E02001874" 28.8 25 36.698032  16.62404 33.649982 32.700035 14.906833  2.1191084  83.37596 1
1 2011 "E02001875"   29 27  31.45201  22.97794 37.561275  26.65441  11.15196  1.6544118  77.02206 0
1 2011 "E02001876" 25.5 21 16.953564 18.718113  40.84821  28.50765  10.36352     1.5625  81.28189 1
1 2011 "E02001877" 28.3 25 37.888317 17.614231 33.937916  32.08929 14.719218  1.6393442  82.38577 1
1 2011 "E02001878" 28.7 26 35.857063 21.832287  32.47137 29.996305  13.70521  1.9948282  78.16771 1
1 2011 "E02001879" 30.9 28  28.69121 25.071316  33.91442  28.30428 11.125198   1.584786  74.92868 0
1 2011 "E02001880" 31.3 29  34.35446  22.24777 34.147743  29.92239  12.33113   1.350963  77.75223 1
1 2011 "E02001881" 29.5 27  36.87594 18.416582 32.993546 32.279987 14.576962  1.7329255  81.58342 1
1 2011 "E02001882" 36.4 34 26.091757 26.812977 32.919846   29.3257  9.541985   1.399491  73.18703 1
1 2011 "E02001883" 36.9 35  25.26756  23.51314 32.752422 31.701244  10.45643  1.5767635  76.48686 1
1 2011 "E02001884" 26.3 23  41.97424 21.332243 34.859013 31.099306 11.524316  1.1851246  78.66776 1
1 2011 "E02001886" 34.3 31 21.764305  36.24265 35.406994  20.33426  6.994739  1.0213556  63.75735 0
1 2011 "E02001888" 36.5 35   26.8769 26.916666 33.041668 29.166666  9.833333  1.0416666 73.083336 1
1 2011 "E02001889" 29.5 26 36.700214 20.079086 33.304043 33.128296 12.434094  1.0544815  79.92091 1
1 2011 "E02001890" 36.2 31 15.531307  44.44944 32.179775 17.191011  5.325843   .8539326  55.55056 0
1 2011 "E02001892" 31.3 29  33.89581 21.590265 35.172607  28.94737 12.507074  1.7826825  78.40974 1
1 2011 "E02001893" 38.7 38  24.39175  33.20707 33.270203   26.0101  6.755051   .7575758  66.79293 1
1 2011 "E02001895" 39.1 38   24.9294  24.11783  31.48205 33.169685 10.064437  1.1660018  75.88217 1
1 2011 "E02001896" 29.2 26 35.522774 18.331957  34.72337 31.420313 13.377374   2.146986  81.66805 1
1 2011 "E02001897" 31.6 28 31.371775  14.69127  31.68914 34.315117 16.926899  2.3775728  85.30873 1
1 2011 "E02001898" 41.1 42  21.53206  38.47598 34.234234  22.52252  4.466967   .3003003  61.52402 1
1 2011 "E02001899" 41.4 41 19.603355  55.41045  27.61194 14.521144 2.2077115   .2487562  44.58955 0
1 2011 "E02001900" 34.9 30 19.305984      36.8  31.27619 22.857143   7.92381  1.1428572      63.2 0
1 2011 "E02001901"   38 34 16.805872  59.26027 24.476475  12.91814  3.154746   .1903726  40.73973 0
1 2011 "E02001902" 38.1 38 23.766596  37.99759 33.252914  22.43667  5.950945   .3618818  62.00241 1
1 2011 "E02001903" 28.6 25 36.460526  14.53373  33.43254 34.275795 16.071428   1.686508  85.46627 1
1 2011 "E02001904" 33.5 31 28.462315  29.83193 35.752483  24.21696  9.052712  1.1459129  70.16807 1
1 2011 "E02001905" 30.6 21 12.124331  55.00388 32.583397   9.73623 2.1722264  .50426686  44.99612 0
1 2011 "E02001906" 38.9 39 23.628555  40.78462 33.175354  20.32649   5.21327   .5002633  59.21538 0
1 2011 "E02001907" 40.9 41 20.557056 36.111786 34.289185 23.572296  5.662211     .36452  63.88821 1
1 2011 "E02001908" 28.4 25 36.270767 16.299356 34.389347  32.04775 14.921947  2.3415978  83.70065 1
1 2011 "E02001909" 29.4 26   34.6857 20.278503 31.288076  30.11314 16.536118    1.78416   79.7215 1
1 2011 "E02001910" 30.4 27  33.60967  21.20172 32.274677  31.03004 13.218884  2.2746782  78.79828 1
1 2011 "E02001911" 34.2 32  29.22495 30.539644 32.107327  26.89177  9.436237  1.0250226  69.46036 0
1 2011 "E02001913" 37.5 32  15.65544  41.16753  31.40772 20.036486  6.688963   .6993007  58.83247 0
1 2011 "E02001914" 34.2 31 26.067747  44.19135 29.202734  19.08884  6.970387    .546697  55.80865 1
1 2011 "E02001915" 36.8 35 22.621014 37.118713  31.94559 22.753504  7.440231   .7419621  62.88129 1
1 2011 "E02001916" 33.1 30  30.99164  27.06645 34.100487 27.682333 10.016208  1.1345218  72.93355 1
1 2011 "E02001918" 37.7 35 18.434568  41.68096 28.865475  19.67655  8.502818  1.2741975  58.31904 1
1 2011 "E02001919"   37 34  20.18239  38.75762  34.64177 18.978659  6.821646   .8003049  61.24238 1
1 2011 "E02001920" 36.9 36  26.32134  27.64128 33.630222 27.579853  9.889435  1.2592138  72.35872 0
1 2011 "E02001921" 36.8 35  24.98404 35.485836 30.642704 24.459335  8.620165   .7919586  64.51416 0
1 2011 "E02001922" 24.1 21  4.640316  42.51837  41.64997  12.52505  3.006012   .3006012  57.48163 0
1 2011 "E02001923" 34.6 31 29.004494 30.946745  35.14793 26.301775  6.952663   .6508875  69.05325 1
1 2011 "E02001924" 37.3 36  25.52664   33.6394 31.343906  26.46077  8.013355  .54257095 66.360596 1
1 2011 "E02001925"   39 36 18.402155  48.00853  30.90327 16.145092 4.5519204   .3911807  51.99147 0
1 2011 "E02001926" 36.8 31 16.287773  48.67444 30.222694 16.578297 4.1710854   .3534818  51.32556 0
end
label values airport_n airport_n
label def airport_n 1 "Birmingham", modify

I want to export following t test results to excel using putexcel

local excelFile "$airports\ttest_results_2011Census.xlsx"
putexcel clear


*age*
putexcel set `excelFile', sheet("Age") modify
putexcel A1 = ("Variable") B1 = ("t-statistic") C1 = ("p-value") D1 = ("Degrees of Freedom")


foreach var of varlist meanage medianage ageUnder17_pct{
    
    ttest `var', by(treatMSOA) unequal
    
    local pvalue = r(p)
    local tstat = r(t)
    local df = r(df)
    
    

    putexcel A`=_n+1' = "`var'" B`=_n+1' = `tstat' C`=_n+1' = `pvalue' D`=_n+1' = `df', modify

    
}



*deprivation*
putexcel set `excelFile', sheet("Deprivation") modify
putexcel A1 = ("Variable") B1 = ("t-statistic") C1 = ("p-value") D1 = ("Degrees of Freedom")


foreach var of varlist notdeprived_pct deprived_1_pct deprived_2_pct deprived_3_pct deprived_4_pct deprived_pct {
    ttest `var', by(treatMSOA) unequal
    
    local pvalue = r(p)
    local tstat = r(t)
    local df = r(df)
    
    
    putexcel A`=_n+1' = "`var'" B`=_n+1' = `tstat' C`=_n+1' = `pvalue' D`=_n+1' = `df', modify
    

}

I get error

invalid 'queries'

after

putexcel set `excelFile', sheet("Age") modify

Please suggest solution.

Thanks!

[Titir Bhattacharya]

I modified the code with this:

putexcel set "$airports\ttest_results_2011Census.xlsx", sheet("Age") modify
putexcel A1 = ("Variable") B1 = ("t-statistic") C1 = ("p-value") D1 = ("Degrees of Freedom")
foreach var of varlist meanage medianage ageUnder17_pct{
    
    ttest `var', by(treatMSOA) unequal
    
    local pvalue = r(p)
    local tstat = r(t)
    local df = r(df)
    
    

putexcel A`=_n+1' = "`var'" B`=_n+1' = `tstat' C`=_n+1' = `pvalue' D`=_n+1' = `df'

    
}

Now the error is resolved but only the last variable in the loop is exported to the sheet and the df are not appearing. I do not use any replace, so not sure what is going wrong.