How SAS engines can read a range of data in Excel

8

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?

Share

About Author

Charu Shankar

Technical Training Specialist

Charu Shankar has been a Technical Training Specialist with SAS since 2007. She started as a programmer, and has taught computer languages, business and English Language skills. At SAS, Charu teaches the SAS language, SQL, SAS Enterprise guide and Business Intelligence. She interviews clients to recommend the right SAS training to help them meet their needs. She is helping build a center for special needs kids in this project. http://www.handicareintl.org/pankaja/pankaja.swf

8 Comments

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

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

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

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

  5. Pingback: Five reasons to use the SAS DATA Step or PROC SQL - The SAS Training Post

  6. i am trying to execute a simple select query however it goes for toss
    basically i need to import data tables from SQL SERVER 2008 which i did thru ODBC and created library folder in sas base9.3
    now when i execute any query with the tables in library it just goes running and need to forcefully stop the query
    kindly advise if anything missed or which would be best way to migrate SQL SERVER 2008 tables in SAS base 9.3 and then execute them in SAS

Leave A Reply

Back to Top