Proc Export dbms=xlsx Column Limit

[Deryl]

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).

[AncaTilea]

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.

[Deryl]

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.

[Reeza]

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

[Deryl]

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.

[Reeza]

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

[Deryl]

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.

[Reeza]

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?

[Deryl]

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.

[AncaTilea]

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;

...

[Deryl]

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

[Reeza · Accepted solution]

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

[Deryl]

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