Export to Excel 2010 just got a little bit easier

In case you missed it, the first maintenance release for SAS 9.3 was recently released. Because we're all friends here, you may call it "SAS 9.3M1" for short.

Maintenance releases are usually about fixing problems that SAS has found or that customers have reported. However, sometimes a new capability might sneak out the door along with it. That's what happened this time with improved XLSX support in PROC EXPORT.

Now for the first time, this round-trip import/export cycle works just as you would expect with no additional setup required:

proc export 
proc import 

Remember: using the IMPORT and EXPORT procedures to read and write Microsoft Excel files requires the SAS/ACCESS to PC Files module. With these latest changes, you can get the job done without setting up a PC Files Server, even on systems that don't have a 32-bit Windows architecture.

Prior to this support, most customers who run SAS for 64-bit Windows or on a UNIX platform would need to use DBMS=EXCELCS for this operation. The EXCELCS method works by delegating the Excel read/write operation to another Windows node that has a PC Files Server instance. (On 64-bit Windows, you can make it work with very little configuration using the "autostart" capability.)

The PC Files Server is still very useful for other scenarios, such as supporting the PCFILES LIBNAME engine, which can read and write Microsoft Excel and Microsoft Access files, among others.

And if you're looking for a point-and-click method for creating XLSX files in SAS Enterprise Guide, we cracked that nut a while ago.

