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

Importing/Exporting Excel Files with 64 bit SAS

Reply
Occasional Contributor
Posts: 5

Importing/Exporting Excel Files with 64 bit SAS

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.

Super Contributor
Posts: 356

Importing/Exporting Excel Files with 64 bit SAS

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

Contributor
Posts: 46

Importing/Exporting Excel Files with 64 bit SAS

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

Super Contributor
Posts: 277

Importing/Exporting Excel Files with 64 bit SAS

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

Occasional Contributor
Posts: 13

Importing/Exporting Excel Files with 64 bit SAS

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

Ask a Question
Discussion stats
  • 4 replies
  • 4180 views
  • 0 likes
  • 5 in conversation