Export to Excel 2007/2010 using SAS Enterprise Guide

Since its 4.2 release, SAS Enterprise Guide has been able to import Microsoft Excel 2007 and 2010 spreadsheet files (usually encountered as .XLSX files). But while SAS Enterprise Guide can export XLS files (which are compatible with all versions of Microsoft Excel), it does not have the ability to export to a native .XLSX file.

Customers see this as a limitation. The Microsoft Excel 2007 format allows for more rows and columns than traditional XLS files can support, and SAS Enterprise Guide does not offer a point-and-click method to create output results in this new format.

In SAS 9.2, you can use PROC EXPORT or the EXCEL LIBNAME engine to write data in this new format, but using PROC EXPORT requires the customer to write a custom SAS program, and both PROC EXPORT and the EXCEL LIBNAME engine require you to license and install SAS/ACCESS to PC Files.

New Custom Task: Export Data to Microsoft Excel 2007/2010
To help customers to work around this limitation, SAS R&D has created a custom task that focuses on one thing: export your SAS data to a Microsoft Excel 2007 (.XLSX) file. Here is an example screen shot of the task:

The task supports the following features/options:

  • Works only with SAS Enterprise Guide 4.3
  • Export to XLSX or XLS file
  • Specify the name of the worksheet in the exported file
  • Specify which columns to include in the exported file
  • Specify the output format (as a SAS format) for each column. This is especially useful for applying special treatment to date and date-time values to preserve the correct format in the spreadsheet.
  • Optionally place field headings on the first row of the exported sheet. You can also use labels instead of variable names.
  • Optionally create multiple worksheets if the row count exceeds the maximum supported by Microsoft Excel
  • Optionally create multiple worksheets if the column count exceeds the maximum supported by Microsoft Excel

Note: This task uses "native" technology to do its work: SAS/ACCESS to PC Files is not required. However, this also means that the task does not generate a SAS program that can be run independently outside of SAS Enterprise Guide or within a stored process.

Download the custom task from this location (ZIP file, 473KB). Read the README PDF file in the ZIP archive to learn how to install and use it with SAS Enterprise Guide 4.3.

Update: There is also a 5.1 version of this task available via SAS Note 41132.

tags: excel, excel 2007, export, SAS 9.2, SAS custom tasks, SAS Enterprise Guide, SAS tips, SAS/ACCESS PC Files, xlsx

