topic Re: Proc Export dbms=xlsx Column Limit in SAS Procedures

Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 16:49:43 GMT

When I run this code:

proc export

dbms=xlsx

outfile="/adir/afile.xlsx"

data=mydata

replace;

run;

everything works fine as long as I only have 26 columns to produce. As soon as a I add one more column I get an error when trying to open the file. The log is clean however.

The excel error is:

Exel found unreadable conent in 'afile.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of thie workbook, click Yes.

I click "Yes" and I get this message:

Removed Records: Cell information from /xl/worksheets/sheet1.xml part

I think all of the data is in the file and the first row, which is just the column labels, is written correctly, but the rest of the data is only written in columns names that end in 'A' (ie AA, BA, CA).

Re: Proc Export dbms=xlsx Column Limit

AncaTilea — Tue, 22 Jan 2013 17:06:18 GMT

Hi.

When I try your code, using dbms = xlsx

I get this error:

ERROR: XLSX is not yet supported by proc export

Can you try changing it to "dbms = EXCEL";

Thank you.

Anca.

Re: Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 17:18:11 GMT

Thank you for the response Anca.

Interestingly enough, I get this error when I swiched to excel:

ERROR: DBMS type EXCEL not valid for export.

When I use dbms=xlsx I do not get an error and the files are created.

Here's a link to the where I read about using dbms=xlsx.

Re: Proc Export dbms=xlsx Column Limit

Reeza — Tue, 22 Jan 2013 17:20:50 GMT

try ExcelCS as engine if you're on 9.3 or higher

Re: Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 17:28:05 GMT

I'm on 9.3 (TS1M1).

I'm not too familiar with excelcs. When I used dbms=excelcs I get this error:

ERROR: Server Name is invalid or missing.

Re: Proc Export dbms=xlsx Column Limit

Reeza — Tue, 22 Jan 2013 17:32:42 GMT

You shouldn't have that error anyways with 26 columns, how many rows do you have.

You need to start PC Files Server - 43802 - Installing SAS® 9.3 PC Files Server and using it to convert 32-bit Microsoft Office files to SAS® 64-bit files

Re: Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 17:41:02 GMT

With dbms=xlsx I don't have any errors until I get to 27 columns. 26 works fine.

The server is out of my control. Starting a PC file server is not an option for me.

Re: Proc Export dbms=xlsx Column Limit

Reeza — Tue, 22 Jan 2013 17:56:58 GMT

Its named badly...its not a server as such, its more of a feature if anything.

But I totally understand if that's not an option as well.

Is it a specific 27th column or any 27th column that causes the issue?

Re: Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 18:11:18 GMT

It's any 27th column. Even just doing this:

%macro make_data;
data my_data;
   %do i=1 %to 27;
      var&i = &i;
   %end;
run;
%mend;
%make_data;

proc export
dbms=xlsx
outfile="/adir/my_file.xlsx"
data=my_data
replace;
run;

Change it to "%to 26" and there is no issue.

Re: Proc Export dbms=xlsx Column Limit

AncaTilea — Tue, 22 Jan 2013 18:41:17 GMT

This code works fine for me (I had to chance xlsx to EXCEL);

%macro make_data;

data my_data;

%do i=1 %to 27;

var&i = &i;

%end;

run;

%mend;

%make_data;

proc export

dbms=excel

outfile="\\&path.\my_file.xlsx"

data=my_data

replace;

run;

...

Re: Proc Export dbms=xlsx Column Limit

Deryl — Tue, 22 Jan 2013 18:49:55 GMT

Since excel isn't an option for me, maybe I'm out of luck. Is this a known limitation for xlsx?

Re: Proc Export dbms=xlsx Column Limit

Reeza — Tue, 22 Jan 2013 23:45:05 GMT

Talk to Tech Support, this isn't something that should happen.

I'm getting the same error in Excel 201 and SAS 9.2 but not with 26 columns so I don't know what causes it. And they need to fix it.

Apparently that is the case is 9.3 M1 and earlier, the limit is actually 25 columns:

40383 - An unreadable content error occurs when you open Microsoft Excel files that were created by a 64-bit Excel driver

Re: Proc Export dbms=xlsx Column Limit

Deryl — Wed, 23 Jan 2013 13:33:51 GMT

Thanks for pointing me towards this. It help clarify the matter.