###############################################################################
# Functions to Create from ts.xpt files from excel spreadsheet
# See example spreadsheet - it can be expanded with other parameters and any number of studies
# Expects to place output in subdirectories of the location of the XLS file selected
####################################################################################
# XLConnect documentation here:
# https://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf
# SASxport documentation here:
# https://cran.r-project.org/web/packages/SASxport/SASxport.pdf
####################################################################################
# You may need next two lines first time
# install.packages("XLConnect")
# install.packages("SASxport")
####################################################################################
require(XLConnect)
require(SASxport)
require(utils)
if(packageVersion("SASxport") < "1.5.7") {
stop("You need version 1.5.7 or later of SASxport")
}
# This section is to replace functions in 1.5.7 or SASxport to allow column lengths of less than 8 bytes
# This gives the directory of the file where the statement was placed , to get current .R script directory
sourceDir <- getSrcDirectory(function(dummy) {dummy})
source(paste(sourceDir, "/write.xport2.R", sep=""))
tmpfun <- get("read.xport", envir = asNamespace("SASxport"))
environment(write.xport2) <- environment(tmpfun)
attributes(write.xport2) <- attributes(tmpfun)
assignInNamespace("write.xport", write.xport2, ns="SASxport")
# Select file to read, will place output in subdirectories of that
# Set output files
myFile <- choose.files(caption = "Select XLS file",multi=F,filters=cbind('.xls or xlsx files','*.xls;*.xlsx'))
mainDir <- dirname(myFile)
# Read in XLSX file
df <- readWorksheetFromFile(myFile,
sheet=1,
startRow = 1,
endCol = 7)
# make copy skipping first two rows (field type and field label)
df2 = df[-1,]
df2 = df2[-1,]
# for those that are num, transform to numeric
df2=transform(df2, TSSEQ = as.numeric(TSSEQ))
# set labels for each field
Hmisc::label(df2)=df[2,]
# For each set of rows belonging to a study create TS.XPT file
studyList <- unique(df2$STUDYID)
for(aStudy in studyList){
# Create subdirectory
# Set output files
setwd(mainDir)
if (file.exists(aStudy)){
setwd(file.path(mainDir, aStudy))
} else {
dir.create(file.path(mainDir, aStudy))
setwd(file.path(mainDir, aStudy))
}
# filter to this study
studyData <- subset(df2, STUDYID==aStudy,keepNA=FALSE)
# Set length for character fields
SASformat(studyData$DOMAIN) <-"$2."
# place this dataset into a list with a name
aList = list(studyData)
# name it
names(aList)[1]<-"TS"
# and label it
attr(aList,"label") <- "TRIAL SUMMARY"
# write out dataframe
write.xport2(
list=aList,
file = "ts.xpt",
verbose=FALSE,
sasVer="7.00",
osType="R 3.4.2",
cDate=Sys.time(),
formats=NULL,
autogen.formats=TRUE
)
aLine <- paste("Created file: ", mainDir, "/",aStudy,"/ts.xpt",sep = "")
print(aLine)
} # end of study loop
setwd(mainDir)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.