SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Reply
New Contributor
Posts: 4

Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

I just reinstalled SAS and Microsoft Office on a new computer.  Now I cannot export to Excel probably due to the 32-bit vs 64-bit issue that has been discussed here previously.  I have read about how to export to xlsx files using code.  Here's what I used:

proc export

        data=work.data2

        dbms=xlsx

        outfile="c:\temp\arcsin_seed_pc_lsmeans_allfixed.xlsx"

        replace;

run;

It worked!  However I would prefer to use the "export" button under 'file'.  Is there a workaround that make that possible?

Many thanks for any suggestions!

Community Manager
Posts: 575

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Hi Kristin, are you using University Edition?

New Contributor
Posts: 4

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Good question.  I am not sure.  I am at a University, but I did not get a free student version or anything.  The information that comes up when I start SAS is:

X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Does that tell you anything?  If not, let me know where I should look.

Thanks!

Kristin

Community Manager
Posts: 575

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

If you see SAS under All Programs on your Start menu, then you have a local copy of SAS through your university's license. Glad you were able to continue on your way!

Super User
Super User
Posts: 6,842

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

If the GUI tool doesn't work right for you then don't use it.

Just write the PROC EXPORT command yourself.

Our use the XLSX libname engine.

libname out xlsx "c:\temp\arcsin_seed_pc_lsmeans_allfixed.xlsx";

data out.data2; set data2; run;

libname out clear ;

New Contributor
Posts: 4

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Thanks, Tom, for that alternate way to export to xlsx files.

Cheers,

Kristin

SAS Employee Pav
SAS Employee
Posts: 2

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Hi Kristin,

If you have a local copy of SAS Installed and it says in the log: NOTE: This session is executing on the X64_7PRO  platform.

Then it is 64 bit.

The Microsoft Office is usually 32 bit and doesn't provide to SAS (64bit) a compatible driver for Excel and Access files.

You can install the Microsoft Access Database Engine x64bit 2010 Service Pack 1 to provide the correct driver for 64bit sas sessions.

Previous versions of the driver conflicted with the existing Office installation, but this Service Pack 1 works for me.

http://www.microsoft.com/en-US/Download/details.aspx?id=26605

Via this driver you will be able to use the File>Export>EXCEL or the proc export DBMS=EXCEL.

Best regards,

Paul

Super User
Posts: 7,386

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

The most stable workarounds for this problem are the following (as they do NOT rely on any Microsoft crapware at all; MS's inability to maintain a stable API is the main reason for the many import/export problems)

Reports:

Use ODS TAGSETS.EXCELXP to create a .xml file. This file can hold formatting information, and you can do all kinds of interesting things with data (like several differently structured tables in one sheet).

Data:

Write a .csv file with a simple data step

In both cases, the created file can be read for control (and modified) with a simple text editor.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Kurt I Wouldn't trust the tagsets os SAS either. There are different incompatible versions of that arround and the versioning is not clearly documented.

The best thing would be following the up to date standards as Eg open office at OASIS. The ms types are an extension on those.

SAS is working on direct XML (zipped) access to these ms types. This will avoid any classic interface as being supported for many years 20+. That is amazingly long.

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Kurt I Wouldn't trust the tagsets os SAS either. There are different incompatible versions of that arround and the versioning is not clearly documented.

The best thing would be following the up to date standards as Eg open office at OASIS. The ms types are an extension on those.

SAS is working on direct XML (zipped) access to these ms types. This will avoid any classic interface as being supported for many years 20+. That is amazingly long.

---->-- ja karman --<-----
Super User
Posts: 7,386

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

You see, from experience I do not trust anything coming out of Redmond. If the chairman of MS tells me that the sky is blue, I go outside to check myself.

Tagsets at least produces a .xml that can be checked with an XML parser, if necessary. The MS "extension" of the OASIS is mostly blocks containing "MS tries to do a miracle in here, so don't bother".

I'd rather for SAS to completely drop any MS specific formats and interfaces (or at least don't take part in any future atrocities) and support the real ODF formats from now on (.odt, .ods, ...)

If this sounds polemical, well, that's the result of 30 years of dealing with MS shortcomings. And seeing that it is easily done better (the "old" HP, UNIX, open source, even IBM)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Using "export" under "file" to export to xlsx files despite the 32-bit vs 64-bit issue.

Great.  Thanks for the input, folks!

Ask a Question
Discussion stats
  • 11 replies
  • 2311 views
  • 4 likes
  • 6 in conversation