Problem with PROC EXPORT

[KarthikDeepak]

Hi,

I am trying to transfer the contents using PROC EXPORT using the below code:

PROC EXPORT DATA=last

OUTFILE='/home/data.xls'

DBMS=XLS REPLACE;

SHEET="data";

RUN;

My log says the file was successfully created but when I try to open the sheet, it gives an error saying :

Excel found unreadable content in 'data.xls'.

Could anyone help me out here.

My original goal is to transfer the contents of sas dataset to excel and then feed it as an input to pivot table on the same workbook on a different sheet.

So I need to only replace the data sheet in order to have the pivot structure as it is. So only PROC EXPORT has the option to replace sheet.

Any ideas on this??

[Jagadishkatam]

Hi Karthik,

The code what you have written is perfectly fine, the problem occurs when you try to open the excel in excel 2007 and excel 2010. If you try to open the same file with excel 2003 it will open without any problem.

May i know which is the excel version you are using (2007 or 2010).

To avoid the problem add version=2003 to the code.

PROC EXPORT DATA=last

OUTFILE='/home/data.xls'

DBMS=XLS REPLACE;

SHEET="data";

version=2003;

RUN;

Hope this works for you.

Good Luck

Thanks,

Jagadish

[KarthikDeepak]

Hi Jagadish,

Thanks for your time. I have already tried that before and still get the same error.

I am running the code on unix server and trying to open the sheet through Citrix.

And it is Office 2010 suite.

My prime task is to replace an excel sheet in workbook, without recreating it again( recreating would delete the pivot structure which is ther on another sheet on same workbook)

Is there any other way to replace a sheet? ODS TAGSETS doesnt seem to have a replace sheet option. Any ideas?

Karthik

[Jagadishkatam]

Hi Karthik,

did you try exporting the file to dbms=xlsx

Thanks,

Jagadish

[KarthikDeepak]

Hi Jagadish,

I get an ERROR: DBMS type XLSX not valid for export.

[AncaTilea]

HI.

Could you please try to change the extension of your file -- that you try to export to

"/.../data.xlsx"

and use DBMS = EXCEL;

PROC EXPORT DATA=last

OUTFILE='/home/data.xlsx'

DBMS=EXCEL REPLACE;

SHEET="data";

version=2003;

RUN;

Good luck!

Anca.

[KarthikDeepak]

Hi Ance,

I still get an ERROR: DBMS type excel not valid for export

[AncaTilea]

what happens when you remove the DBMS option?

PROC EXPORT DATA=last

OUTFILE='/home/data.xlsx'

DBMS=EXCEL REPLACE;

SHEET="data";

version=2003;

RUN;

[KarthikDeepak]

Hi Anca,

Would it be possible without DBMS?

It says Server Name is invalid or missing...

Karthik

[Reeza]

This is a bug in SAS and Excel. If you search back last week on this forum I posted a link to the tech support note that describes this error.  I used a libname statement and the excel engine but I don't believe that works on servers. You can try the XLSX engine.

For what I was doing and on my Windows system to export to an excel sheet that replaced the values so that the link functioned, I needed to have the exact range as a named range in my excel sheet. This included the correct columns and all rows.  If the export went beyond the range then the excel file had an error.

You'd still need to refresh the pivot table with this method anyways.

[KarthikDeepak]

Hi Reeza,

I have come around that problem of auto refreshing the pivot from the data sheet.

I have used a formula to get the range of rows n columns and even if they would increase or decrease, the pivot would still refer to the range(after setting refresh data while opening file option in pivot)

I have tested this and it works.

I am using the formula: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Now my only concern is to replace the data sheet and not replacing the excel workbook as a whole which would collapse my pivot on the other sheet.

Thanks,

Karthik

[Soumyaa]

I used this code and it's working fine for me in MS Office 2010.

PROC EXPORT DATA=last

OUTFILE='/home/data.xlsx' DBMS=XLSX

REPLACE;

SHEET="data";

RUN;

[FatihAkici]

I know this is an old thread, but I just encountered this problem myself, and solved the following way. Actually two ways.

1) Forget about Excel, just export into CSV. (This is what I always did with Python, always easier.)

PROC EXPORT DATA=last

OUTFILE='/home/data.csv'

DBMS=CSV REPLACE;

PUTNAMES = YES;

RUN;

2) If you insist on Excel, here you go:

PROC EXPORT DATA=last

OUTFILE='/home/data.xls'

DBMS=EXCEL5 REPLACE;

RUN;

Long story short, EXCEL5 is the key. Hope this helps.

[hhchenfx]

I can't believe that it is so troublesome export to excel.

I try all of the possible combination and get all kind of error notice you guys here mentioned.

XLSX not supported

Server...

Export sucessfully but cant open

I got to use csv.

The funny thing is the code work with old version of excel and when I got the 2013, it is no longer works.

So dissappointed.

HHC

[Go]

THIS WORKS..... the attach statement (conetnttype) is the key

PROC EXPORT DATA=sashelp.cars
OUTFILE="/tmp/cars_test.xlsx"
DBMS=XLSX REPLACE LABEL;
SHEET=Sheet1;
RUN;

FILENAME Mailbox EMAIL 'nayakig@sce.com'
Subject='Test Mail message'
attach=("/tmp/cars_test.xlsx" content_type="application/xlsx");
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Attached is the report";
RUN;