# Example 9.28: creating datasets from tables

[This article was first published on

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

**SAS and R**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

**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 2×2 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) female homeless 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 count 1 0 1 169 2 1 1 40 3 0 0 177 4 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) female homeless 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`option (as a statement within the proc) which will duplicate each observation

*varname**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

**An unrelated note about aggregators**We love aggregators! Aggregators collect blogs that have similar coverage for the convenience of readers, and for blog authors they offer a way to reach new audiences. SAS and R is aggregated by R-bloggers and PROC-X with our permission, and by at least 2 other aggregating services which have never contacted us. If you read this on an aggregator that does not credit the blogs it incorporates, please come visit us at SAS and R. We answer comments there and offer direct subscriptions if you like our content. In addition, no one is allowed to profit by this work under our license; if you see advertisements on this page, the aggregator is violating the terms by which we publish our work.

To

**leave a comment**for the author, please follow the link and comment on their blog:**SAS and R**.R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.