/*miss value */ ********** ******************** ****************************** **************************************** ************************************************** ************************************************** // ************************************************** ******************** // ******************** capture program drop mtable1 program mtable1, rclass syntax [varlist(max=1)] [if] [in] [, CATvar(varlist) CONvar(varlist) MPutexcel EName(string) CATName(string) CATList(namelist) CATRow(real 5) CONName(string) CONList(namelist) CONRow(real 5) ] marksample touse tokenize `varlist' local exp "`1'" ************************************************** //Exposure variable ************************************************** display _newline(2)"********** The exposure variable **********" tab `exp', matrow(E) global rn=rowsof(E) ************************************************** //categorical variables ************************************************** ******************** //Number of categorical varaibles ******************** display _newline(2)"********** The categorical variables **********" local catvarnum: word count `catvar' *display as error "number of categorical variables = " `catvarnum' if `catvarnum'>0 & `catvarnum'!=. { ******************** // ******************** local j=1 foreach v in `catvar' { display _newline(1)"***** Categorical variable: `v'" tab `v' `1' if `touse', matcell(C`j') matrow(R`j') col local eachcatnum=rowsof(C`j') mat MA=C`j' mata : st_matrix("N", colsum(st_matrix("MA"))) mat P`j'=J(`eachcatnum', $rn, .) local cn= $rn * 2 mat K`j'=J(`eachcatnum', `cn', .) mat rowname K`j'= "`v'" foreach c of numlist 1/ $rn { foreach r of numlist 1/`eachcatnum' { local pp=(C`j'[`r',`c']/N[1, `c'])* 100 local pv:display %4.2f round(`pp', 0.0001) mat P`j'[`r', `c'] = `pv' } } ******************** //collect frequency ******************** local s=1 foreach c of numlist 1/ $rn{ foreach r of numlist 1/`eachcatnum' { mat K`j'[`r', `s']=C`j'[`r', `c'] } local s= `s'+2 } ******************** //collect percent ******************** local s=2 foreach c of numlist 1/ $rn { foreach r of numlist 1/`eachcatnum' { mat K`j'[`r', `s']=P`j'[`r', `c'] } local s= `s'+2 } mat F`j' = R`j', K`j' mat rowname F`j'= "`v'" local cname "Freq1 Percent1" foreach x of numlist 2/ $rn { local cname "`cname' Freq`x' Percent`x' " mat colname F`j'= Category `cname' } local j= `j' + 1 } local mylist "F1" if `catvarnum'>=2 { /* if more than one varaibles */ foreach x of numlist 2/ `catvarnum' { local mylist "`mylist' \ F`x'" } } mat F=`mylist' } /*if categorical variable not missing */ ************************************************** //continuous variables ************************************************** local cn= $rn * 3 display _newline(2)"********** The continuous variables **********" local convarnum: word count `convar' *display as error "Number of continuous variable = " = `convarnum' if `convarnum'!=0 { local i=1 foreach v in `convar' { display _newline(1) "***** Continuous variable: `v'" local nump=1 local mup=2 local sdp=3 mat M`i'=J(1, `cn', .) foreach r of numlist 1/ $rn { local cv=E[`r',1] summarize `v' if `exp'==E[`r',1] & `touse' local muv=r(mean) local muvv: display %10.4f round(`muv', 0.0001) local sdv=r(sd) local sdvv: display %10.4f round(`sdv', 0.0001) local nn=r(N) mat M`i'[1,`nump']=`nn' mat M`i'[1,`mup']=`muvv' mat M`i'[1,`sdp']=`sdvv' local nump=`nump'+3 local mup=`mup'+ 3 local sdp= `sdp'+ 3 } mat rowname M`i'= "`v'" local i= `i' +1 } local mylist "M1" if `convarnum'>=2 { /* if more than 1 continous variables specified */ foreach x of numlist 2/`convarnum' { local mylist "`mylist' \ M`x'" } } mat M=`mylist' local cname "Freq1 mean1 sd1" foreach x of numlist 2/ $rn { local cname "`cname' Freq`x' mean`x' sd`x' " mat colname M= `cname' } } /* connect to if continous not missing */ if "`mputexcel'"== "mputexcel" { if "`catname'" =="" { local catname "Categorical" } if "`ename'"=="" { local ename "mtable1" } putexcel set `ename', sheet(`catname') modify local excelcatrn=rowsof(F) local excelcatcn=colsof(F) local elist1num: word count `catlist' if `elist1num'==0 { display as error "Please specify the columns' name in Excel for categorical variables" } if `excelcatcn'!= `elist1num' { display as error "The number of columns for categorical variables should be `excelcatcn' but you specify `elist1num' columns for your Excel" } if `excelcatcn'== `elist1num' { local c=1 foreach v of local catlist { local m=`catrow' foreach r of numlist 1/`excelcatrn' { local vv=F[`r', `c'] putexcel `v'`m'=(`vv') local m= `m'+1 } local c=`c'+1 } } /* connect to if `excelcatcn'!= `elist' */ if "`conname'" =="" { local conname "Continuous" } putexcel set `ename', sheet(`conname') modify local excelconrn=rowsof(M) local excelconcn=colsof(M) local elist2num: word count `conlist' if `elist2num'==0 { display as error "Please specify the columns' name in Excel for continous variables" } if `excelconcn'!= `elist2num' { display as error "The number of columns for continous variables should be `excelcatcn' but you specify `elist2num' columns for your Excel" } if `excelconcn'== `elist2num' { local c=1 foreach v of local conlist { local m= `conrow' foreach r of numlist 1/`excelconrn' { local vv=M[`r', `c'] putexcel `v'`m'=(`vv') local m= `m'+1 } local c= `c'+1 } } /* connect to if `excelconcn'!= `elist2num' */ } if `catvarnum'!=0 { return matrix F=F } if `convarnum'!=0 { return matrix M=M } end