BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Enterprise Guide

SAS Enterprise Guide: Import the Excel Spreadsheet – Easy Peasy

Submitted by on 2012-05-10 – 12:18 PM 4 Comments

One SAS Enterprise Guide feature I particularly like is the ability to import Microsoft Excel data quickly and easily.  SAS offers many ways to work with Excel spreadsheets but often I find I just want to extract data from Excel and get on with my job.

Tip – Click on any picture to see a larger image!

Use a “Known Good” Spreadsheet the First Time

If you are trying this process for the first time, use a “known good” or simple spreadsheet so if any issues arise you can at least eliminate the data as the cause. When this process fails, I generally find that the spreadsheet has something odd going on, such as pasted text, etc.  SAS Enterprise Guide has some sample spreadsheets available, which I use in this example. [Tip: Here’s how to deal with odd spreadsheet data.]

The SupplyInfo.xls spreadsheet is available in the SAS Enterprise Guide Sample data sub-directory. It has two sheets: Suppliers and Shippers.  Let’s import the Suppliers spreadsheet for some quick analysis. Here is the location of my sample files.  [Read Create Your Own Sample Data for SAS BI for ideas about where other sample data lives.]

sas enterprise guide sample spreadsheets

Import Excel Spreadsheet Data into SAS

I’m using SAS Enterprise Guide 5.1; as far as I can tell the wizard has not changed much from earlier releases so you should be able to follow along.

1 – From SAS Enterprise Guide, select File > Import Data.

2 – In the Open window, navigate to the where the spreadsheet is stored and select it. The Import Data Wizard appears.

Import Excel Into EG Step 1 3 – SAS populates the Specify Data window with the source spreadsheet and suggests loading the spreadsheet into the WORK library. Click Next to continue.Use the Browse button if you want to make changes.
4 – In the Select Data Source window, select the sheet you want to import.For this example, you are importing the entire spreadsheet so you only need to select the spreadsheet name.  The first row has the column names.

You might want to experiment later with just importing certain row and columns to see how SAS handles it.

 

5. In the Define Field Attributes window, you can make decisions about the individual data items.  For instance, you may want a field brought in as character (string) instead of a number.The SupplierID data item is all numbers so SAS guessed it was Number.  This is a common instance where you might prefer this value to be treated as a character.

 

 6 – The last window allows you a few choices to change before the import.  

Viewing the Results

After the import completes you will have a fresh dataset to use for analysis.

 

Modifying the Import

If you don’t like how the data appears, you can ticker with the results.  The Modify Task button re-starts the Data Import wizard.  You can also right-click the Import Data icon to make changes.

If you later add more rows to the spreadsheet, just Run the Process Flow again.  You can re-import the spreadsheet a thousand times if you want to spend your day doing that. 😉

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , ,

4 Comments »

1 Pingbacks »