25 Comments

  1. Magnús Helgason
    Posted March 31, 2011 at 10:50 am | Permalink

    Do you know if it's possible to export to multiple sheets with this add-in?

  2. Chris
    Posted March 31, 2011 at 1:51 pm | Permalink

    Yes, it does support multiple sheets for "overflow" situations.

    It does not support adding sheets to an existing workbook, or breaking out the sheets by a BY value.

  3. Alain
    Posted November 2, 2011 at 1:04 pm | Permalink

    You said "It does not support adding sheets to an existing workbook".... do you know if it will soon or if there is another way to do that now? I have an Excel workbook (2010) with a lot of formulas and stuff in worksheets that all depends on the data that I want to update in another worksheet....thanks.

    • Chris Hemedinger Chris Hemedinger
      Posted November 2, 2011 at 1:05 pm | Permalink

      I don't think that will be added to this task in the near term, but soon SAS 9.3 will have better native support for XLSX files using PROC EXPORT, and this might be covered there.

  4. munna
    Posted December 29, 2011 at 3:21 am | Permalink

    Hi,

    can we export sas data into existing excel file sheet which is having specific template. i know we can do in sas 9.2 using DDE but sas enterprise guide doesn't support DDE. so do you know any other way to do this task.

    • wenming
      Posted March 6, 2012 at 10:18 am | Permalink

      Hi Munna,

      You have 3 way to export sas data into existing excel file sheet which is having specific template.

      1. SAS 9.2 (DDE)
      2. SAS enterprise guide ( submit SAS DDE program selected local, you still need to setup...)
      3. Submit an application (you don't need to write SAS DDE), I am using it to replace SAS DDE.

      • mbeu
        Posted July 5, 2012 at 3:40 pm | Permalink

        Hi wenming,

        What type of application do you submit with Enterprise Guide to replace the DDE.
        Your feedback on these would be greatly appreciated.
        Cheers!

  5. shaily
    Posted March 2, 2012 at 11:13 am | Permalink

    Chris, I use SAS on VMS instead of Unix to generate the reports. Currently reports comming in .XLS format and wanted to convert them into .XLSX format. It use ODS HTM to generate the report. I tried ODS EXCELXP to generate .XLSX report but go an error says improper use of function, any idea how to fix this.

  6. Andrei Mitiaev
    Posted April 4, 2012 at 6:36 pm | Permalink

    Is there any plans in SAS R&D to create custom import task for Excel 2007/2010 .xlsx files?

    • Chris Hemedinger Chris Hemedinger
      Posted April 4, 2012 at 8:45 pm | Permalink

      SAS Enterprise Guide 4.2 and later can already import XLSX files, and the import wizard provides a great deal of control over how the file is processed.

  7. philip sumner
    Posted April 12, 2012 at 5:15 am | Permalink

    One of the problems I find using Enterprise Guide over base sas is its inability to export more than one table to a single spreadsheet in different sheet tabs or as tables in a database, Enterprise guide wants to create a new spreadsheet or database for each sas table exported

    in base sas the export wizard gives you the option to append and existing file so you can store another dataset in an existing spreadsheet or database, are sas ever going to include the append option into its export tasks rather than just a replace option?.

    • Chris Hemedinger Chris Hemedinger
      Posted April 12, 2012 at 9:07 am | Permalink

      Philip,

      If you're willing to write your own code, of course you can accomplish these tasks by using PROC EXPORT and other means. When EG exports to Excel directly, it uses a different mechanism than SAS programs (and thus doesn't use/require SAS/ACCESS to PC Files).

      Another method that provides even more control is to use the SAS Add-In for Microsoft Office within Excel itself. You can pull SAS data and results (such as from a SAS stored process) into any spreadsheet location, and also apply your custom formatting.

      As to whether SAS will ever include an append option in a wizard/task form within EG...I agree this would be useful, but I'm not sure if it's on a roadmap. You can make your suggestion known formally by submitting it to Tech Support.

      • Merl
        Posted April 29, 2013 at 11:37 am | Permalink

        Hello Chris,
        I'm using add-in SAS for MS Excel but i have a large table with 67 076 lines and i can't print it on excel because of an error. It is said that Add-in found an error and i must restart. I would like to know if it is the way of the length of my data.
        Thank

        • Chris Hemedinger Chris Hemedinger
          Posted April 29, 2013 at 1:28 pm | Permalink

          Merl, this sounds like an issue that you should track with SAS Technical Support. Here's the link to begin a track -- the consultants will be happy to help you.

  8. Yelitze
    Posted January 16, 2013 at 9:41 am | Permalink

    I do not understand how to export to excel 2007. I can not find the option in the menu of SAS. Please help me!

    • Chris Hemedinger Chris Hemedinger
      Posted January 16, 2013 at 9:48 am | Permalink

      There are several methods, and which is best depends on your software, configuration, and needs. See How do I export to Excel for more details.

  9. Prashant
    Posted May 6, 2013 at 12:31 pm | Permalink

    Thanks Chris for this Custom Task.

    I see a small glitch in the Task when i export a dataset containing column having amount with $ symbol and decimal places. For eg while Exporting the dataset SASHELP.CARS using this task to an Excel 2010 Worksheet display the values in the excel as for eg : $36,945{.00} with the {} brackets instead of just $36,945.00. Can this be fixed?

    • Chris Hemedinger Chris Hemedinger
      Posted May 6, 2013 at 3:45 pm | Permalink

      Prashant - I suggest you track this with SAS Tech Support -- providing a good test case -- to see if it can be addressed.

  10. Posted August 30, 2013 at 6:27 pm | Permalink

    Chris - How do i export to excel files using proc export inside a code in the process flow? I 'm running on sas eg 5.1 and have pcfiles installed and open while runnig the export step. Below is my code.

    proc export data=abc
    outfile="c:\test.xlsx"
    dbms=excelcs
    REPLACE;
    sheet="sheet1";
    Server= '123.000.00.000';
    Port = XXXX;
    run;

    Thanks!

  11. Ram
    Posted January 7, 2014 at 5:25 am | Permalink

    Hi Chris,

    I have applied this and tried to export into .XLSX file using SAS EG 4.3 version.

    but end with below error:

    An internal error has occurred. The error details are:
    Unspecified error: -2147417848(0x80010108)

    • Chris Hemedinger Chris Hemedinger
      Posted January 7, 2014 at 10:14 am | Permalink

      Ram, that error code indicates (I think) that your SAS session was lost/disconnected. I suggest that you follow up with SAS Technical Support.

  12. Fani
    Posted September 1, 2014 at 3:58 am | Permalink

    Hi all,

    Can anyone please help me to fix this?
    Being a beginner,I'm using SAS university edition and exporting a file to excel (.xlsx).

    the syntax goes as below.

    proc export data=sashelp.class
    dbms=xlsx
    outfile="C:\Users\dell\Desktop\immi.xlsx"
    replace;
    run;

    error: xlsx file cannot be created, make sure the path is correct and that you have write permission.

    I have msoffice 2010 installed in my computer.

    thanks for helping me to fix this.

    fani.

    • Chris Hemedinger Chris Hemedinger
      Posted September 1, 2014 at 5:22 pm | Permalink

      Fani, try changing the OUTFILE destination to:

      outfile="/folders/myfolders/immi.xlsx"
      

      The SAS University Edition uses a Linux VM under the covers, so you must express file paths using Linux/UNIX conventions.

One Trackback

  1. [...] 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, [...]

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>