Importing an Excel Spreadsheet
Let's look at an example of how to import an Excel spreadsheet.
In the list of files that you downloaded earlier, you will find an Excel file named excel_practice_01.xlsx.
Open the file on your computer:
The Excel spreadsheet contains four columns in the first worksheet:
The four columns are:
In order to import the file, you need two things:
The Filename statement is used to create a file reference.
Let's look at an example.
Filename REFFILE 'your external file location';
In this example, the file reference is REFFILE.
The file reference can be associated with an external file.
In this example, it is the Excel spreadsheet that we want to import (i.e., excel_practice_01.xlsx).
We need to specify the exact location of this file:
💡 Note: if you are not using SAS OnDemand for Academics, you can simply put the file location in the Filename statement.
SAS OnDemand for Academics is a little more trickier. Please follow the steps below to find the exact file location.
Let's right click on excel_practice_01.xlsx under Server Files and Folder, and click on Properties:
You can find the exact file location there:
Note: your path location will be different to the one in this example.
This is because your file is stored in a unique location on the SAS server.
Now, copy this path and paste it in the Filename statement.
Your Filename should look similar to this:
Filename REFFILE '/home/kisumsam/proc_import/excel_practice_01.xlsx';
Run this statement to create the file reference to the Excel spreadsheet!
Note: in this example, we have named the file reference REFFILE.
However, you can give it a different name, as long as it is no more than eight characters and does not contain any special characters, such as $, %, #.
You will see a number of examples in later topics.
Proc Import Procedure
The Proc Import procedure is used to import the file:
PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=WORK.IMPORT; GETNAMES=YES; RUN;
The Proc Import procedure has four elements:
1. The DATAFILE Option
The DATAFILE option tells SAS which file to import. In this example, it is REFFILE.
2. The DBMS Option (important)
The DBMS option tells SAS what type of data file we are importing into SAS. For an Excel spreadsheet, the DBMS value should be specified as XLSX.
Below are the values that can be specified with the DBMS option for the different types of files:
You will learn more about importing the different types of files in the next few topics.
3. The OUT Option
The OUT option specifies the output data set. In our example, it is IMPORT.
4. The GETNAME Option
The GETNAME option will be explained in the next topic.
Now, run the code above. The Proc Import statement will import the external data file and store the data in the IMPORT data set:
The IMPORT data set can be found in the Work library:
Use Proc Import to import the file named excel_practice_02.xlsx.
When reading an Excel spreadsheet, the DBMS option should be XLSX.
** Remember to change the path in the FILENAME statement **;
FILENAME REFFILE2 '/home/your_user_name/proc_import/excel_practice_02.xlsx';
PROC IMPORT DATAFILE=REFFILE2