## Monday, April 23, 2012

### Example 9.28: creating datasets from tables

R
There are often times when it is useful to create an individual level dataset from aggregated data (such as a table). While this can be done using the expand.table() function within the epitools package, it is also straightforward to do directly within R.

Imagine that instead of the individual level data, we had only the 2x2 table for the association between homeless status and gender within the HELP RCT:
`> HELPrct = read.csv("http://www.math.smith.edu/r/data/help.csv")> xtabs(~ homeless + female, data=HELPrct)        femalehomeless   0   1       0 177  67       1 169  40`

We can use this information to create an analytic dataset using just the four rows of a new dataset:
`> female = c(0, 1, 0, 1)> homeless = c(1, 1, 0, 0)> count = c(169, 40, 177, 67)> ds=data.frame(cbind(female, homeless, count))> ds  female homeless count1      0        1   1692      1        1    403      0        0   1774      1        0    67`

Next we use the rep() function to generate a vector of indices to repeat. The index object repeats each row number count times.
`> index = rep(seq_len(nrow(ds)), times=ds\$count)> newds = ds[index,]> newds\$count = NULL> xtabs(~ homeless + female, data=newds)        femalehomeless   0   1       0 177  67       1 169  40`

The resulting data set is identical to the summarized input data set.

SAS
Many SAS procedures offer a weight varname option (as a statement within the proc) which will duplicate each observation varname times. So, for example, we can make a data set such as that shown above, then use, e.g., proc freq to produce a table.
`data ds;female = 0; homeless = 1; count = 169; output;female = 1; homeless = 1; count = 40; output;female = 0; homeless = 0; count = 177; output;female = 1; homeless = 0; count = 67; output;run;proc freq data = ds;table homeless * female;weight count;run;                homeless     female                Frequency|                Percent  |                Row Pct  |                Col Pct  |       0|       1|  Total                ---------+--------+--------+                       0 |    177 |     67 |    244                         |  39.07 |  14.79 |  53.86                         |  72.54 |  27.46 |                         |  51.16 |  62.62 |                ---------+--------+--------+                       1 |    169 |     40 |    209                         |  37.31 |   8.83 |  46.14                         |  80.86 |  19.14 |                         |  48.84 |  37.38 |                ---------+--------+--------+                Total         346      107      453                            76.38    23.62   100.00`

However, some procedures lack this option, and/or it may be difficult to arrange your data appropriately to take advantage of it. In such cases, it's useful to be able to expand the data manually, as we show for R above. We demonstrate this below, assuming the count variable can be constructed. The explicit output statement puts a line into the newds data set count times.
`data newds;set ds;do i = 1 to count;  output;  end;run;proc freq data = newds;table homeless * female;run;                homeless     female                Frequency|                Percent  |                Row Pct  |                Col Pct  |       0|       1|  Total                ---------+--------+--------+                       0 |    177 |     67 |    244                         |  39.07 |  14.79 |  53.86                         |  72.54 |  27.46 |                         |  51.16 |  62.62 |                ---------+--------+--------+                       1 |    169 |     40 |    209                         |  37.31 |   8.83 |  46.14                         |  80.86 |  19.14 |                         |  48.84 |  37.38 |                ---------+--------+--------+                Total         346      107      453                            76.38    23.62   100.00`

Nick Horton said...

Steve Taylor helpfully notes that if you have the data in an xtabs object called mytable, the code:

> female = c(0, 1, 0, 1)
> homeless = c(1, 1, 0, 0)
> count = c(169, 40, 177, 67)
> ds=data.frame(cbind(female, homeless, count))
> ds

can be replaced by:

> ds = as.data.frame(mytable)

Ken Kleinman said...

Just clarifying that while the weight statment works as describe for proc freq, in many procedures the freq statement does the same thing, while the weight statement is more complex. Its effect in different procedures varies.