1.5. SAS PROCEDURES -> Equivalent in R

1.5.10. PROC EXPORT Excel in SAS vs R equivalent

1. Basic Excel Export: One Dataset, One Sheet

SAS Example

proc export data=subjects
    outfile="&path./subjects.xlsx"
    dbms=xlsx replace;
    sheet="SUBJECTS";
run;

Explanation (SAS):

• PROC EXPORT creates Excel files from SAS datasets.
• data=subjects specifies the input dataset.
• outfile="&path./subjects.xlsx" defines the output file path.
• dbms=xlsx indicates Excel format output.
• replace overwrites any existing file.
• sheet="SUBJECTS" names the worksheet.

R Example

# Dummy data and package
library(openxlsx)
subjects <- data.frame(
  ID = 1:5,
  Name = c("Alice", "Bob", "Carol", "David", "Eve"),
  Age = c(23, 31, 27, 29, 35)
)
write.xlsx(subjects, "./output/subjects.xlsx", sheetName = "SUBJECTS")

Explanation (R):

• Uses the openxlsx package for Excel output functionality.
• write.xlsx() exports a data frame to an Excel file.
• sheetName = "SUBJECTS" names the worksheet within the Excel file.
• By default, overwrite = TRUE replaces any existing file with the same name.

2. Exporting Multiple Sheets

SAS Example

proc export data=subjects
    outfile="&path./subjects_ca2.xlsx"
    dbms=xlsx replace;
    sheet="SUBJECTS";
run;

proc export data=base_ca
    outfile="&path./subjects_ca2.xlsx"
    dbms=xlsx replace;
    sheet="BASELINE_CA";
run;

Explanation (SAS):

• Requires multiple PROC EXPORT statements to create multiple sheets.
• The first call uses replace to create a new file.
• Subsequent calls add new sheets to the same file.
• Each call specifies a different sheet= name.
• Sheet order is determined by the sequence of PROC EXPORT calls.

R Example

# Dummy data and package
library(openxlsx)
subjects <- data.frame(
  ID = 1:5,
  Name = c("Alice", "Bob", "Carol", "David", "Eve"),
  Age = c(23, 31, 27, 29, 35)
)
base_ca <- data.frame(
  ID = 1:5,
  Baseline = c(100, 110, 120, 130, 140)
)
l2 <- list("SUBJECTS" = subjects, "BASELINE_CA" = base_ca)
write.xlsx(l2, file = "./output/subjects_ca2.xlsx")

Explanation (R):

• Creates a named list containing multiple data frames.
• Each list element becomes a separate worksheet.
• Sheet names are taken from the list names.
• The order of sheets follows the order in the list.
• write.xlsx() automatically handles the multi-sheet export.

3. Beyond Basics: Formatting, Styling, and Formulas

SAS Example: Styled Export

ods excel file="styled_subjects.xlsx" style=statistical;
proc print data=subjects style(header)={background=lightblue foreground=white};
run;
ods excel close;

Explanation (SAS):

• Opens an Excel destination with ods excel and applies the "statistical" style.
• Uses PROC PRINT to output the dataset to Excel.
• The style(header)= option formats just the header row with light blue background and white text.
• After outputting data, ods excel close finalizes and saves the Excel file.

R Example: Styled Export

# Dummy data and package
library(openxlsx)
subjects <- data.frame(
  ID = 1:5,
  Name = c("Alice", "Bob", "Carol", "David", "Eve"),
  Age = c(23, 31, 27, 29, 35)
)
wb <- createWorkbook()
addWorksheet(wb, "Styled")
writeData(wb, "Styled", subjects)
style <- createStyle(fontColour = "#FFFFFF", fgFill = "#4F81BD", halign = "center")
addStyle(wb, "Styled", style, rows = 1, cols = 1:3, gridExpand = TRUE)
saveWorkbook(wb, "./output/styled_subjects.xlsx", overwrite = TRUE)

Explanation (R):

• Creates a new Excel workbook using createWorkbook().
• Adds a worksheet named "Styled" with addWorksheet().
• Writes the data to that worksheet with writeData().
• Creates a style with white font on blue background and center alignment.
• Applies that style to the first row (headers) across columns 1-3.
• Saves the workbook to disk, overwriting any existing file.

4. Metadata-Driven Export Automation

SAS Example

