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.