How do I export from SAS to Excel files: Let me count the ways

I have a love-hate relationship with spreadsheet data. I am interested in finding data to analyze, and if it arrives in the form of a spreadsheet, I'll take it. And I like to deliver results and reports, but often my constituents ask for it as a spreadsheet that they can then manipulate further. <Sigh.>

A spreadsheet is not a database, so it can be a challenge to whip spreadsheet data into analysis-ready shape. Likewise, saving results as a spreadsheet can lose something in the translation -- usually value formatting, appearance attributes, or graphs.

SAS offers many ways to read from and write to Microsoft Excel spreadsheets. They each have pros and cons. This post is an inventory of the methods that I know about for creating Excel files from within SAS.

Some "bits" about 32-bit and 64-bit architecture

Before I get to the Big List, let me set the stage by describing a few terms and concepts.

In order to create Excel files directly from SAS, you need SAS/ACCESS to PC Files. This product enables all sorts of file exchanges between SAS and other PC-based applications, including Microsoft Excel, Microsoft Access, SPSS, and more.

SAS/ACCESS to PC Files includes a component called the PC Files Server. The PC Files Server is a service-based application that runs apart from SAS on a Windows node. It accepts requests from SAS to convert data to and from Excel (and other file types). Traditionally, this innovation allows SAS running on UNIX (where there are no native facilities for Excel data) to exchange data with PC-based data programs.

Recent changes in desktop computing have complicated the picture. Newer desktop machines all have 64-bit architecture, and most organizations are now adopting 64-bit versions of Microsoft Windows. All of your 32-bit applications (designed for x86 architecture) still can still run, of course, but there are a few hard-and-fast rules. One of those rules is that a 64-bit application cannot dynamically load 32-bit modules in its process space. And guess what? There is a better-than-even chance that the built-in data providers that you have for Microsoft Excel -- the bits that allow SAS to write to Excel on Windows -- are 32-bit modules. This means that the PROC EXPORT DBMS=EXCEL program that worked in your 32-bit SAS session will not work in your 64-bit SAS session.

The second remedy is to use the PC Files Server, right there on the same Windows machine where SAS is running. This allows a 64-bit SAS process to delegate the data exchange to a 32-bit PC Files Server process. Thanks to the out-of-process communication, this circumvents the bit architecture mismatch. To make this work you don't have to set up any additional software, but your SAS programs must change to use DBMS=EXCELCS. The EXCELCS keyword tells SAS to use the PC Files Server instead of attempting to use in-process data providers.

Exporting to Excel: ways to get there from SAS

With the architecture lesson behind us, here's my list for how to put SAS content into Microsoft Excel. I won't dive into much detail about each method here; you can follow the links to find more documentation.

These methods use features of SAS/ACCESS to PC Files:

LIBNAME EXCEL – reads/writes Excel files at the sheet level when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office). An Excel file is viewed as a SAS library, while sheets/ranges are the member tables. Requires exclusive lock on an existing Excel file.

LIBNAME PCFILES – does the same as LIBNAME EXCEL, but uses PC Files Server. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

PROC EXPORT DBMS=EXCELCS – uses PC Files Server to write an Excel file. There are various options to control the output behavior. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

ODS TAGSETS.CSV (or just DATA step and FILE output) – produces comma separated value files, most often used in Excel.

ODS TAGSETS.EXCELXP – uses ODS to create an Office XML file. Provides a fair amount of control over the content appearance, but recent versions of Excel do not recognize as a "native" format, so user is presented with a message to that effect when opening in Excel.

FILENAME DDE – uses Windows messages to control what goes into an Excel file, down to the cell level. Works only when SAS for Windows and Excel are on the same machine. Doesn't work in SAS workspace servers or stored process servers (often accessed with SAS Enterprise Guide). It's an antiquated approach, but offers tremendous control that many long-time SAS users enjoy.

SAS client applications make it easier

