How SAS engines can read a range of data in Excel

Japan has a superfast train called the Shinkansen.
The first I heard of it was when my sister visited Japan on a scholarship. Engine-awestruck, we wanted a ride, but weren’t able to get to Japan. Nor, did we know the language. (With over 5000 kanjis, it took my sister 3 years to master the alphabet alone!)

What does the Shinkansen have to do with reading foreign data?

You don’t have to go far nor learn a new language to read your foreign data. In SAS one statement lets those smooth-and- oh-so-powerful engines hum away in the back lifting your data from foreign lands and bringing it to your doorstep. Like the Shinkansen, these library engines do the heavy lifting, freeing up your time to work on other tasks. In this post I will show you how and hope you like the music link at the end.

Recently I taught SAS Programming 1 in Ottawa. On this trip, a SAS user asked:

How can I read a range of data from excel into SAS? (Note: No imports please)

SAS can read virtually any type of data. Here’s how to read Excel data.
Let’s start with a checklist:
1. Do I have the license to SAS ACCESS for PC Files?
-This is a must have so you can use the LIBNAME statement to access Excel
-Run this PROC to check-PROC SETINIT; RUN;
-Your log will list modules licensed at your site and should display SAS/ACCESS Interface to PC Files

2. Did I remember to respect SAS naming rules (i.e. length, characters allowed)?

Yet to create a named range? No problem. Highlight the range of data that you want to import into SAS. Type the name for this range here (see image on the right). Voila! Your named range is ready.

Now that you’ve checked off your list, here is the technique:

Use the LIBNAME statement to point to your excel workbook

For example, LIBNAME sales ‘Y:postsales datasales.xls’;
SAS creates a library called Sales. (A small globe appears on the bottom right indicating external non-SAS data). Double click to open the Sales Library.

Maybe you need to access Jan_sales –a named range in this example. You know it's a named range because it doesn't end with a dollar sign. (Excel puts a $ sign at the end of a worksheet name not for a named range.
Now SAS treats your excel range just like a SAS dataset. Any PROC or DATA step is fair game for this Excel-range-masquerading-as-a-SAS Dataset. Don’t you love it?

Can I use the LIBNAME statement to read other data?

Absolutely, work this unassuming yet powerful LIBNAME statement to read Oracle transactional data, your DB2 database, the list goes on. You just need the right ACCESS products licensed. Picture the ease of joining your Excel table to an Oracle table without any translation to SAS! Clearly there is way more to it than I can do justice in a short post, so please write to me if you want more.

I like efficiency just like you, our SAS user who is constantly creating simplicity, elegance and refinement in code. Freeing you to tackle those complex-hard-to-solve problems that only the human mind does so well. Maybe relax with this piece of music I created? Just like leveraging the LIBNAME engine to easily read foreign data, music needs no words-hope you enjoy this melody.

I love simple and efficient solutions and the LIBNAME statement to read foreign data would definitely qualify among my top 10 SAS winners! I would even go as far as to say that this statement matches the Shinkansen experience without leaving home! What do you say? What other software can do this for you?

tags: excel, tips and tricks

6 Comments

  1. Tom Nicholls
    Posted December 1, 2010 at 9:52 pm | Permalink

    I am also learning Japanese, too. I would say that Japanese is way more difficult than the SAS language!

  2. Jared
    Posted December 3, 2010 at 5:07 pm | Permalink

    Does this work in EG? The LIBNAME statement runs successfully, but there are no tables in the library even though the Excel spreadsheet has plenty of data.

  3. charu
    Posted December 5, 2010 at 9:40 pm | Permalink

    Absolutely! SAS is quite intuitive as you already discovered.. Thanks for your comment Tom!

  4. charu
    Posted December 6, 2010 at 12:09 pm | Permalink

    It should. maybe check you're pointing to the right path for the excel workbook. and you chose the excel engine if you're using the Assign project library task. If you can send me your screen capture charu.shankar@sas.com I can take a look

  5. Alex Ling
    Posted December 13, 2010 at 9:49 am | Permalink

    Hi Charu. Thanks. That's very useful and you taught a great class in Ottawa. It's just too bad we don't have SAS/ACCESS and I had to do a lot of "Save-as CSV" first.

  6. charu
    Posted December 14, 2010 at 9:52 am | Permalink

    Hope you can get SAS/ACCESS soon, will make life easier. Glad you enjoyed the class Alex. thanks so much for your comments!

One Trackback

  1. [...] Reading text. The ability of SAS to read virtually any data (text files, etc.) is outstanding. Use the DATA Step when you want to read text files. PROC SQL [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>