Using LIBNAME XLSX to read and write Excel files

Reading Excel files with LIBNAME XLSX

You can use the XLSX engine to read from and write to a Microsoft Excel file as if it were a SAS data set. The LIBNAME statement associates a libref with the XLSX engine to access tables in a workbook. 

The XLSX engine enables you to connect to an Excel .xlsx data source and to specify an external data object name in a two-level SAS name.

To manipulate Excel files programmatically, SAS provides a LIBNAME XLSX engine that allows reading and writing data to Excel files.

For details on reading Excel files, see our guide on Proc import and Methods to import data in SAS

In this blog post, we will explore how to use the LIBNAME XLSX engine to read and write Excel files in SAS.

Reading Excel files with LIBNAME XLSX To read data from an Excel file, you first need to create a library reference to the Excel file using the LIBNAME statement. For example, if your Excel file is named “class.xlsx” and is located in the “/home/9to5sas/inputs/” folder, you can create a library reference as follows:

libname myexcel xlsx '/home/9to5sas/inputs/class.xlsx';

/* discover member (DATA) names */
proc datasets lib=myexcel; 
quit;

Once the library reference is created, you can access the Excel file using a SAS dataset name that matches the sheet name in the Excel file. For example, if your Excel file has a sheet named “class”, you can access it using the following SAS code:

data mydata;
  set myexcel.class;
run;
libname myexcel clear;

Unlike the SAS dataset libname, the Excel libname must be clear, otherwise, the Excel file will be locked, and occupied by SAS, thus cannot be opened by MS Excel.

Writing Excel files with LIBNAME XLSX

To write data to an Excel file, you can use the same LIBNAME statement as before. For example, to write data to a new sheet named “Sheet2” in the same Excel file, you can use the following code:

libname myexcel xlsx '/home/9to5sas/inputs/cars.xlsx';
data myexcel.Sheet2;
  set sashelp.cars;
run;
libname myexcel clear;

The LIBNAME XLSX engine has some notable differences from other engines such as EXCEL and PCFILES.

For example, the XLSX engine does not support Excel named ranges, which allow a portion of a spreadsheet to be surfaced as a discrete table.

Additionally, you will not see the familiar “$” decoration around the spreadsheet names when they are accessed within SAS.