While I've focused on the SAS programming methods for creating Excel files, applications like SAS Enterprise Guide and the SAS Add-In for Microsoft Office make the operation a breeze. SAS Enterprise Guide can import and export Excel files through point-and-click methods, and SAS/ACCESS to PC Files is not needed to make that work. (However, the work is not captured in a SAS program, so it cannot be run in SAS batch jobs or stored processes.)

SAS Add-In for Microsoft Office turns the problem on its head. By allowing you to access SAS data and analytics from within Microsoft Excel, you pull the results into your Excel session, rather than export them from your SAS session.

59 Comments

There are loads of other ways to handle the issue. SAS datasets are ODBC and OleDb compliant so you can read them in using Excel. A pull strategy vs a push. Not perfect, but an alternative.

I have used .NET apps to convert SAS datasets to Excel. The advantage to that is pure power and speed. Far more control over how the worksheets come out and the speed is incredible.

Finally, to address the issue with SAS on Unix and being able to generate Excel worksheets in a data step, I have an entry on sascommunity.org called SaviCellsPro. That allows a data step to write a simplistic XML structure and then execute a .NET app to convert that into binary Excel workbooks.

There are others but it is a fascinating world. I tend to use everything depending upon the particular client and need...and the 32/64 bit issue has 'bitten' me multiple times now.

Before I forget, the advantage on the .NET side is that we can control things like print options, graphs, OLAP cubes, etc. Things outside of just dumping the data.It is not always needed so that approach is not always the best, however...it is there if needed.

Tricia, the "IOM bridge" is part of SAS Integration Technologies, the SAS product that allows any client application to communicate with a SAS server. It's the mechanism that Enterprise Guide and the SAS Add-In for MS Office use to talk to SAS metadata and workspaces. It can also be used directly by a number of Windows programming technologies: C++, .NET (C# and VB) and even VBA (within Excel). Programmers can find more information in the SAS Integration Technologies Windows Development Guide.

Chris,
I have been using ODSTagsets.ExcelXP in my stored processes, to generate the results in Excel and was always annoyed with the message that comes to warn the users before opening the file in excel. I did some research and found that if I used one of the follwoing two methods, you don't get the message any more
1. Make some registry changes so excel doesn't display the warning message anymore. SAS Usage Note 31956, provides the details, but some places do not allow for registry changes on clients machines so this may not work for them.
2. I feel this is a better option, if you are using ODSTagsets.excelxp in a stored process. You can set the content type of the output file to be "text/xml" using the "stpsrv_header" function, and then generate your xml file. This file opens in excel and here is the fun part, you don't get the warning message in excel when trying to open the file.

I'm using a proc export on my web page and it works for some but it mostly generates a 7kb xlsx file with a tab name of "A266FF2A662E84b639DA" and no data. I've seen this be an issue with Microsoft project but not sas except for me. Has anyone seen this issue? Is there a work around?

I have a web app that allows users to select options and generate an xlsx file. All the data is collected and prepared in sas and then exported. If it is an xls file it works fine but when I change the options to xlsx it doesn't.

Very nice post, also not mentioned yet are the msoffice2k and tagsets.msoffice2k_x markups for ODS which are html variants for Excel books and have some slightly different capabilities than tagsets.excelxp such as embedding images which Microsoft does not allow in their XML markup that excelxp uses.

Fact: in early releases of SAS Add-In for Microsoft Office, tagsets.msoffice2k was used to generate results to pull back into the Office clients. When SAS Report was invented later, it enabled far more flexibility (enabling support for PowerPoint, for example).

But here's one thing you *can* do. Right-click on the data node in the process flow, select Properties. On the Columns tab, select "Copy to Clipboard". You can then paste that into Excel. It is row-oriented though, and not column-oriented.

Chris, “Let me count the ways” is, for me, exactly the problem. I long ago coined the term "Options Over-Choice" for the situation faced by users of SAS (and other) software.

