Table generation: collapse AND fpc?

[Hannah Willett]

Hi there,

In order to generate a table that can be used to build a Tableau dashboard, I need to generate a dataset that contains the prevalence (%) and associated CIs and RSEs for a series of variables. For example, the % of mothers in my state who had a pre-term birth in each year (as well as the CIs and RSE for each percentage). After messing around with a bunch of different options, the best (read: least time-consuming) thing I have found to do is to use the collapse command to produce data that I can copy and paste from the data browser into an excel file. The issue I am having is that I recently learned I need to introduce a finite population correction (fpc). As far as I can tell there is no way to introduce this through the collapse command. I am wondering if anyone knows of a way to introduce this correction into the code I am including below, or if there is another way to get what I have described above with the finite population correction. (I unfortunately cannot include data here because it's a confidential dataset)

Thanks in advance!
Hannah

***For variable "variable"***

egen variableyes = total(variable==1), by(certyear)
collapse (sd) sd_variable = variable (firstnm) y= variableyes (count) n= variable (mean) mean_variable= variable (sem) sem_variable = variable, by(certyear)

**CIs**
generate lo_variable = mean_variable - invttail(n-1,0.025)*(sd_variable/sqrt(n))
generate hi_variable = mean_variable + invttail(n-1,0.025)*(sd_variable/sqrt(n))

gen Percent = (mean_variable)*100
gen PercentLCL = lo_variable*100
gen PercentUCL = hi_variable*100
gen PercentRSE = (sem_variable/mean_variable)*100

drop sd_variable
drop lo_variable
drop hi_variable
drop sem_variable
drop mean_variable

[Jeff Pitblado (StataCorp)]

Here is an example of how you can take full advantage of the
svy prefix for computing your CIs and RSEs, and post your results
to a separate frame.

In the following I'll use NHANES data with highbp in place
of your variable and region in place of your
certyear. For the weighted counts, I'll use svy:total.
For the remaining percent statistics, I'll use svy:mean. I
assume you want to treat each certyear as a subpopulation, so
I'll use the over() option with svy:total and
svy:mean to get the subpopulation statistics.

Here is the code.

* load dataset
webuse nhanes2l

* show svy characteristics
svyset

* create a frame named -results-, and define the variables where I
* intend to post my results
frame create results region total percent lb ub rse

* compute survey totals
svy: total highbp, over(region)
matrix total = e(b)
* computed survey means
svy: mean highbp, over(region)
* coeflegend show the column names for referencing our statistics
svy, coeflegend
local colnames : colnames e(b)
* each mean (proportion/percent) corresponds to a region
levelsof region, local(regions)
* loop over the means (regions) and post our results
foreach colname of local colnames {
    gettoken region regions : regions
    frame post results ///
        (`region') ///
        (total[1,"`colname'"]) ///
        (100*_r_b[`colname']) ///
        (100*_r_lb[`colname']) ///
        (100*_r_ub[`colname']) ///
        (100*_r_se[`colname']/_r_b[`colname'])
}

* copy value labels
local vl : value label region
if "`vl'" != "" {
    tempfile labs
    quietly label  save `vl' using `labs'
    frame results {
        run `labs'
        label values region `vl'
    }
}

frame results : list

Here is the listing of the results dataset.

. frame results : list

     +---------------------------------------------------------------+
     | region      total    percent         lb         ub        rse |
     |---------------------------------------------------------------|
  1. |     NE    9593805   39.58184   32.89805   46.26564   8.279431 |
  2. |     MW   1.01e+07   34.75142   28.25875   41.24409   9.160611 |
  3. |      S   1.15e+07   36.95276   31.67157   42.23395   7.007421 |
  4. |      W   1.20e+07   36.61405   31.54263   41.68547   6.791337 |
     +---------------------------------------------------------------+

You can also use the above frame prefix to save or export that dataset.

frame results : save results

[Hannah Willett]

Hi Jeff,

This is helpful - thank you!

I am running into a strange issue: I am getting an error message indicating "no observations" after running both svy: total and svy: mean. It does not make a lot of sense because when I run each without svy they produce a result with no error. Any thoughts on why that might be?

Hannah

[Jeff Pitblado (StataCorp)]

Try codebook on the variables you svyset. Look for missing values. Maybe share more information about your data if this does not help.

[Hannah Willett]

One more follow up! I am getting numbers much higher than expected and I can't figure out why they are so different. I did realize that total is probably the total observations for each question (as opposed to what I need with is the count of "yes" responses) - but even if this were the issue, the numbers I am getting are way higher than the total respondents for this question. Any thoughts on why? Output below for the svy:total vs. svy:tab

[Jeff Pitblado (StataCorp)]

svy: total computes the weighted totals, which are population estimates. The values reported by svy: tabulate are weighted percentages and unweighted counts.