Export to Excel 2010 just got a little bit easier

64

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.

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. He's also co-author of the popular SAS for Dummies book, author of Custom Tasks for SAS Enterprise Guide using Microsoft .NET, and a frequent participant on the SAS Enterprise Guide discussion forum.

Related Posts

64 Comments

  1. Pingback: How do I export SAS to Excel files: Let me count the ways - The SAS Dummy

  2. Richard Audsley on

    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 on

      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 on

        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. 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?

      • 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=
        outfile=
        dbms=xlsx
        replace;
        run;

        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 on

          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 on

    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 on

      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. Pingback: The top gotchas when moving to 64-bit SAS for Windows - The SAS Dummy

  6. Works great; fixes a workflow bind.

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

  7. 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!

  8. 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 on

      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.

  9. Pingback: The best of SAS blogs for 2012 - SAS Voices

  10. 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 on

      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.

  11. Andrew Tschirhart on

    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 on

      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 on

        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 on

          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 on

            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.

  12. 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 on

      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.

  13. 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 on

      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.

  14. Wouter Duerinck on

    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 on

      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.

  15. Pingback: Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports - The SAS Dummy

  16. Pingback: Performing Logistic Regression in R and SAS | Mubashir Qasim

  17. 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 on

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

  18. 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 on

      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.

  19. 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 on

      Dan,

      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.

      • 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"
        style=Pearl
        options(sheet_interval="proc" sheet_label="none" sheet_name="Test");
        proc print data=test noobs; run;
        ods excel close;

        • Chris Hemedinger
          Chris Hemedinger on

          Dan,

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

          ods excel file="c:\test.xlsx"
          style=Pearl
          options
          (sheet_interval="proc"
          sheet_label="none" sheet_name="Test"
          absolute_column_width='20');

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

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

  21. Pingback: SAS and Excel 2010. XLSX. | SAS-Education

  22. 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.)

  23. Hi All,

    Kindly do me a favor.

    How to IMPORT .xlsx file into SAS UNIX environment.

    I tried
    PROC IMPORT DATAFILE="path/abc.xlsx" OUT=ABC DBMS=XLSX REPLACE;
    RUN;

    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.

    Regards,
    Kunal

  24. But it doesn't work for multiple sheet by macro. The new sheet will replace the old one. Is any solution for macro output to xlsx?

    %macro ex(oa,oaname,prod);
    PROC EXPORT DATA=pool_&oa
    OUTFILE="&data_oa.\List for &oaname._20&today..xlsx"
    DBMS=XLSX REPLACE;
    sheet="&prod.1st";
    RUN;
    %mend ex;
    %ex(P,AA,CC);
    %ex(P,AA,PL);

    • Chris Hemedinger
      Chris Hemedinger on

      You don't mention the SAS version you're running. Replacing content at the sheet level is supported in SAS 9.4 -- is that what you're running?

  25. Can someone help getting the following error wile exporting class dataset to local machine.
    Insufficient authorization to access
    /opt/sas/config/Lev1/SASApp_FIN_OPSPAY/\\GBMLVFILFS04N02\Home4$\sahuaad\Profile\Desktop\class.xls.

  26. Can Someone Help Getting the following error while exporting report_daily dataset to local machine.
    ERROR: Temporary file for XLSX file can not be created ->
    /opt/sasinside/SASConfig/Lev1/SASApp/D:\SASUniversityEdition\myfolders\Daily\/MTD_AMDOCS.$$1. Make sure the path name is correct
    and that you have write permission.

    • Chris Hemedinger
      Chris Hemedinger on

      Using SAS University Edition, you must specify the proper path for your shared folder in the PROC EXPORT file out option, i.e., "/folders/myfolders/Daily\/MTD_AMDOCS.xlsx".

Leave A Reply

Back to Top