The single best alternative to create a highly formatted report from a SAS production batch application, with the widest capability (even if it sometimes means using an empty, but partially formatted, template workbook to be loaded and formatted further dynamically from SAS code, or the desperate measure of programmatically sending keystrokes to Excel from SAS code), is Dynamic Data Exchange (aka DDE).

This old technology (whose death has been prematurely forecasted repeatedly) offers more single-solution power than any of the partial solutions provided by SAS developers to date.

Various non-SAS-Institute DDE-oriented authors have discussed what I call "SAS-with-Excel Application Development". One covers a complex multi-function SAS macro, another has a toolkit of single-function SAS macros and sample programs, and the initial conspicuous contributor in this domain (Vyverman) covered parts of the general problem in a few early papers.

I’m still hoping for a single, reliable solution created, endorsed, and supported by the SAS development team for programmers to create production batch applications that can create an Excel workbook formatted exactly as desired.

For a quick, one-time dump, PROC EXPORT is great.

For a simple, slightly formatted report, ODS HTML (or ODS HTML3) with a file type of XLS is adequate.

I experiment with ALL of the ODS tagsets for Excel, and inevitably find that whatever is my current choice meets some needs, but not all needs.

Call me lazy, but just give me one way that I can depend on.

Thanks for starting the dialogue on this important topic. The commonest ultimate destination of data prepared with SAS software is an Excel workbook. Almost all computer users have Excel, and it allows them to work with the data further with a tool that they already know how to use. But I like to deliver to the data viewer/user exactly what she/he wants to see. Not more work to do in order reshape the data delivery.

Dr. Bessler, thank you for the thoughtful response. I agree that DDE is an incredibly flexible (and fast) approach to the problem. In my early days at SAS I was tasked with writing some simple, repeatable DDE examples for the SAS Companion for the Microsoft Windows Environment (my first SAS book); I remember the challenge with fondness.

These days, the main obstacle for DDE is not one of technology, but of topology. If you're a SAS professional who provides Excel-based reports as one-off requests, you can manage that all from your desktop SAS environment. But if you need to scale that to run from stored processes, and/or within SAS Enterprise Guide in distributed environment, or within any number of other "enterprise-wide" deployed systems, DDE might not cut it. As you know, DDE relies on Windows messages between two Windows application processes on the same machine. If your SAS server is on one box and Excel is on another, you must settle for one of the other approaches to create your Excel content. DDE is simply not on the menu.

Next to DDE, I believe that the SAS Add-In for Microsoft Office (running in Excel) offers the most out-of-the-box flexibility and control over formatting/behavior. That's because it uses SAS Report to retrieve the substance of your results from SAS, and client-side code (using Excel automation, the practical successor to DDE) to merge that content with your Excel formatting and non-SAS content.

I understand that you are in, or work with, the BI client tool development team, not the ODS development team.

My concern is with production batch applications, not BI client addressable needs.

Until late 2009, I actually did support a production batch application that ran on a BI server, and did use DDE with great success, but I was not really thrilled with the situation. If a SAS DDE program DOES have a problem in that situation, you can end up with a hung Excel session on the remote server (and a locked Excel workbook), and not everyone has an easy way to deal with that.

Lately, my production batch work for other SAS sites has also entailed creating formatted reports in Excel, and I have not turned to DDE. But, because of the incomplete solutions so far available with the (at least three) different ODS tagsets, I am in the situation of not being able to deliver as much as I would like. I once heard the comment that when a programmer (or a provider of anything) says to a client (or a customer) "You're too picky.", it is really just a substitute for "I can't do that."

Please pass on my SAS for Excel thoughts to the ODS development team. Thanks.

I did work on the BI Clients team for many years, and it's true that there is a special place in my heart for SAS Enterprise Guide and SAS Add-In for Microsoft Office. I know the capabilities (and limitations) of those client apps pretty well.

