SASCRUNCH TRAINING
  • Home
  • SASĀ® Certification Training
    • SAS Certified Specialist Exam Training Program
    • How to Prepare For SAS Certified Specialist Base Programming Exam
  • Online Courses
    • Practical SAS Training Course for Beginners
    • Proc SQL Course
    • SAS Project Training Course
    • Logistic Regression (Credit Scoring) Modeling using SAS
  • Articles
    • Get Started >
      • 18 Free Resources to Help You Learn SAS
      • SAS Tutorial
      • How to Install SAS Studio
      • How to Learn SAS Fast
    • Data Import >
      • Importing Excel Spreadsheet
      • Importing CSV Files
      • Importing Text Files
    • SAS Functions >
      • CAT, CATT, CATS, CATX Functions
      • If-Then-Else statement
      • TRIM Function
      • STRIP Function
      • YEAR, MONTH, DAY Functions
      • Compress Function
      • Do-Loop
      • SCAN Function
      • LIKE Operator
      • INDEX Function
    • Data Manipulations >
      • The Ultimate Guide to Proc SQL
      • Proc Datasets
      • Dictionary Tables
      • Dealing with Missing Values
      • Proc Compare
      • Proc Transpose
      • RETAIN Statement
      • SAS Formats
      • SAS Arrays
    • Statistical Analysis >
      • Proc Means
      • Proc Freq
      • Proc Tabulate
    • Machine Learning >
      • Predicting Fish Species Using K-nearest Neighbor in SAS
      • Classify Product Reviews on Amazon Using Naive Bayes Model in SAS
    • Informational Interviews >
      • How to get a Clinical Trial/Research job without experience
      • Senior Recruiter at a Fortune 500 Retail Company
      • Manager, Non-profit Health Services Research
      • HR Manager
      • Quantitative Analyst
  • Services
    • The Ultimate Job Search Automation Services
    • Statistical Consulting
    • SAS Project or Assignment Help
    • Data Import Services
    • Data Manipulation and Reporting Services
  • In-class Training
    • SAS Training for Job Seekers
  • Guest Lecture
  • Sample Resume
  • About us
  • Contact Us
Practical SAS Training Course for Beginners


Get Access to:
​
  • 90+ Training ​Modules
  • 150+ ​Practice ​Exercises
  • 5 ​Coding ​Projects
  • 1000+​ Satisfied ​Students
Start your Free training!
x
Picture
Need help studying for the new
SAS Certified Specialist Exam?
Get access to:
  • Two Full Certificate Prep Courses
  • ​300+ Practice Exercises
Picture
Start your free training now
How to Prepare for the SAS Certified Specialist Base Programming Exam
Picture
 

Importing Excel Files into SAS


​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:
  1. PROC IMPORT to import a simple Excel file into SAS
  2. PROC IMPORT to import an Excel file with multiple sheets into SAS
  3. SAS Studio Point-and-click to import an Excel file into SAS

Software​
Before we continue, make sure you have SAS Studio or SAS 9.4 installed. Don't have the software? Download SAS Studio now. It's free!​
SAS Studio

Data Sets
The files used are:
  1. Cars_excel.xlsx
  2. Multi_sheet.xlsx
cars_excel.xlsx
File Size: 37 kb
File Type: xlsx
Download File

multi_sheet.xlsx
File Size: 121 kb
File Type: xlsx
Download File

Before running any of the examples below, you will need to replace the path ‘/folders/myfolders/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 Studio as described above, the path will typically start with “/folders/myfolders

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  =  '/folders/myfolders/SASCrunch/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:​
Picture

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.​

Picture
​Do you need help importing your data?
We can help you import your file for $5/file.
Key features:
  • $5/file
  • Accurate data import with Excel, CSV and text files
  • Complete source code with comments and instructions
  • SAS 9.4 / SAS Studio / SAS Enterprise Guide
  • Fast delivery (1 business day)
  • Secure data upload and handling (your data will be deleted once the project is completed)
Data Import Services

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 = '/folders/myfolders/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:
Picture
​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:
Picture

​2. Navigate to the folder where your Excel file is stored:
Picture

3. Right click on the file which you would like to import and select Import Data:

Picture

4. Click on the Settings tab and review the current settings:​
Picture

​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:

Picture

Are you totally new to SAS?
Picture
Take our Practical SAS Training Course for Beginners and learn how to code your first SAS program!
Start learning now


​6. Type in the data set name cars_excel and click Save:
Picture

​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:
Picture
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:
Picture

​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.
Picture

​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):
Picture

Picture
Need help studying for the new
SAS Certified Specialist Exam?
Get access to:
  • Two Full Certificate Prep Courses
  • ​300+ Practice Exercises
Start your free training now
How to Prepare for the SAS Certified Specialist Base Programming Exam

 

Master SAS in 30 Days

Start your Free training now!
Copyright © 2012-2019 SASCrunch.com All rights reserved.
  • Home
  • SASĀ® Certification Training
    • SAS Certified Specialist Exam Training Program
    • How to Prepare For SAS Certified Specialist Base Programming Exam
  • Online Courses
    • Practical SAS Training Course for Beginners
    • Proc SQL Course
    • SAS Project Training Course
    • Logistic Regression (Credit Scoring) Modeling using SAS
  • Articles
    • Get Started >
      • 18 Free Resources to Help You Learn SAS
      • SAS Tutorial
      • How to Install SAS Studio
      • How to Learn SAS Fast
    • Data Import >
      • Importing Excel Spreadsheet
      • Importing CSV Files
      • Importing Text Files
    • SAS Functions >
      • CAT, CATT, CATS, CATX Functions
      • If-Then-Else statement
      • TRIM Function
      • STRIP Function
      • YEAR, MONTH, DAY Functions
      • Compress Function
      • Do-Loop
      • SCAN Function
      • LIKE Operator
      • INDEX Function
    • Data Manipulations >
      • The Ultimate Guide to Proc SQL
      • Proc Datasets
      • Dictionary Tables
      • Dealing with Missing Values
      • Proc Compare
      • Proc Transpose
      • RETAIN Statement
      • SAS Formats
      • SAS Arrays
    • Statistical Analysis >
      • Proc Means
      • Proc Freq
      • Proc Tabulate
    • Machine Learning >
      • Predicting Fish Species Using K-nearest Neighbor in SAS
      • Classify Product Reviews on Amazon Using Naive Bayes Model in SAS
    • Informational Interviews >
      • How to get a Clinical Trial/Research job without experience
      • Senior Recruiter at a Fortune 500 Retail Company
      • Manager, Non-profit Health Services Research
      • HR Manager
      • Quantitative Analyst
  • Services
    • The Ultimate Job Search Automation Services
    • Statistical Consulting
    • SAS Project or Assignment Help
    • Data Import Services
    • Data Manipulation and Reporting Services
  • In-class Training
    • SAS Training for Job Seekers
  • Guest Lecture
  • Sample Resume
  • About us
  • Contact Us