Can R replicate this data manipulation in SAS?

Since this involves time series within each id group I thought it
would be interesting to see if this could be formulated using
zoo series. ? The approach is to read it in, and convert it to a long
form by just stacking the start and stop times in a data frame
and converting that to zoo using the concatenation of date and id as
the time index. ?It makes use of the fact that
- zoo can use non-standard "times" (here the concatenation of date and id)
- read.zoo can split and aggregate and
- the resulting object can be directly cumsum'd using cumsum.zoo .

Finally we convert it back to the required form.

This was mostly done by looking at the output rather than trying to
follow the SAS code so some differences are possible. It does
seem to give the same output in this case.

Yet another approach might be to use model.matrix to create the
NRTI/NNRTI/PI columns:
? ? ? ?model.matrix(~ drug - 1, long2) * long2$change
but below we stick with zoo.

Some of the code here could be transplanted into Ista's solution
(which uses the reshape and plyr packages) to get a combination
approach.

Lines <- "1004 NRTI ?07/24/95 01/05/99
1004 NRTI ?11/20/95 12/10/95
1004 NRTI ?01/10/96 01/05/99
1004 PI ? ?05/09/96 11/16/97
1004 NRTI ?06/01/96 02/01/97
1004 NRTI ?07/01/96 03/01/97
9999 PI ? ?01/02/03 .
9999 NNRTI 04/05/06 07/08/09"

library(zoo)

# need na.fill from development version of zoo
source("http://r-forge.r-project.org/scm/viewvc.php/*checkout*/pkg/zoo/R/na.fill.R?revision=831&root=zoo")

# read in data and reshape to long form

DF <- read.table(textConnection(Lines), as.is = TRUE, na.strings = ".",
? ? ? ?col.names = c("id", "drug", "date", "date"), check.names = FALSE)
long <- rbind(cbind(DF[-4], change = 1), cbind(DF[-3], change = -1))

# convert to zoo. ? date.id, the concatenation of date & id, becomes index.
# Then take cumulative sums of columns and append regimen.

long2 <- with(long,
? ? ? ?data.frame(date.id = paste(as.Date(date, "%m/%d/%y"), id), drug, change))
z <- read.zoo(long2, index = 1, split = "drug", FUN = identity, aggregate = sum)

z <- cumsum(na.fill(z, fill = 0))
z$regimen <- ave(1:nrow(z), sub(".* ", "", time(z)), FUN = seq_along)

# convert z to data frame for final processing

id <- sub(".* ", "", time(z))
start_date = as.Date(sub(" .*", "", time(z)))
d2 <- data.frame(id, start_date, coredata(z))

# from each group of rows in same id form the desired columns.
# f processes one such group.

result