Unable to open xls file

[srinivasa_attot]

Hi,

We are unable to open an .xls file generated by a SAS program on a local desktop. However, the same program works correctly when generating an .xlsx file.

Error message: “We found a problem with some content in <filename.xls>.”

This issue started occurring after upgrading to SAS 9.4 M8. The same functionality was working fine in SAS 9.4 M7.

Note: There are no special characters in the file.

Statement: 
proc export data=sample outfile=/home/raju/filename.xls" dbms=xls
replace;
run;

Regards,
Srinivas

[sbxkoenk]

Are you running into this?

Problem Note 40383: An unreadable content error occurs when you open Microsoft Excel files that were created by a 64-bit Excel driver
https://support.sas.com/kb/40/383.html

BR, Koen

[srinivasa_attot]

I tried the article, but it did not help.

[PaigeMiller]

---

@srinivasa_attot wrote:

Statement: 
proc export data=sample outfile=/home/raju/filename.xls" dbms=xls
replace;
run;

---

Is this a typographical error? You need a double quote after outfile= to match the double-quote at the end of the file name.

[srinivasa_attot]

It's a typo error.

[Ksharp]

Did you try EXCEL engine ?

proc export data=sample outfile=/home/raju/filename.xls" dbms=excel
replace;
run;

or XLSX engine :
proc export data=sample outfile=/home/raju/filename.xls" dbms=xlsx
replace;
run;

[srinivasa_attot]

DBMS=EXCEL gave an error as " DBMS TYPE EXCEL IS NOT VALID PROMPT"

[Tom]

What exactly is generating that error message? Because it sounds like something that would be generated by a program that was trying to READ the file.  Please share you SAS log so we can see the exact error.

Also do you really need to create the ancient XLS format?  Try switching to the XLSX format and see if that works better for you.

proc export data=sample
  dbms=xlsx
  outfile="/home/raju/filename.xlsx" replace
;
run;

[srinivasa_attot]

There are no errors in the log. It shows successfully executed, the issue is occurring when opening the file in desktop. XLSX format is working fine. There are multiple programs with xls which were working fine in M7 but not working in M8. Changing all these programs to XLSX is tedious task.

[Tom]

---

@srinivasa_attot wrote:
There are no errors in the log. It shows successfully executed, the issue is occurring when opening the file in desktop. XLSX format is working fine. There are multiple programs with xls which were working fine in M7 but not working in M8. Changing all these programs to XLSX is tedious task.

---

"Opening file in desktop" with what software?  How did you get the file from the SAS server to the desktop?

Are you making a NEW file, or trying to modify an existing one?  If an existing one then perhaps there is something strange with the file already before SAS tried to write to it.

Does the same problem occur when making a brand new XLS file?

Can you recreate the issue on SAS On Demand?

[BillyVelasq]

Hi,

We are unable to open an .xls file generated by a SAS program on a local desktop. However, the same program works correctly when generating an .xlsx file.

Error message: “We found a problem with some content in <filename.xls>.”

This issue started occurring after upgrading to SAS 9.4 M8. The same functionality was working fine in SAS 9.4 M7.

Note: There are no special characters in the file.

Statement:
proc export data=sample outfile=/home/raju/filename.xls" dbms=xlssite
replace;
run;

Regards,
Srinivas

I've run into similar issues before where the problem wasn't actually SAS but the Excel file itself. In a couple of cases the file had an .xls extension even though it wasn't a valid Excel workbook anymore. Opening the file directly in Excel and then saving it as a fresh workbook solved the issue. It might be worth checking whether the file opens normally outside of SAS before spending too much time troubleshooting the code.

[srinivasa_attot]

Let me try this option and get back to you.

[Tom]

Usually the problem with reading an Excel file created by SAS is caused by SAS not actually putting any information into the file.  Make sure that the step that actually writes to the XLS file has written at least one observation with at least one variable.

Also XLS files have smaller limits on number of rows (observations) and columns (variables) than modern XLSX files allow.  Perhaps your data exceeds one or both of those limits.

[srinivasa_attot]

There is no issue with code. The same program is working for xlsx. I am trying to export a dummy data for testing the xls. It contains 3 columns and 10 rows without any special characters.

[Kurt_Bremser]

I just ran this on SAS On Demand, which is also on 9.4M8:

proc export
  data=sashelp.class
  file="~/class.xls"
  dbms=xls
  replace
;
run;

After downloading the file to my Mac, it opened successfully both in MS Excel and LibreOffice.

So it seems that you either have a problem with your particular SAS installation, or with the data itself.

Questions to answer:

• on which particular platform is your SAS server installed?
• on which particular desktop do you open your files (Windows/Mac/Linux)?
• which application is used to open the files (include version/maintenance level)?
• and finally we need to see your data; post it as a self-contained DATA step with DATALINES, so we can exactly recreate your dataset in our environments