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

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

Import Your Excel Spreadsheet

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

Tags: Enterprise Guide, Excel & SAS, working with data