Need Help using putexcel command for tabulate in survey setting

[anwar dudekula]

Hi Everyone, I need help with exporting results from tabulate command by using putexcel command in survey setting I have a survey datasets with a string variable.I am trying to perform oneway tabulation and use putexcel My goal is to put the various string values (for the string variable) in column A and their weighted count in column B in excel sheet. To demonstrate it, I am using the nhanes dataset and the code is as follows
***************code Start*********************************
webuse nhanes2f, clear
decode race, gen(s_race) // we generate a string varaible for demonstration purpose
svy, subpop(sex): tabulate s_race, count missing format(%11.3g) // I wanted to use tabulate s_race

* Now I am trying to use the estimated results to put into the excel cells

mat x = e(b)'
mat y=e(Rows)'

putexcel A1=("STRING_VAR") B1=("COUNT") A2=matrix(e(Rows)') B2=matrix (e(b)') using results , modify

***************code End********************************* *
however , I get the excel file with following results.
STRING_VAR COUNT
1 0.095615161
2 0.025368614
3 0.879016225

However What I am looking for is something like

STRING_VAR COUNT
Black Weighted count of Black
White Weighted count of White
Other Weighted count of Other

Can anyone of the stata gurus in this forum help me getting the desired results.

I thank you very much for your time and effort. Sorry:I posted this in old statalist and then realized that the list is being migrated to the new statalist.

Thank you,
Yours Sincerely,
Anwar Dudekula

[Elan Cohen]

Anwar,

One problem here is that Stata matrices cannot contain strings (though mata matrices can IIRC). Here I just manually loop through the matrices to extract whichever parts we want. Not pretty, but it works. Note that I do not have Stata 13 so I can't check if the putexcel commands work.

// Use numeric version of race
svy, subpop(sex): tabulate race, count missing format(%11.3g)
mat eb = e(b)
loc i 1
putexcel A`i'=("Race") B`i'=("Count") using results, modify
loc ++i
loc n = colsof(eb)
forv j=1/`n' {
    loc str "`:lab (race) `=el(e(Row),1,`j')''"
    loc val = round(el(eb,1,`j'),0.01)
    di "`str' : `val'"
    putexcel A`i'=("`str'") B`i'=("`val'") using results, modify
    loc ++i
}

HTH,

- Elan

[Steve Samuels]

The reason that you are not getting counts is that e(b) contains the category weighted proportions. Get a matrix of weighted counts by multiplying e(b) by the estimated population size (sum of the weights):

matrix prop = e(b)
matrix counts = e(N_pop)*prop
matrix list counts

I don't know anything about putexcel, so can't advise about formatting the results.

You can make code & results more readable and skip the "start"/"end" lines if you hit the "A" button at the right of the edit window, select the code/results, and then push the "#" button. Try it out in the Sandbox forum.


Steve
[email protected]

[anwar dudekula]

Dear Steve and Elan,

Thank you very much for your effort.

Elan: Please give me some time to run the code and understand it.

Samuel: Thank you very much.I understand the code now.

Thanks again for your time and effort.
sincerely,
Anwar