Proc Import [6-7]

Accessing Excel Data using XLSX Engine

In the earlier sections, we learned how to import an Excel spreadsheet using Proc Import.

Another way to import the data from Excel is to use the SAS XLSX engine.  

Let's look at the Excel_Practice_01.xlsx file again.

The Excel file has two workbooks:

  • Segment 1 and
  • Segment 2

We will import both workbooks at the same time using a LIBNAME statement.

In earlier modules, we learned how to use a LIBNAME statement to create a library that connects to the shared folders.

The LIBNAME statement can also be used to connect to an Excel spreadsheet.

In this example, The XLSX (3) is the name of the SAS engine. 

The LIBNAME statement creates a new library called SEG (2) that connects to the Excel_Practice_01.xlsx (4) file. 

The SEG library has two data sets called SEGMENT 1 and SEGMENT 2.

These two data sets are imported from the two worksheets in the Excel spreadsheet. 

You can double click on the data set to see the contents:

This method allows you to easily access the data from an Excel spreadsheet using just the LIBNAME statement!


​Name Literals

There is one major problem with the two data sets in the SEG library.

The names of the data sets are shown as 'Segment 1' and 'Segment 2'.

Both names contain a space!

This not only violates the SAS rules for data set names, it also creates problems when referencing the data set in the code.

For example, let's suppose we want to copy the data set into the WORK library using the SET statement.

There is a space in the data set name:

SAS will treat "Segment" and "1" separately. As a result, it won't recognize the data set "Segment 1" at all.

When you encounter situations where a SAS data set name contains a space, you can use the name literal to reference the SAS name.

​Below is an example:

A SAS name literal allows you to reference a data set by including the name in single quotes, followed by the letter 'n'.

For example:
Segment 1 --> 'Segment 1'n
A B C --> 'A B C'n

When referencing the "Segment 1" data set in the SEG library, we can do the following:

This allows you to reference the data set without getting an error message.


Exercise

Use the SAS XLSX engine to import the data from the file named Excel_Practice_02.xlsx.

Get Hint

Get Solution