These days I work in SAS Professional Services on a variety of projects. I spend more time programming with SAS than I ever have, and my projects often have less-than-neat requirements. That means that I don't always get to just pick a technology and deliver what works best within that technology. Instead, I have to get creative in order to deliver exactly what meets the requirement. (I'm guessing that sounds familiar :) )

I can appreciate that there doesn't seem to be a single foundation approach from SAS to solve all of the Excel reporting output scenarios -- hence the 10 methods listed in the post. I know that the ODS group is working towards a more comprehensive approach. I will make sure that they see the comments that you've shared.

Hi,
I am using ods tagsets.excelxp to write my SAS results in an Excel. Now as part of the second iteration I want to open the same excel spreadsheet and write the results. All this happens dynamially meaning that I do not know for sure how many rows get populated in that spreadsheet.
The challenge here is that I am not finding any option which allows me to open the same sheet again for writing the results. Any suggestions??

One of the limitations of EXCELXP is that the tagset doesn't create a true native Excel file. It's a format that Excel can read, but not a form that allows you to update the content "in place" until Excel has had a chance to read it in and save it natively.

That said, you can probably get some more information from the experts on communities.sas.com - check out the Office Integration discussion forum.

I am using SAS Enterprise Guide for the first time. I have a process that creates an output file. The name of the output file was created using a macro variable that I capture at the beginning of the process from a prompt to the user. Example - I ask for the Month and Year to use for processing. I use this macro variable fine throughout the process. When it comes to exporting the file to excel I am in EG and have the file open and click on the EXPORT. When it says Export as Step in Project, I click yes and follow the wizard. But it saves the export process with the macro name resolved, so now when I want to redo this project and select another month and year it doesn't know how to do the Export part. Can you help me with this section. I don't want just a generic name, I need the month and year attached to the exported file name. Can you give me a suggestion for how to do that? Thanks

Do you have a way to export the data using code? That is, do you have the ability to run PROC EXPORT in your SAS environment? It's coding (and not point-and-click), but you could use that to pick up your macro variables/prompts.

Also, if you had a way to then download the exported Excel files from your SAS environment, as a step in your process flow, would that help?

Hi Chris,
Can you elaborate a bit more on the statement below.
'PROC EXPORT DBMS=XLS – writes Excel (XLS) files directly, no driver or PC Files Server needed. Has limits on volume and format. Works on Windows and UNIX.'

We are using Marketing Automation (still an old version: 4.4), and would like to be able to use the Excel output for some of our agencies, in stead of doing post-processing.
I am aware of the limit of 65k, but the output becomes unreliable.
I have been able to put out 60k records in a correct way, whereas 50k records had an unrecoverable read error in excel, and the data were the same. When trying to repair, no column headings were in the sheet, and random columns were populated.
Can I perhaps avoid the format and volume limitations?

Tom, there are a few limitations on size and content with DBMS=XLS. That method writes the XLS file directly using a file format that Excel recognizes, although it's not the most recent file format that Excel supports. For ideas about some of the limitations, check the SAS notes related to DBMS=XLS.

Chris,
great article and discussion. What could you say about ODBC, OLEDB and other "pull" strategies Alan Churchill has mentioned? I found pulling SAS data in excel to be very smooth from the process point of view, since you could create multiple views/summaries (using pivot tables for example) graphs etc from the same detailed SAS dataset very quickly in Excel and it is very easy to refresh the presentation. The SAS data set is produced independently from the presentation update, adding flexibility to the process. The downsides I see so far are:
1. dealing with SAS dates (not translated automastically to MS Office "start from 1/1/1900 format")
2. having to set up ODBC libraries (if ODBC method used)
Also if OLE DB providers are used, Microsoft lists multiple providers (SAS Base... SAS IOM... SAS Local... SAS OLAP... SAS share...) not sure what is the difference.

The "Pull" strategy is exactly how the SAS Add-In for Microsoft Office works to pull SAS content (data and reports) into Excel and other clients. Under the covers, it uses the SAS IOM OLE DB provider to connect to a SAS session.

