Blogs

Blogs

Accessing Excel files using LIBNAME XLSX

6
By Topics | Programming Tips

If you have been using SAS for long, you have probably noticed that there is generally more than one way to do anything. (For an example, see my co-author Lora Delwiche’s blog about PROC SQL.) The Little SAS Book has long covered reading and writing Microsoft Excel files with the IMPORT and EXPORT procedures, but for the Sixth Edition, we decided it was time to add two more ways: The ODS EXCEL destination makes it easy to convert procedure results into Excel files, while the XLSX LIBNAME engine allows you to access Excel files as if they were SAS data sets.

With the XLSX LIBNAME engine, you can convert an Excel file to a SAS data set (or vice versa) if you want to, but you can also access an Excel file directly without the need for a SAS data set. This engine works for files created using any version of Microsoft Excel 2007 or later in the Windows or UNIX operating environments. You must have SAS 9.4M2 or higher and SAS/ACCESS Interface to PC Files software. A nice thing about this engine is that it works with any combination of 32-bit and 64-bit systems.

The XLSX LIBNAME engine uses the first line in your file for the variable names, scans each full column to determine the variable type (character or numeric), assigns lengths to character variables, and recognizes dates, and numeric values containing commas or dollar signs. While the XLSX LIBNAME engine does not offer many options, because you are using an Excel file like a SAS data set, you can use many standard data set options. For example, you can use the RENAME= data set option to change the names of variables, and FIRSTOBS= and OBS= to select a subset of rows.

Reading an Excel file as is 

Suppose you have the following Excel file containing data about magnolia trees:

With the XLSX LIBNAME engine, SAS can read the file, without first converting it to a SAS data set. Here is a PROC PRINT that prints the data directly from the Excel file.

* Read an Excel spreadsheet using XLSX LIBNAME;
LIBNAME exfiles XLSX 'c:\MyExcel\Trees.xlsx';

PROC PRINT DATA = exfiles.sheet1;
   TITLE 'PROC PRINT of Excel File';
RUN;

Here are the results of the PROC PRINT. Notice that the variable names were taken from the first row in the file.

PROC PRINT of Excel File

Converting an Excel file to a SAS data set 

If you want to convert an Excel file to a SAS data set, you can do that too. Here is a DATA step that reads the Excel file. The RENAME= data set option changes the variable name MaxHeight to MaxHeightFeet. Then a new variable is computed which is equal to the height in meters.

* Import Excel into a SAS data set and compute height in meters;
DATA magnolia;
   SET exfiles.sheet1 (RENAME = (MaxHeight = MaxHeightFeet));
   MaxHeightMeters = ROUND(MaxHeightFeet * 0.3048);
RUN;

Here is the SAS data set with the renamed and new variables:


Writing to an Excel file 

It is just as easy to write to an Excel file as it is to read from it.

* Write a new sheet to the Excel file;
DATA exfiles.trees;
   SET magnolia;
RUN;
LIBNAME exfiles CLEAR;

Here is what the Excel file looks like with the new sheet. Notice that the new tab is labeled with the name of the SAS data set TREES.

The XLSX LIBNAME engine is so flexible and easy to use that we think it’s a great addition to any SAS programmer’s skill set.

To learn more about the content in The Little SAS Book, check out the free book excerpt.  To see up-and-coming titles and get exclusive discounts, make sure to subscribe to the SAS Books newsletter.

Tags book libname Little SAS Book Microsoft Excel SAS Press XLSX
Share

About Author

Susan Slaughter

Author

Susan Slaughter (left) is best known as one of the authors of The Little SAS Book. Her newest book, written with Rebecca Ottesen and Lora Delwiche, is Exercises and Projects for The Little SAS Book Fifth Edition. Susan discovered SAS software in graduate school over 30 years ago. Since then she has used SAS in a variety of business and academic settings. She has presented over 90 papers at local, regional, and international SAS user group conferences, and currently works as a consultant through her company, Avocet Solutions.

6 Comments

  1. Tracy Gleghorn on

    when i use the Libname abc xlsx..... my libref gets assigned, however the worksheet 'Sheet1' is not showing.

    Reply
  3. Scott Wheeler on

    Thanks for the relatively new tip - works great to create SAS datasets -- except when it doesn't !!

    Does anyone know what this SAS error means?

    "No cell rectangle in range Requested Input File Is Invalid"

    I don't really know why but I had 36 macro passes for 36 Excel tabs and four of them failed with these same error messages.
    I checked - all source tabs were spelled correctly.
    All tabs have data in them and all of the source headers are "Variable name" (which get converted to "variable_name" in SAS).

    Reply
    • Susan Slaughter on

      Scott,

      I know exactly what you mean by "works great--except when it doesn't!!"

      I have not run into this particular error message, but I found this: Problem Note 60543 The error "No cell rectangle in range occurs when you are reading an XLSX file for a named range of one cell." Here is the link: http://support.sas.com/kb/60/543.html

      This note says "The problem occurs when the named range includes only one cell."

      This does not exactly address your situation because this note uses PROC IMPORT instead of the XLSXL LIBNAME engine. Perhaps this note will suggest a solution to the problem using XLSX LIBNAME. Alternatively, you could try the workaround for PROC IMPORT listed in the note. Reading Excel files requires an extra measure of experimentation as your situation shows! Of course, if neither of those works, you can always submit your question to SAS Technical Support. Here is their link: https://support.sas.com/en/technical-support.html

      Good luck, stay healthy, and Happy SASsing!
      Susan

      Reply
  4. Peter Lancashire on

    The XLSX LIBNAME engine has a big disadvantage compared with the EXCEL LIBNAME engine: it does not support SAS data set options. This is a showstopper for many Excel files, where the type of data (character or numeric) can be anarchic. The only way I have found to read Excel files reliably is to define the type of every column with the DBSASTYPE= dataset option.

    This prompts the question: When does SAS plan to allow data set options with the XLSX LIBNAME engine?

    Documentation: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=p09nufzflnat5fn1f92yv8vuiutl.htm&docsetVersion=9.4&locale=en

    Reply
    • Susan Slaughter on

      Peter,

      You have raised a good point. SAS offers many ways to access Excel files, and they each have advantages and disadvantages.

      The second example in this blog uses the RENAME= data set option. So some data set options DO work with the XLSX LIBNAME engine, but not all. The disadvantage of the EXCEL LIBNAME engine is that it may not work if you are mixing 32-bit and 64-bit systems.

      When we were writing The Little SAS Book Sixth Edition, I talked to SAS developers and asked if they are likely to add options to the XLSX LIBNAME. The response I got was that they did not expect to add any features. However, SAS Institute has always been responsive to customer requests. I was going to suggest that you submit your request to the SASware Ballot, but I see you have already done that. Good for you! That is the best way to get what you want. https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-DBSASTYPE-or-similar-option-to-XLSX-engine-to-force-variable/idc-p/631215#M4065

      regards,
      Susan Slaughter

      Reply

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top