Most SAS programmers have been here. Someone just wants a handful of numbers that they can add to a graph or power point presentation that is due tomorrow. You have the data files, you have a job to summarize it, and you have a dilemma. How do I get my data where the boss wants it, into Excel?
Transferring data between SAS and Microsoft Excel may be easier than you think.
I do not know how many times I have “Googled” something and gotten a cryptic answer that was marginally effective or even useless. You know that something somewhere will tell you how to do this, but where is that. Then you remember that the company that wrote the software has information online that will tell you how to do everything that your software can perform. But if you do not know the name of the procedure to use, how do you find the documentation about it?
My new book, “Exchanging Data between SAS and Microsoft Excel: Tips and Techniques to Transfer and Manage Data More Efficiently” is designed to help solve that problem by culling information from the SAS manuals and my personal experience into a document that shows you how to transfer data between SAS and Excel. In this first article on the subject, I will show you a simple way to transfer data to Excel with very little effort on your part. It is done with a “Right Click” of your mouse.
When viewing your SAS datasets in the SAS Explorer window all of the datasets have an icon or other display representing the SAS dataset. By using a “Right Click” on the dataset icon a menu appears with an option entitled “View in Excel”. Selecting this option creates an HTML file that Excel can open and use to view the data. In fact, SAS actually invokes Excel to open the HTML output file so you can use the data in Excel. The file will typically have a name similar to “#LNxxxxxx.xls”. The three byte extension (xls) allows Excel to open the file without hesitation prior to Excel version 2007. The newer versions check the contents of the file and if the file name ends in .xls but contains HTML or XML formatted commands for Excel then a message is displayed asking you to verify that you want to proceed. Select “Yes” and Excel opens and your data appears.
5 Comments
I just downloaded the Example Code and Data accompanying this book. Unfortunately the Chapter 8 Options lists..pdf is an exact copy of the READ_ME.pdf, could you correct this please?
Congratulations Bill for completing this book! I agree. With all of the things we do with Excel files, this SAS to Excel book interface will be essential.
My first thought was... a _book_ about SAS and Excel? Sure, I struggle with it sometimes but how could there be enough material for a book? Then I looked at the Table of Contents. Wow! I won't have to google "SAS Excel' anymore when I have a question. Ben's book looks to be incredibly comprehensive, very organized, and well written with lots of code samples. Just what I need. Thanks Ben!
John,
Thank you for the comment. The effort behind "Exchanging Data Between SAS and Microsoft Excel" took nearly eight years of my nights and weekends along with several rewrites and chapter shuffling to get the flow correct. I hope you will be able to find it useful. Also note the careful choice of the word between. The final chapters contain useful ways to automate your SAS processing for reports.
William E Benjamin Jr
I am looking forward to seeing this book at SAS Global Forum in Dallas. This is going to be a great resource.