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 Odd Spreadsheet Data

Submitted by on 2012-05-30 – 8:44 AM 4 Comments

Perhaps you took my advise a few weeks back and took advantage of the amazing (trumps blare!) SAS Enterprise Guide ability to import MS Excel spreadsheets.  However, if your spreadsheet is a little different – then you may run into some issue when you go to import it.  For instance, look at this import:

Oh it’s so horrible – the columns names are not there and it’s going to be a lot of work to clean it up. Urgh .. it’s giving me a bad day already.  Looks like the default approach to importing this spreadsheet didn’t work out as well. Curses – there must be a better way!

Your Data Needs Some Special Lovin’

So let’s look at a spreadsheet similar to the one imported above.  You’ll notice that the actual data I want starts on row 3 not on row 1 as the default import would like.  So row 3 contains the column labels and row 4 contains the data.

When you go with the default…

If I go with the defaults and select First row of range contains the field name – the Import wizard default assumes row 1 is where I want to start.  Basically when using this approach with data that is not as expected – I have to decide if I want to edit the field names now or later in the code.  Since the import procedure used the first row – it’s all messy.  This is not a good approach.  It’s too much work – which I think should be illegal.

Use the Top-Left Cell Row Setting, Luke

You can set the top-row, which in this case is cell A3.  Then SAS EG understands where the first row is located and the import is handled correctly.  Check it out … SAS imports and you drink coffee (fruit juices are also allowed).  If you plan to use this task more than once, use the Expand row range as needed.  This setting causes the lower-right cell value to increase if more rows are added to your spreadsheet.  It’s the cool method!

 

 

Update: I’m using MS Excel 2010 and Enterprise Guide 5.1.  There were some issues noted with Excel and EG that are discussed here: http://support.sas.com/kb/43/802.html

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 »

  • Hi Diane:

    That same thing happens to me – drives me insane! Like pull my hair outta my head insane.

    So here’s two workarounds I know of:
    – Use Proc Import
    Based on the spreadsheet – I have had complete success and varying success. Sometimes PROC IMPORT knows exactly how to handle it and sometimes not.
    Also I have had drama if I was using a newer version of MS Excel. But then sometimes is works without any prodding and produces a dataset that you
    can easily monkey with.
    – Use the EG Import Wizard
    Save the XLS as a CSV.
    Import the file.
    Copy and modify the code.
    This method makes it easier to repeat the process because the code is portable.
    – You can use a LIBNAME statement to connect directly to a XLS spreadsheet. I’ll demo this method in an upcoming blog – but just check the SAS doc set
    if you want to “steal my thunder”! 😉

    Let me know if any of these techniques work or if you figure out a better solution.

    Tricia

  • Diane says:

    I love the blogs. I’m fairly new to them, and have learnt a lot.
    Just wondering what you’d suggest in this situation:
    I have a spreadsheet that has about 80 columns and the column names start in row 2. I can import it ok, but the column names are really awful and some of the lengths and formats are incorrect so I want to change them. The import wizard is a pain when it comes to that many columns, so I borrowed the code behind the importing and modified that. Then when I ran it again in a new EG session the file name was incorrect because the import wizard “has used internal routines to transfer the source data file from the local file system to SASBI. There is no SAS code available to represent this action.” How do you get around this?

  • Hey Michel:
    Thanks for the feedback. I’ll write a blog post to expand on the HTML. Can you tell me your specific question or what you were hoping we would explore a little more?

    And I love you too!!!! 🙂

    Tricia

  • michel jubinville says:

    I bought your book about SAS BI. I don’t really get it on page 77 and I sent an email at sas press (#7610804587). Some of us, ol’ SAS programers, aren’t quite acquainted with those HTML commands. It would have helped to get some explanations.
    Other than that I love you (you asked for it !), well, I mean the tricks you give us.

    Have a sunny day (but not too hot)