%macro export_loop;
  %let n = 2;
  %do i = 1 %to &n;
    %let ds = %scan(subjects base_ca, &i);
    %let sh = %scan(SUBJECTS BASELINE_CA, &i);
    proc export data=&ds
        outfile="&path./multi_export.xlsx"
        dbms=xlsx replace;
        sheet="&sh";
    run;
  %end;
%mend;
%export_loop;

Explanation (SAS):

• Creates a macro called export_loop to automate the export process.
• Sets n = 2 to define how many datasets to export.
• Uses a %do loop to iterate from 1 to n.
• For each iteration, extracts the dataset name and sheet name from space-delimited lists.
• Executes PROC EXPORT with the selected dataset and sheet name.
• The macro approach enables programmatic control of exports.

R Example

# Dummy data and package
library(purrr)
library(openxlsx)
library(readxl)
subjects <- data.frame(
  ID = 1:5,
  Name = c("Alice", "Bob", "Carol", "David", "Eve"),
  Age = c(23, 31, 27, 29, 35)
)
base_ca <- data.frame(
  ID = 1:5,
  Baseline = c(100, 110, 120, 130, 140)
)
ae_data <- data.frame(
  ID = 1:3,
  AE = c("Headache", "Nausea", "Fatigue")
)
vitals <- data.frame(
  ID = 1:3,
  BP = c("120/80", "130/85", "110/70")
)
config <- data.frame(
  data = c("subjects", "base_ca", "ae_data", "vitals"),
  sheet = c("SUBJECTS", "BASELINE_CA", "ADVERSE", "VITALS"),
  file = c("./output/report1.xlsx", "./output/report1.xlsx", "./output/safety.xlsx", "./output/safety.xlsx"),
  stringsAsFactors = FALSE
)
write.xlsx(config, "export_config.xlsx")
pwalk(config, function(data, sheet, file) {
  write.xlsx(get(data), file = file, sheetName = sheet, append = TRUE)
})

Input Example (export_config.xlsx):

Explanation (R):

• Uses purrr package for functional programming and iteration.
• Reads a configuration Excel file that contains dataset names, sheet names, and output file paths.
• pwalk() iterates through each row of the config file, applying the function to export each dataset.
• Inside the function, get() retrieves the dataset by name, then writes it to the specified sheet and file.
• append = TRUE allows adding sheets to existing files without overwriting them.
• The configuration approach makes exports maintainable and self-documenting.

5. Real-World Clinical Use Cases

Explanation:

• Listings for review: SAS typically uses more complex PROC REPORT for formatting, while R uses a simpler one-function call.
• Annotated CRF: SAS can export Case Report Forms with annotations using ODS EXCEL, while R offers more flexible styling options through openxlsx.
• Validation reports: SAS typically uses PROC COMPARE results exported to Excel, while R's pointblank package provides specialized validation reports.
• Metadata exports: SAS exports from metadata datasets, while R's approach is often more concise using list structures.

6. Lightweight Export Options

SAS Example (CSV alternative)

proc export data=subjects
    outfile="&path./subjects.csv"
    dbms=csv replace;
run;

Explanation (SAS):

• When Excel formatting isn't crucial, CSV export can be faster and more reliable.
• CSV files can be opened directly in Excel by end users.
• This approach avoids potential Excel engine complexities.

R Example

# Dummy data and package
library(writexl)
subjects <- data.frame(
  ID = 1:5,
  Name = c("Alice", "Bob", "Carol", "David", "Eve"),
  Age = c(23, 31, 27, 29, 35)
)
write_xlsx(subjects, "./output/subjects.xlsx")

Explanation (R):

• The writexl package provides a faster, simpler alternative to openxlsx when formatting isn't needed.
• write_xlsx() quickly exports a data frame to Excel format.
• No styling or advanced features are supported.
• Best used for quick exports, draft outputs, or large datasets where speed is critical.

7. Summary: SAS vs R Excel Export Capabilities

Explanation:

• SAS requires more code for multi-sheet exports and advanced formatting.
• R generally provides more flexible and programmer-friendly Excel export capabilities.
• Both systems handle basic exports well, but R has an advantage for complex styling and metadata-driven approaches.
• SAS can be more challenging in cross-platform environments due to Windows dependencies for the Excel engine.

**Resource download links**

1.5.10.-PROC-EXPORT-Excel-in-SAS-vs-R-equivalent.zip