BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Deryl
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

12 REPLIES 12
AncaTilea
Pyrite | Level 9

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

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
Super User

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

Deryl
Calcite | Level 5

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
Super User

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

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
Super User

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

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
Pyrite | Level 9

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

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

Reeza
Super User

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

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

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
  • 12 replies
  • 5706 views
  • 0 likes
  • 3 in conversation