Accessing Excel Data Using the 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:
We will import both workbooks at the same time using a LIBNAME statement.
libname Seg xlsx'/home/kisumsam/proc_import/excel_practice_01.xlsx';
In earlier topics, we learned how to use a LIBNAME statement to create a library that maps to a folder on the SAS server.
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!
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.
Incorrect data seg1; set seg.segment 1; run;
There is a space in the data set name:
SAS will treat "Segment" and "1" separately. As a result, it won't recognize the "Segment 1" data set 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:
Correct data seg1; set seg.'segment 1'n; run;
A SAS name literal allows you to reference a data set by including the name in single quotes, followed by the letter 'n'.
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:
data seg1; set seg.'segment 1'n; run; proc print data=seg.'segment 1'n; run; proc contents data=seg.'segment 1'n; run;
This allows you to reference the data set without getting an error message.
Use the SAS XLSX engine to import the data from the file named excel_practice_02.xlsx.
The LIBNAME statement should have specified XLSX as the SAS engine.
** Remember to change the path in the FILENAME statement **;
libname City xlsx '/home/your_user_name/proc_import/excel_practice_02.xlsx';