topic Importing/Exporting Excel Files with 64 bit SAS in Microsoft Integration with SAS

Importing/Exporting Excel Files with 64 bit SAS

Cyndie — Wed, 06 Jul 2011 23:28:10 GMT

There is a limit of 255 variables when using proc export to export files between 64 bit SAS and Excel (2007, 2010). SAS reports that the conversion has worked but the files are not readable in Excel. Has anyone else encountered this problem and if so what types of workarounds are you using? We are about to deploy 64 bit windows systems and would like to provide our end users as many mitigations as possible. Thanks in advance.

Importing/Exporting Excel Files with 64 bit SAS

twocanbazza — Thu, 07 Jul 2011 04:22:41 GMT

I know not very helpful for you,

We have come across other issues with Proc Export on Windows 7 and windows 2008 (64bit) envs logged issues but was told not fixing (by way of hotfix) and they are re-writing the EXPORT procedure for 9.3... The recomendation we were given were to use PC File Server to handle the exports.

Quote from TS "Regardless the answer is still the same, you have to use the pc file server to do this well until 9.3. "

Barry

Importing/Exporting Excel Files with 64 bit SAS

AndersBergquist — Mon, 19 Dec 2011 15:10:12 GMT

One solution is to use Enterprise Guide. It will handle import and export to Excel. I have not test the number of variables. There is an add-in to EG from SAS for export to Excel 2007/10 files

Importing/Exporting Excel Files with 64 bit SAS

GreggB — Mon, 02 Jan 2012 20:00:03 GMT

I thought this issue was resolved with 2007 and 2010. What does your DBMS= statement say in your PROC EXPORT step?

Importing/Exporting Excel Files with 64 bit SAS

Maheshvaran — Fri, 09 Mar 2012 15:45:22 GMT

Hi Cyndie,

The SolutionProvided in this article is Perfect with one adjustment for exports via PC Files:

http://support.sas.com/kb/33/228.html

PROC EXPORT DBMS=EXCELCS

DATA = MyLib.MyData

OUTFILE= "\directory\filename.xls"

REPLACE;

SHEET="Sheet-Name";

SERVER="server-name.company.com"

PORT=8621;

VERSION=2007;

RUN;

The Changes are in bold:

- Add version as 2007 for very large files

- leave out the file extention in the outfile name. this helps excel not to get confused with the extentions. if find it creates a .XLSB binary file but the data is perfectly fine!

You could also try the version statement without PC Files server, not sure if this works though!

Please dont hesitate to ask if i have not been very clear about anything....

Kind Regards,

Maheshvaran