Example 8.38: WriteXLS to create spreadsheets

In our last entry, we described reading Excel files. In this entry, we do the opposite: write native Excel files.

R

In R, the WriteXLS package provides this functionality. It uses perl to do the heavy lifting, and the main complication is to install this with the needed Perl modules. There are detailed instructions here. On Nick's mac (which came with perl already installed), he needed to run the additional command:

cpan -i Text::CSV_XS

to add the needed functionality. Once this was done, he ran the commands:

library(WriteXLS)
testPerl()

which yielded the encouraging output:

Perl found.
All required Perl modules were found.

To generate the spreadsheet, the WriteXLS() function was called with a character vector or list containing the data frames to export, along with the name of the spreadsheet to create. Here we want to write out the HELP data frame.

HELP = read.csv("http://www.math.smith.edu/r/data/help.csv")
WriteXLS("HELP", "newhelp.xls")

It might be necessary to write multiple sheets to a single file. Here, as an example, we make a new table with just female subjects, then create an Excel file with the whole data and just the women.

helpfemale = subset(HELP, female==1)
WriteXLS(c("HELP", "helpfemale"), "newhelp.xls")

SAS

Several options exist in SAS for writing Excel files. The simplest may be through using the libname statement, but this appears to be platform dependent and we've had trouble using it. We've had more success with proc export, shown below.

data help;
set "c:\book\help.sas7bdat";
run;

proc export data = help outfile = "c:\book\newhelp.xls" 
     dbms=excel; 
run;

proc export data = help (where=(female=1)) 
     outfile = "c:\book\newhelp.xls" dbms = excel; 
     sheet="Females only"; 
run;

The second proc export statement adds a new sheet to the existing Excel file, with the designated name. This sheet contains only women due to the data set option where (section 1.5.1).