Example 8.38: WriteXLS to create spreadsheets

[This article was first published on 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.

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


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:

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")


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";

proc export data = help outfile = "c:\book\newhelp.xls" 

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

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).

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.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)