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).
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:
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.
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.
try ExcelCS as engine if you're on 9.3 or higher
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.
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...
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.
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?
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.
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;
...
Since excel isn't an option for me, maybe I'm out of luck. Is this a known limitation for xlsx?
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:
Thanks for pointing me towards this. It help clarify the matter.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: