Proc Import [1-7]

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:

  • ID
  • Age
  • Gender
  • Acct Open Date (Account Open Date)

Now, we are going to show you how to import this file, which involves two steps:

  • Create a library reference (FILENAME statement) and
  • Import the file (Proc Import Procedure)

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:

  • XLSX: Excel spreadsheet
  • CSV: CSV file
  • TAB: Tab-delimited file
  • DLM: Other delimited file

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:

Exercise

Use Proc Import to import the file named Excel_Practice_02.xlsx.

Get Hint

Get Solution