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?