BookmarkSubscribeRSS Feed
KarthikDeepak
Calcite | Level 5

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??

14 REPLIES 14
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
KarthikDeepak
Calcite | Level 5

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
Amethyst | Level 16

Hi Karthik,

did you try exporting the file to dbms=xlsx

Thanks,

Jagadish

Thanks,
Jag
KarthikDeepak
Calcite | Level 5

Hi Jagadish,

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


AncaTilea
Pyrite | Level 9

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
Calcite | Level 5

Hi Ance,

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

AncaTilea
Pyrite | Level 9

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
Calcite | Level 5

Hi Anca,

Would it be possible without DBMS?

It says Server Name is invalid or missing...

Karthik

Reeza
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Barite | Level 11

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
Quartz | Level 8 Go
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 22193 views
  • 4 likes
  • 8 in conversation