Hello,

I have a dataset (example data below) that contains, by area code (lsoacode), the travel distances and durations to hospitals that provide certain services. These services are variable names beginning with NCB* in the data. They are dummy variables, where 1 is where a hospital provides a service and 0 where a hospital does not (hospital code not shown). There are 200 of these service codes beginning with NCB*

What I need to do is create the mean for traveldist & traveldur BY lsoacode for each of the NCB* codes, where they are 1. I would like this to be in a new .dta table, as I need to append to a larger dataset to use in a regression.

I have the following code:

frame create meantravel str10(NCB_code) meantravel_dist meantravel_dur

foreach var of varlist ncb* {
capture tabstat traveldist traveldur if `var' ==1 by (lsoacode), save
mat matA = r(StatTotal)
if _rc==0 {
frame post meantravel ("`var'") (matA[1,1]) (matA[1,2])
}
else {
frame post meantravel ("`var'") (.) (.)
}
}

frame change meantravel


However, I need to amend this so that the results are by lsoacode, I have not been able to amend how you alter the code to accommodate this.

Can anyone advise please?


Code:
* Example generated    by -dataex-. To install: ssc    install dataex
clear
input str9 lsoacode    double(traveldist traveldur)    byte(NCBPS01C NCBPS01J)
"E01017810" 298.712    3.0844444444444443 0 1
"E01001547" 315.573    3.271666666666667 0 0
"E01012673"  83.051    .8836111111111111 1 0
"E01000978" 339.149    3.837222222222222 0 0
"E01012363" 190.067    1.9341666666666666 0 0
"E01001964" 304.718    3.2480555555555553 0 1
"E01015304" 354.473    4.173055555555555 1 0
"E01030867"  366.75    3.843611111111111 0 0
"E01025548"   75.05    .8477777777777777 0 0
"E01014176"  94.392    1.1572222222222224 0 0
"E01024137" 347.671    3.6177777777777775 0 0
"E01005843"  13.501    .28277777777777774 0 0
"E01020604" 223.914    2.357222222222222 0 0
"E01000804" 337.629    3.8072222222222223 0 0
"E01005350"   28.04    .3572222222222222 0 0
"E01016938"  420.15    4.336111111111111 0 0
"E01032439" 129.561    1.596111111111111 0 1
"E01007111"  60.891    .7661111111111112 0 0
"E01023611" 267.998    2.8827777777777777 0 0
"E01013621" 156.655    1.8022222222222224 0 1
"E01024043" 424.268    4.370555555555556 0 0
"E01000372"  351.51    3.6819444444444445 0 0
"E01030164" 388.062    3.9699999999999998 0 0
"E01001984" 303.793    3.2375 0 0
"E01002483"  294.46    3.108611111111111 0 0
"E01007026"  62.788    .6872222222222223 0 0
"E01007660"  83.212    1.076111111111111 0 0
"E01000042" 322.333    3.525 0 0
"E01027181" 214.069    2.3133333333333335 0 0
"E01000696" 375.944    3.902222222222222 0 0
"E01021691" 350.732    3.8447222222222224 0 0
"E01024600" 405.477    4.2075 0 0
"E01015402" 372.422    4.300277777777778 0 0
"E01027386" 331.383    3.5394444444444444 0 0
"E01004155" 353.849    3.7275 0 0
"E01033723" 370.323    3.9413888888888886 0 0
"E01000978" 328.558    3.711111111111111 0 0
"E01018260" 276.567    2.906388888888889 0 0
"E01001921" 325.054    3.4983333333333335 0 0
"E01009254" 138.561    1.5619444444444446 0 0
"E01017614" 261.645    2.722777777777778 0 0
"E01005505"  28.183    .3433333333333334 0 0
"E01017793" 294.177    3.2230555555555553 0 0
"E01000548" 303.323    3.201388888888889 0 0
"E01014169" 101.028    1.2872222222222223 0 0
"E01031970" 330.844    3.790833333333333 0 0
"E01000633" 312.413    3.3222222222222224 0 0
"E01009271"  121.91    1.3905555555555555 0 0
"E01001599"  328.99    3.7427777777777775 0 0
"E01007559" 136.772    1.4486111111111113 0 0
"E01027693" 108.581    1.2388888888888887 0 0
"E01016326" 283.562    3.0300000000000002 0 0
"E01026364" 210.474    2.3802777777777777 0 0
"E01025489"  56.677    .6344444444444445 0 0
"E01009377" 130.287    1.5358333333333334 0 0
"E01021344" 361.715    3.749722222222222 0 0
"E01021533" 355.631    3.705833333333333 0 0
"E01003743" 331.274    3.5836111111111113 0 0
"E01028329" 116.863    1.483611111111111 0 0
"E01013045" 151.061    1.786388888888889 0 0
"E01024927"  43.763    .495 0 0
"E01002876"  319.61    3.3780555555555556 0 0
"E01014110" 111.587    1.43 0 0
"E01000024"  328.87    3.5425 0 0
"E01000402" 353.978    3.7305555555555556 0 0
"E01001545" 303.669    3.1802777777777775 0 0
"E01031895" 267.405    2.99 0 0
"E01028606"  278.86    2.9658333333333333 0 0
"E01001548" 303.044    3.186388888888889 0 0
"E01000076" 336.456    3.638611111111111 0 0
"E01006528"  56.044    .6627777777777778 0 0
"E01009229" 122.458    1.4202777777777778 0 0
"E01001596" 329.738    3.7597222222222224 0 0
"E01029537"  56.318    .6858333333333333 0 0
"E01001984" 305.197    3.2105555555555556 0 0
"E01032131" 146.806    1.6108333333333333 0 0
"E01014233"   66.15    .8244444444444444 0 0
"E01023390"  277.72    2.8897222222222223 0 0
"E01023608" 267.442    2.894722222222222 0 0
"E01009981" 120.409    1.3494444444444444 0 0
"E01017377" 264.779    2.8183333333333334 0 0
"E01003027" 316.776    3.5125 0 0
"E01009518" 132.775    1.5766666666666667 0 1
"E01000486" 298.003    3.1722222222222225 0 1
"E01026209" 169.322    1.9316666666666669 0 1
"E01018656"  20.746    .3538888888888889 0 0
"E01028069" 133.602    1.581388888888889 0 0
"E01033317" 347.902    3.5741666666666663 1 0
"E01005740"  25.343    .3108333333333333 0 0
"E01027544" 272.977    2.8408333333333333 0 0
"E01024523" 427.203    4.428055555555556 11
"E01009361" 125.904    1.396388888888889 0 0
"E01024691" 441.653    4.575833333333334 0 0
"E01029101" 312.134    3.2952777777777778 0 0
"E01006643"  46.611    .5019444444444444 0 1
"E01021390" 338.324    3.681388888888889 0 0
"E01001544" 300.143    3.175277777777778 0 0
"E01030150" 312.204    3.3902777777777775 0 0
"E01025221"  65.516    .6944444444444444 0 0
"E01015173" 443.881    4.635555555555555 1 0
end