The different providers are related to different types of SAS "endpoints". If you don't have a SAS session to connect to but have a SAS data set file, you can use the Local provider. For IOM servers (SAS Workspace sessions), you use the IOM provider. For SAS OLAP Server connections, you use the SAS OLAP provider (complies with OLE DB for OLAP - ODBO), and for remotely administered SAS libraries in a SAS/SHARE server, you can use the SHARE provider.

I have an excell file with 7 sheets.I have to count these sheets and import into sas datasets. Excell is not installed on my system.I have to compare these sheets with other excell sheets.
Kindly help me to solve this problem.

Hi,
I agree there should be a general approach with good formatting capabilities. Frankly, I am tired of spending hours counting cells to enter in easily misplace column and row numbers into DDE macros. Those ODS tagsets are a pain. Life should not be this annoying.
- SH, faithful programmer

Chris, I have been using PROC EXPORT DBMS=EXCEL REPLACE for a long time. Recently some of my data is not replacing (or updating). I have multiple sheets, some update and some don't. The log says that everything is good (no errors). I am using SAS 9.3 (32 bit) with windows 7, Excel 2007. Have you or anyone else come across this problem?

One of our developers created some software to gather some Oracle data into SAS and then manipulate it according to our business requirements. She converts the output to .xls. But when I open the output file in Notepad, I notice the following:

000001679801821

The Oracle datatype was Text. Somehow, somewhere along the line, it gets changed to Number, which we can't have, because opening the file in Excel causes the leading zeroes to get dropped. The developer says she doesn't think it can be fixed. It's not that I don't believe her, but it just seems odd that it can't be fixed. Any ideas as to what point in the process the datatypes are getting changed? Is it something in her code or is it at point of the export process?

