topic Export to .xlsx on UNIX in SAS Procedures

Export to .xlsx on UNIX

Venkatesh_gajula — Mon, 16 Jul 2012 15:41:48 GMT

I am trying to output a SAS dataset with observations > 65k into a excel file on UNIX server.we have SAS 9.2 TS2M2 on a AIX platform(64 bit).

Tried to export using proc export - DBMS = XLS but only 65k rows are getting output to excel file.

Any work arounds for exporting datasets with > 65k to a excel file on Unix.

Thanks,

G.Venkatesh.

Re: Export to .xlsx on UNIX

SASKiwi — Mon, 16 Jul 2012 20:02:59 GMT

You should try DBMS = XLSX. This is the new XML standard introduced in Excel 2007 that enables you to write more than 64,767 rows to a spreadsheet.

What version of Excel will you be using to read these spreadsheets? There are other issues related to whether your version of Excel is 32-bit or 64-bit. Excel 2007 is 32-bit, Excel 2010 is either 32-bit or 64-bit. You may also require the SAS PC File Server software to handle 32-bit to 64-bit translation.

Check out the numerous SAS notes on these topics.

Re: Export to .xlsx on UNIX

Venkatesh_gajula — Mon, 16 Jul 2012 22:01:28 GMT

This option of DBMS=XLSX does not support in SAS 9.2. i still tried to run it and got the error.

Re: Export to .xlsx on UNIX

SASKiwi — Tue, 17 Jul 2012 01:58:52 GMT

Sorry, you are correct. XLSX is not supported until 9.3 TS1. As a quick workaround you could export as a CSV, then ensure that it is imported by Excel 2007 or later.

Re: Export to .xlsx on UNIX

Venkatesh_gajula — Tue, 17 Jul 2012 02:16:41 GMT

Yeah... looks like there is no way to get this done,because i have to export to multiple sheet in single excel workbook.