tags: excel 2007, pc files server, sas 9.3, SAS/ACCESS PC Files, xlsx


  1. Posted January 28, 2012 at 8:11 am | Permalink

    Thanks! I have been struggling with this issue!

  2. Richard Audsley
    Posted February 14, 2012 at 10:37 pm | Permalink

    So, does this latest release fix the known issue with corruption of named ranges in xlsx/xlsm files under 9.2? From the track I had running a while ago, this was due to an issue with the MS Jet Engine which I am guessing has been fixed in 9.3M1.

    • Chris Hemedinger Chris Hemedinger
      Posted February 14, 2012 at 11:15 pm | Permalink


      I cannot say for certain whether this fixes the issue you saw. It's true that this update allows you to create an XLSX file without invoking the Microsoft drivers (which seemed to be to blame for the issue you reported). But I'm not sure that you can use this method to accomplish your objective...especially if you are wanting to create a macro-enabled file (XLSM). That question might be work tracking again with SAS Technical Support.

      • Richard Audsley
        Posted February 17, 2012 at 12:42 am | Permalink

        Thank you Chris. I don't have 9.3M1 yet - will need to wait until it is deployed at our site. In the meantime, I will stay with xls files for proc export, or a modified version of exportToXL (DDE maco suite) for xlsx or xlsm. Unfortunately exportToXL doesn't work with hidden worksheets.

  3. Ganesh
    Posted April 20, 2012 at 2:30 am | Permalink

    I'm facing some problem in creating EXCEL file under LINUX thru proc export.
    Can you please suggest me the best way to create or export data to excel on LINUX platform itself?

    • Chris Hemedinger Chris Hemedinger
      Posted April 20, 2012 at 8:25 am | Permalink


      If you have SAS 9.3 Maint 1, then you can use the XLSX method described in this post. For a more complete survey of all of the possibilities, check out "How do I export Excel files, let me count the ways".

      • Inipat
        Posted February 6, 2015 at 1:31 am | Permalink

        Hi Chris,
        I want to write xlsx file in unix server using unix sas code. My Unix Sas aricheture is ts1m1 64 bit and I am having 32 bit excel microsoft 2007. When I am using below code for exporting the xlsx file in unix server location.

        Code used :
        proc export data=

        xlsx file getting created in the unix location and after ftping to the local machine using ftp application. I am getting this error.

        'Excel found unreadable contents in 'filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes

        Could you please help me on this

        • Chris Hemedinger Chris Hemedinger
          Posted February 6, 2015 at 9:51 am | Permalink

          When you FTP the file, are you certain that you are using BINARY mode as the transport? If not, the file could become corrupted in the transfer.

          XLSX files are ZIP-formatted archives, so you could try to open in a tool such as 7Zip or WinZip to make sure the file is at least a valid archive.

  4. Marc Schlessel
    Posted April 23, 2012 at 1:53 pm | Permalink

    Chris... What would my proc export statement look like if I have SAS V9.1.3 and want to export to Excel 2010 because that's what I have on my PC?

    • Chris Hemedinger Chris Hemedinger
      Posted April 24, 2012 at 8:19 am | Permalink

      In SAS 9.1.3 there is not native support for writing to XLSX files. You can still use DBMS=EXCEL of course, but you will still have the limitations of the XLS format: 65K rows, etc.

      As an alternative, you can use DBMS=CSV, ODS TAGSETS.EXCELXP, or ODS CSV output. I have a more complete list of methods available here.

  5. subsci
    Posted July 24, 2012 at 5:14 pm | Permalink

    Works great; fixes a workflow bind.

    But how does one specify a particular sheet (if it's not the first worksheet in the workbook)

    • Chris Hemedinger Chris Hemedinger
      Posted July 25, 2012 at 8:02 am | Permalink

      Check out many options for Excel export flexibility in this post.

    • Tomas Mosquera
      Posted November 15, 2012 at 6:43 pm | Permalink

      Subci, you currently can't specify a particular sheet to export to or to import from by using SAS Interface to PC Files. You'd need to download and install SAS 9.3 PC Files Server.
      All you need to know is explained here: http://support.sas.com/kb/43/802.html

      Good luck

    • Jessica Smith
      Posted June 20, 2014 at 11:23 am | Permalink

      I have found the following allows me to create worksheets within the same workbook (using the code example in the original post):

      proc export
      outfile="c:\temp\prdsale.xlsx" ;
      sheet = sheet1 ;

      proc export
      outfile="c:\temp\prdsale.xlsx" ;
      sheet = sheet2 ;

      I hope this helps someone, as it was a lifesave for my team! Thanks!

      • Chris Hemedinger Chris Hemedinger
        Posted June 22, 2014 at 2:43 pm | Permalink

        Jessica, thanks for sharing. I believe that the ability to add sheets to an existing workbook is a new feature in SAS 9.4.

      • SS
        Posted July 30, 2014 at 1:15 pm | Permalink

        Works great!

  6. Matt
    Posted September 6, 2012 at 3:23 pm | Permalink

    Is there a list of options for this output type? I can't find any documentation on it. The defaults works great, but I'd love to write to a specific worksheet, add autofilters, freeze the first column and/or row, etc. Thanks!

    • Chris Hemedinger Chris Hemedinger
      Posted September 10, 2012 at 9:16 pm | Permalink

      I think what you want is the features of the TAGSETS.TABLEEDITOR, which I didn't mention in this post. That has the ability to create more control over the output columns like you're asking for.

  7. Bjorn
    Posted September 25, 2012 at 12:26 pm | Permalink

    Sorry, Doesn't work...

    38 %LET filrf = "/sas/sasdata/sashome/41000769/bilstod/2012/&prognos/Utbet_exc.xlsx";

    171 proc export data=bet5_3
    172 outfile=&filrf
    173 dbms=xlsx replace;
    ERROR: DBMS type XLSX not valid for export.

    --- SAS-programvara ---
    SAS Enterprise Guide-version:
    SAS System-version: none
    SAS System-kommando: none
    Shared SAS-files: D:\Apps\SAS\SharedFiles
    --- Actual process ---
    Actual catalog: d:\apps\sas\enterpriseguide\4.3
    Commandorow: d:\apps\sas\enterpriseguide\4.3\SEGuide.exe
    Space for tempfiles: C:\TEMP\7\SEG55896
    Memory used: 202 633 216 bytes
    Type of clientsession: Terminal services client
    Runs in WOW64 (32-bit subsystem): Nej
    --- Operativsystem ---
    Namn: Windows 2003
    Version: 5.2.131072.3790
    Servicepack: Service Pack 2
    .NET Runtime Version: 2.0.50727.3634 (SP 2)
    Internet Explorer-version: 6.0.3790.3959
    Systemkatalog: C:\WINNT\system32
    Standardwebreader: Citrix Metaframe Server FTA
    MDAC-version: 2.82.3959.0

    • Chris Hemedinger Chris Hemedinger
      Posted September 25, 2012 at 2:58 pm | Permalink

      In order for this to work, you need SAS 9.3 maint 1 (at least) and SAS/ACCESS to PC Files on the SAS server. It's not clear from your log whether you have that; I suggest redirecting follow up questions to SAS Technical Support.

  8. Dave Devoll
    Posted March 13, 2013 at 9:18 am | Permalink

    Is there any way to pass SAS formats to Excel when using PROC EXPORT DBMS=XLSX? I'm exporting a dataset with some PERCENT-formatted columns, but they just show up as raw numbers in Excel.

    • Chris Hemedinger Chris Hemedinger
      Posted March 13, 2013 at 10:19 am | Permalink

      If you want to stick with DBMS=XLSX, you might try creating a character-formatted column with put(col,percent5.) - for example - and see if that carries over.

      Otherwise, you can use ODS tagsets.EXCELXP or LIBNAME EXCEL (or LIBNAME PCFILES) to put your data to a spreadsheet and perhaps retain the formatting you want. See this blog post for an inventory of various methods.

  9. Andrew Tschirhart
    Posted November 25, 2013 at 2:19 pm | Permalink


    Thanks so much for this helpful tip! I notice that dbms=xlsx seems to apply some sort of compression to the xlsx file, since when I open it up and immediately resave with a different name, the new file is larger. Do you know anything about this?

    • Chris Hemedinger Chris Hemedinger
      Posted November 25, 2013 at 2:27 pm | Permalink

      Andrew, I don't know what would make the file larger, exactly...but here's what I do know. The XLSX file format is a ZIP archive, compressed already. SAS writes to this file directly, so it creates a no-frills version of your SAS content for use in Excel. When you open and resave the file in Excel, perhaps Excel "enriches" the file with additional information, such as formatting instructions, so the resulting file is a bit larger.

      That's my made-up answer. It might be at least partially correct.

      • Andrew Tschirhart
        Posted November 25, 2013 at 2:40 pm | Permalink

        Thanks. I guess my particular problem is that when I try to pass SAS-created xlsx files as attachments through outlook to colleagues, they get quarantined for "bad compression", but if I open them, resave, and then attach to an email, they go through fine...could be something specific to my work environment.

        • Chris Hemedinger Chris Hemedinger
          Posted November 25, 2013 at 2:54 pm | Permalink

          Andrew, it could be that the XLSX file doesn't "smell" like a true XLSX file to your e-mail filters, since it wasn't created with the Microsoft tools. It might be interesting to examine the file versions, before and after the re-save operation. You could work with SAS Tech Support on this and if a change needs to be made to DBMS=XLSX processing, they can help get that in the system.

          • Andrew Tschirhart
            Posted December 7, 2013 at 5:32 pm | Permalink

            Chris, thanks for your help. I was not able to comprehensively follow up on the issue, but I believe that it was a problem with my work environment unrelated to SAS.

  10. Babu
    Posted December 9, 2013 at 4:47 am | Permalink

    Hi Chris,
    The above syntax is not working for Mainframes SAS, is there any specific solution to export the data in excel 2007 file (.xlsx) in SAS mainframes...
    Thanks and Regards

    • Chris Hemedinger Chris Hemedinger
      Posted December 9, 2013 at 9:43 am | Permalink

      Babu - I see that z/OS is not listed in the support matrix for PROC EXPORT and XLSX. You can still use ODS TAGSETS.EXCELXP, or straight-line CSV. If you need another option, please contact SAS Technical Support for some ideas.

  11. Mark Ambrose
    Posted April 22, 2014 at 11:57 am | Permalink


    We just installed 64-bit SAS 9.4 (TS1M0) under Windows 7. Using PROC IMPORT with DBMS=XLSX works, except that the RANGE statement seems to be ignored and always the first worksheet in the Excel file being imported. This makes it pretty useless when I want to import multiple worksheets from a single Excel file. So, I am sticking with using DBMS=EXCELCS with the 32-bit PC Files Server installed, which seems to work fine with a RANGE statement. Any chance of a fix so that the RANGE statement will work anytime soon?

    • Chris Hemedinger Chris Hemedinger
      Posted April 22, 2014 at 12:02 pm | Permalink


      The XLSX method received some updates in SAS 9.4 TS1M1. I believe the RANGE= works better, plus there is support updating/adding a sheet to an existing workbook.

      I suggest confirming this for your scenario with SAS Technical Support, and then pursue the M1 update if you can.

  12. Wouter Duerinck
    Posted August 27, 2014 at 7:03 am | Permalink

    With SAS 9.2 this simply does the trick as well

    proc export 
      data=[your dataset]
      outfile="[output folder & filename with extension .xlsx]" 
    • Chris Hemedinger Chris Hemedinger
      Posted August 27, 2014 at 8:49 am | Permalink


      Interesting find! I think in this case, it's the XLSX extension that is triggering the file format. If you specify the same code (EXCEL2000) and an XLS file extension, you'll see the file format is different.

  13. Habib
    Posted September 24, 2014 at 5:19 pm | Permalink

    Thank you so much Chris your tip works perfectly

  14. Lekgari
    Posted February 13, 2015 at 8:35 am | Permalink

    I get the following error message when trying to import data from excel. I am using SAS 9.4 and my desktop is running on windows 7 with 64 bit OS.

    Proc import out=MHET112_A1
    192 datafile= "C:\Users\.....\Documents\MHET112_A.xlxs"
    193 dbms=xlxs
    194 replace;
    ERROR: DBMS type XLXS not valid for import.
    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE IMPORT used (Total process time):
    real time 0.00 seconds
    cpu time 0.00 seconds

    195 getnames=yes;
    196 run;

    • Chris Hemedinger Chris Hemedinger
      Posted February 13, 2015 at 3:30 pm | Permalink

      Are you sure that the file extension isn't XLSX? The proper file extension for most Microsoft Excel files has the "S" before the final "X".

  15. Loc
    Posted February 19, 2015 at 2:12 am | Permalink

    Hello, after the file import with XLSX I have very strange columns behaviour - length of the actual data is equal to the length of the column, for example I try to create new integer column from string and get the following note:
    NOTE: Invalid numeric data, '22247...' and new column is empty after that

    I concatenated '!!!' to all values and got the following result - '22247 !!!' - can't compress it either. Do you ever saw such a thing?

    • Chris Hemedinger Chris Hemedinger
      Posted February 19, 2015 at 6:08 pm | Permalink


      How are you importing this XLSX file? Using SAS Enterprise Guide and the Import Data task? Or are you using PROC IMPORT? If you can supply more details, I suggest that you post the question to communities.sas.com. The experts there can probably answer your question quickly.

      • Loc
        Posted February 20, 2015 at 7:39 am | Permalink

        It was PROC IMPORT. Will try to past it there, thanks

  16. rabab
    Posted February 25, 2015 at 7:24 pm | Permalink

    The code works good but,,, when I open the excel file a msg pops up that the file is corrupted and I couldn't open it!!

    • Chris Hemedinger Chris Hemedinger
      Posted February 26, 2015 at 2:27 pm | Permalink

      If you want to follow up on this error, post in communities.sas.com with the details of the code you're running, the version of SAS that you have installed, and the error message. Or, open a track with SAS Technical Support.

  17. dan liu
    Posted June 7, 2015 at 9:59 pm | Permalink

    How do I change the column width? The width of each column looks either too narrow or too wide in Excel. How do I change it in the Proc Export statement? Thanks!

    • Chris Hemedinger Chris Hemedinger
      Posted June 8, 2015 at 7:42 am | Permalink


      PROC EXPORT simply exports the data, and doesn't tell Excel how to format the appearance at all.

      There are two other mechanisms that provide a little bit more control. First, you could try ODS EXCEL, which has several appearance-related options. ODS EXCEL is new in SAS 9.4. Or, you could try the new LIBNAME XLSX engine -- it doesn't have formatting options but since it creates a native XLSX file, the output format might look a little bit better.

      • Dan
        Posted June 8, 2015 at 2:34 pm | Permalink

        Thank you very much! Here is my code. The problem was the text in a couple of columns got truncated in the Excel 2010. But these columns look very nice in SAS 9.4 dataset. The columns weren't truncated in SAS; but it's trruncated after ods to excel. Please help!

        ods excel file="c:\test.xlsx"
        options(sheet_interval="proc" sheet_label="none" sheet_name="Test");
        proc print data=test noobs; run;
        ods excel close;

        • Chris Hemedinger Chris Hemedinger
          Posted June 9, 2015 at 8:31 am | Permalink


          You can use additional options to influence the column widths. For example:

          ods excel file="c:\test.xlsx"
          sheet_label="none" sheet_name="Test"

          I don't think there is an "autofit" option.

  18. Shan
    Posted January 13, 2016 at 2:27 pm | Permalink

    I used your code and it works, but I have first few lines of comments in the excel before the data.
    Is there a way to tell MIXED data in the file. That way it will only copy the data into dataset instead of comments as variable.

    proc import

    • Chris Hemedinger Chris Hemedinger
      Posted January 17, 2016 at 2:03 pm | Permalink

      Try the DATAROW= statement in PROC IMPORT, which tells the procedure where to begin reading.

  19. Dawn
    Posted March 4, 2016 at 11:37 am | Permalink

    Hi. I'm using this to export, and it works well except that if I export more than 1 spreadsheet to a workbook, the spreadsheets are all linked. If I don't remember to unlink them, any changes in one affects all the others. Is there some way to stop this? (It happens both in a plain proc export and a ODS export to xlsx; it never happened when I was using xls.)

    • Chris Hemedinger Chris Hemedinger
      Posted March 4, 2016 at 12:59 pm | Permalink

      I wasn't aware the sheets become linked. Can you share specifics about how you are creating these XLSX files? Perhaps post the details in SAS Support Communities -- your fellow users will test and advise!

  20. Kunal
    Posted March 22, 2016 at 10:27 am | Permalink

    Hi All,

    Kindly do me a favor.

    How to IMPORT .xlsx file into SAS UNIX environment.

    I tried

    But I am getting ERROR: DBMS type XLSX not valid for import.

    NOTE: In DBMS I also used DBMS=EXCEL or DBMS=EXCEL2000 but getting same error.

    Kindly give your valuable inputs.

    Thank you.


    • Chris Hemedinger Chris Hemedinger
      Posted March 23, 2016 at 11:29 am | Permalink

      First, you need SAS/ACCESS to PC Files -- can you verify you have that with proc product_status?

      Next, DBMS=XLSX works with SAS 9.4 reliably, but not earlier. For that, you might need to use DBMS=XLS. But for XLSX files, you probably would need to set up a PC Files Server (again, this is only for releases earlier than SAS 9.4).

  21. Brian P. Mulenga
    Posted May 2, 2016 at 4:42 pm | Permalink

    Enormous thanks, this code worked wonders for me. Thanks once again

6 Trackbacks

  1. [...] PROC EXPORT DBMS=XLSX – new in 9.3M1, writes Excel 2010 files (XLSX format) directly. No driver or PC Files Server needed. Works on Windows and UNIX. [...]

  2. [...] Alternatively, you can try DBMS=XLSX to remove the data providers from the equation. [...]

  3. By The best of SAS blogs for 2012 - SAS Voices on December 27, 2012 at 5:24 pm

    [...] Export to Excel 2010 just got a little bit easier Bookmark on Delicious Digg this post Recommend on Facebook Share on FriendFeed Share on Linkedin share via Reddit Share with Stumblers Tweet about it Share on xing Print for later Tell a friend « SMBs: Five technology trends to look out for in 2013 [...]

  4. […] of columns and many thousands of rows. In my experience, this can bog down ODS EXCEL; I find that PROC EXPORT with DBMS=XLSX performs much better. So...I decided to see if I could combine the two approaches to create a […]

  5. […] In SAS, I imported it using PROC IMPORT. […]

  6. By SAS and Excel 2010. XLSX. | SAS-Education on February 9, 2016 at 11:21 am

    […] P.S.Изначально пример взят от сюда:http://blogs.sas.com/content/sasdummy/2012/01/25/export-to-excel-got-easier/ […]

Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>