Excel files or Workbooks are one of the most common file types to import into SAS that you will encounter.
While SAS does provide a few different methods for importing Excel files into SAS, it is worth noting that converting your Excel file(s) to a text file(s) before importing to SAS often provides greater flexibility and produces more desirable results than importing directly from Excel to SAS.
Most versions of Microsoft Excel can easily output different types of text files by using the “Save As” feature within Excel. The tips and tricks associated with importing CSV and other text files are described in the other SASCrunch importing data articles.
This article will provide a walkthrough of 3 different methods for importing Excel files into SAS, including:
- PROC IMPORT to import a simple Excel file into SAS
- PROC IMPORT to import an Excel file with multiple sheets into SAS
- SAS Studio Point-and-click to import an Excel file into SAS
Data Sets
Before running any of the examples below, you will need to replace the path ‘/home/your_username/SASCrunch’ with a directory that you have read/write access to in your environment.
This can vary depending on the machine you are running SAS on, the version of SAS you are running and the Operating System (OS) you are using.
If you are using SAS OnDemand for Academics, you must first upload the files to the SAS server.
Please visit here for instruction if you are not sure how to do this.
1. Importing a Simple Excel file with PROC IMPORT
Before importing an Excel file into SAS, you will need to know which version of Excel the file was created in, and also which Operating System (OS) you are running SAS on. Depending on the Excel version used and the OS system you are running, the parameters specified for PROC IMPORT may vary.
If you are using SAS in Microsoft Windows, you can use DBMS = Excel for all versions of Excel. However, if you are running UNIX or Linux (which is the case for SAS Studio) and trying to import an Excel file created in Excel 2007 or greater with the extension “.xlsx”, you need to use DBMS = XLSX instead of DBMS = Excel.
In this example, we are importing cars_excel.xlsx into SAS using PROC IMPORT within SAS Studio. Since the extension is “.xlsx”, we know the file was was created in Excel 2007 or later. Since SAS Studio is running Linux in the backend, we use DBMS = XLSX. The replace option is used so that SAS will allow WORK.CARS_EXCEL to be overwritten each time we run the code below:
proc import datafile = ‘/home/your_username/cars_excel.xlsx’
out = cars_excel
dbms = xlsx
replace
;
run;
You should now see the complete CARS_EXCEL dataset that was created in the WORK directory, shown partially here:
One of the major downsides of importing Excel files directly into SAS is that the GUESSINGROWS option cannot be used with DBMS = EXCEL or DBMS = XLSX. In cases where values in your data being imported into SAS are truncated, you are better off exporting your data from Excel into a delimited text file and then importing it into SAS with the GUESSINGROWS option and DBMS=DLM.
Do you have a hard time learning SAS?
Take our Practical SAS Training Course for Absolute Beginners and learn how to write your first SAS program!
2. Importing a Multiple Sheet Excel Workbook with PROC IMPORT
If you have a large Excel Workbook with many Worksheets inside, you can also import specific sheets into SAS, one at a time.
In the Excel file multi_sheet.xlsx, there are 2 sheets which contain data, Sheet1 and Sheet2. Sheet2 contains the Baseball data which will import into a SAS dataset called BASEBALL_SHEET2, and save it to the SAS WORK library. To do this with PROC IMPORT, you simply need to add the SHEET option to your PROC IMPORT call. Note that the name of the sheet, “Sheet2”, is case sensitive.
proc import datafile = ‘/home/your_username/SASCrunch/multi_sheet.xlsx’
out = baseball_sheet2
dbms = xlsx
replace
;
sheet = “Sheet2”;
run;
You should now see the output data, WORK.BASEBALL_SHEET2 , shown partially below:
If you needed to import subsequent sheets, you can simply modify the value for the sheet parameter to point to the desired sheet, and re-run the code.
3. Importing an Excel File with SAS Studio
Excel files can also be imported into SAS using the built-in import data tool inside of SAS studio, without actually writing any code. The same options that you specified in PROC IMPORT can be customized using the point and click utility within SAS studio.
This section will also show you how the tool can be used to generate the PROC IMPORT code that you can later modify and run on your own.
Here is how you can use the point-and-click tool to import an Excel file into SAS:
1. Click on the Server Files and Folders Pane on the left hand side of the screen:
2. Navigate to the folder where your Excel file is stored:
3. Right click on the file which you would like to import and select Import Data:
4. Click on the Settings tab and review the current settings:
By default, SAS will try to choose the most appropriate options but you do have a few options to pick from specify.
Using the Worksheet name box, you can change which Worksheet you would like to import by typing in a specific Worksheet name. If your Excel file only contains 1 worksheet, then you can leave the default “First worksheet” in the box and that worksheet will be imported.
The output dataset name and library can be changed using the Change button.
Finally, the Generate SAS variable names checkbox should be used if the first row of your Excel file contains the names of the columns which will be used for the SAS variable names in your output dataset.
In this example, we will use the default Options, but will change the name of the output dataset to something more informative, cars_excel:
5. To change the name of the output dataset, click Change:
Become a Certified SAS Specialist
Get access to two SAS base certification prep courses and 150+ practice exercises
6. Type in the data set name cars_excel and click Save:
7. For convenience, SAS also generates and displays the PROC IMPORT syntax which will be used to execute the import.
If you prefer, you can modify the import settings using the generated syntax as a starting point. You can also copy, paste and modify this code as needed. To see the PROC IMPORT syntax SAS generates, simply click the Code/Results tab:
Note that the new Data Set name specified in the Settings window has also been updated in the Code tab automatically to reflect this change.
8. The Split tab also provides a convenient view of both the Settings point-and-click window and the corresponding generated code window, all in one screen:
9. Once you have finished reviewing the settings and making any necessary changes, click the Run button to complete the import.
Note that no custom settings are required in this example to import the CARS_EXCEL.XLSX file into a SAS dataset.
10. After running the import utility, you can click on the Code/Results tab again to explore the contents and view the newly created dataset (WORK.CARS_EXCEL):
