Importing an Excel Spreadsheet
Let's suppose we want to import the Excel_Practice_01.xlsx file that we downloaded earlier.
The Excel spreadsheet contains four columns in the first worksheet:
The four columns are:
Now, we are going to show you how to import this file, which involves two steps:
First, we will create a file reference for the Excel file:
The FILENAME statement has three parts:
The FILENAME (1) statement creates a file reference called ExFile (2).
Intuitively, the file reference (or FILEREF) is the name we give to the external Excel file.
In this example, we want to import the Excel_Practice_01.xlsx file.
We give this file a name called ExFile.
Now we will use Proc Import to import the Excel spreadsheet.
filename ExFile '/folders/myfolders/proc import files/excel_practice_01.xlsx';
The Proc Import procedure has four parts:
1. The DATAFILE Option
The DATAFILE option specifies the external file to be imported (i.e. EXFILE in our example).
2. The OUT Option
The OUT option specifies the output data set. In our example, it is EX1.
3. The DBMS Option (important)
The DBMS option allows you to specify the type of data file to import. For an Excel spreadsheet, the DBMS value should be specified as XLSX.
Below are the values for the different types of files:
You will learn more about importing the different types of files in the next few sections.
4. The REPLACE Option
The REPLACE option tells SAS to replace the existing data set if it already exists. We will look at an example of this shortly.
Now, run the code above. The Proc Import will import the external data file and store the data in the EX1 data set:
Use Proc Import to import the file named Excel_Practice_02.xlsx.
When reading an Excel spreadsheet, the DBMS option should be XLSX.
filename ExFile2 '/folders/myfolders/proc import files/excel_practice_02.xlsx';
proc import datafile=Exfile2