Despite the formatting challenges you had while entering this comment (apologies for that), I think I understand the problem. The Oracle column was meant to be VARCHAR, and perhaps it was brought into SAS that way (or perhaps not, you don't know), but when exported to Excel it was numeric.

Also, you have options for how to read data from Oracle into SAS, if you are using SAS/ACCESS to Oracle (or ODBC). The DBTYPE= option provides this control when you access database tables.

Given your description of the problem, I agree with your intuition: this can be fixed. Your developer may need some guidance, or it might be that the "fix" comes with a trade-off that the developer finds unpalatable.

Very interesting and useful post. I've long used DDE to support two-way data transfer between Excel and SAS, and for formatting the spreadsheet. A couple of points that came to me while browsing the article and the comments:

- Frequently, you'll want to add to an existing spreadsheet, not create a new one. What I'd do with DDE is create a template workbook with some pre-formatted hidden sheet, and use SAS with DDE to do things like duplicate a hidden sheet then write to id (for instance, I had to have a dynamic means of converting web form fields into one sheet per form, and this approach worked well for this.)

- However, today has been the day of big education for me as I discovered that my dream of doing a quick conversion of my SAS/DDE application to something that worked through the Add-In with stored processes that still use DDE wasn't going to work!

- So what I've decided to do is to still use a pre-formatted workbook with hidden sheets, still do most of the number crunching with SAS (using a stored process called via the Add-In), and use Excel VBA in place of DDE to transfer data back and forth to SAS and to format the sheets.

- What I'm missing is a handy set of VBA functions that work with the extensions for the Add-In to do common tasks such as write this SAS dataset to cell A of sheet B; or create a SAS dataset by reading an Excel range. I'm going to develop these myself, but since my VBA expertize is not enormous, if anybody already has written such functions, please advise!

Thanks for your post. It is very informative. I am running proc export dbms=xls code using SAS 9.2 version in unix box. I would like to update an excel workbook. The workbook has data in first sheet and remaining 3 sheets having graphs created using data from first sheet. I am not able to update the data in first sheet using proc export dbms=xls opiton. We don't have PC file server on windows machine to explore other options. Please let me know any other solutions that would help us in updating the existing spreadsheet with proc export in unix environment.
Regards,
Chandra

The tricky part of your problem is updating an existing spreadsheet, which PROC EXPORT does not support in SAS 9.2. This capability has been added in SAS 9.4.

If you have the SAS Add-In for Microsoft Office, you could "turn the problem around" and use Excel to pull the SAS data you need into the proper location in the spreadsheet.

And if you had a PC Files Server, you could potentially use the LIBNAME PCFILES engine to treat your workbook like a collection of tables (each spreadsheet as a table), and replace just one table as needed.

Excellent info. Used to just write scripts in Python for converting from SAS to SPSS. Now have more options. Have always used SAS2SPSS by Chronicon to convert the datasets on Windows, but linux sufficiently more difficult. Thanks for the help!

I've had some problems exporting to Excel through a CSV file. When Excel opens the file, it guesses how to read data, transforming it into what Excel thinks it should be. I wrote a SAS macro to write the CSV file in a way I know Excel will not tamper with. When writing a numeric variable i just write its value. If there is a format on the variable I write the formated value embraced with ="value". Excel will not touch this. It will just be read as a string value. All character variables I write the same way embraced with ="value". You cannot read the CSV file from other programs, but it works very well with Excel. You can alter the file in Excel and save it again without trouble.

Birger, Thanks for sharing this. Your technique is one of many that creative SAS programmers will use when they want to get a certain behavior out of Excel, based on the needs of the downstream processes.

Any hints on how to deal with SAS created multi tab Excel workbooks that always open in Excel Group mode?
As a recent convert to the 64 bit world - Windows 7, Excel 2010 and SAS 9.4 this is a new problem that I did not have in the old 32 bit world.

FILENAME RevX 'K:\\SAS Data Exports\06 DEC\RevMon';

.... data step creating a SAS data set "table" and multiple Proc Export sheets with different permutation of the "table" data set......

Gady, thanks for sharing the information. I'm sure that your tool will be a welcome addition for those looking for additional flexibility.

In the meantime, SAS/ACCESS to PC Files has added more support for XSLX in SAS 9.4, which can work without PC Files Server, and can update and add sheets in existing workbooks. Also, the Maint 1 update for SAS 9.4 (due out very soon) includes an experimental ODS EXCEL destination for creating XLSX files; that's part of SAS Base. Enthusiasts will look forward to playing with that.

I'm trying to export a dataset from work folder to a local folder. But the amount of data is very high (may be 1000000) and its taking long time (more tha 2 hours to export 50000 rows) to export the file in local drive.

Below are the steps I did in the process flow.
1. Script to create the required dataset in work folder.
2. Used the output dataset as step in project and export it as csv to a local drive (c drive)
Can you advise is there a way to export the data quicker?

5 Trackbacks

[...] How to export from SAS to Excel files tags: high-performance analytics, sas enterprise guide Bookmark on Delicious Digg this post Recommend on Facebook Share on FriendFeed Share on Linkedin share via Reddit Share with Stumblers Tweet about it Print for later Tell a friend « How to open a SAS information map in SAS Web Report Studio [...]

[...] The Cause: Your 64-bit SAS process cannot use the built-in data providers for Microsoft Excel or Microsoft Access, which are usually 32-bit modules. In a previous blog post, I've provided a bit of explanation about this limitation. [...]

[...] Q: When you select "Send to Excel" for a data set, is there a way to access the SAS program for that operation? Does it use SAS/ACCESS to PC Files? A: The "Send To" mechanism does not actually use SAS code to do the work -- which is why you don't need the SAS/ACCESS product for this. Instead, SAS Enterprise Guide automates your local instance of Microsoft Excel and populates a new spreadsheet with the selected data set. This is just one of many ways to get your SAS content to Excel. [...]

[…] it still has the "experimental" label in the recently released SAS 9.4M2, I've found it to be a useful addition to the many ways I can create Excel content from SAS. ODS EXCEL offers a couple of big advantages over […]