Help using Base SAS procedures

Proc Export dbms=xlsx Column Limit

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Proc Export dbms=xlsx Column Limit

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


Accepted Solutions
Solution
‎01-22-2013 06:45 PM
Super User
Posts: 17,960

Re: Proc Export dbms=xlsx Column Limit

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

View solution in original post


All Replies
Super Contributor
Posts: 543

Re: Proc Export dbms=xlsx Column Limit

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.

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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.

Super User
Posts: 17,960

Re: Proc Export dbms=xlsx Column Limit

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

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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.

Super User
Posts: 17,960

Re: Proc Export dbms=xlsx Column Limit

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

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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.

Super User
Posts: 17,960

Re: Proc Export dbms=xlsx Column Limit

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?

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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.

Super Contributor
Posts: 543

Re: Proc Export dbms=xlsx Column Limit

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;

...

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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

Solution
‎01-22-2013 06:45 PM
Super User
Posts: 17,960

Re: Proc Export dbms=xlsx Column Limit

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

Occasional Contributor
Posts: 13

Re: Proc Export dbms=xlsx Column Limit

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 2401 views
  • 0 likes
  • 3 in conversation