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 
  data=sashelp.prdsale 
  dbms=xlsx 
  outfile="c:\temp\prdsale.xlsx" 
  replace;
run;
 
proc import 
  datafile="c:\temp\prdsale.xlsx" 
  dbms=xlsx 
  out=work.prdsale 
  replace;
run;

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

31 Comments

  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

      Richard,

      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?

  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

      Marc,
      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
      data=sashelp.prdsale
      dbms=xlsx
      outfile="c:\temp\prdsale.xlsx" ;
      sheet = sheet1 ;
      run;

      proc export
      data=sashelp.prdsale
      dbms=xlsx
      outfile="c:\temp\prdsale.xlsx" ;
      sheet = sheet2 ;
      run;

      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";

    170
    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: 4.3.0.10196
    SAS System-version: none
    SAS System-kommando: none
    Shared SAS-files: D:\Apps\SAS\SharedFiles
    JMP-installation:
    --- 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

    Chris,

    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

    Chris,

    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

      Mark,

      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]" 
      dbms=EXCEL2000
      replace;
    run;
    
    • Chris Hemedinger Chris Hemedinger
      Posted August 27, 2014 at 8:49 am | Permalink

      Wouter,

      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.

4 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 […]

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> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>