11-18-2016 07:11 PM
I'm using SAS 9.4 and trying to use code that worked fine in 9.3 but is giving me problems.
I'm using the libname method to export data into Excel. The original libname statement was
libname xls "V:\<spreadsheet name>.xlsx" (<spreadsheet name> is the full address of the file)
I changed the code to add in pcfiles path= so it looks like this:
libname xls pcfiles path="<spreadsheet name>.xlsx"
The code runs fine, and everything appears to work. But when I try to open the resulting excel file, Excel will not open. It isn't the libname clear issue, but Excel is telling me that the file is in an invalid format. Any ideas?
11-19-2016 08:18 PM
I'm with @Reeza
If you're using a current SAS release then use the XLSX enging and not PCFILES
11-21-2016 11:27 AM
Thanks to both of you for your ideas. I actually tried both to no avail. Unfortunately I don't have the latest build of 9.4, so the attempt at using the xlsx method failed. I tried removing the libname (xls) and just using "pcfiles" as my libname reference, and this didn't work either. I tried:
libname pcfiles path=...., but this only gave me a message saying there is no such libname.
I tried again changing the libname to xlsx:
libname xlsx pcfiles path=....
This again appears to work, with no errors in SAS, but the resulting Excel file won't open. The error states, "Excel cannot open the file '<filename>.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."
Any other ideas?
11-21-2016 01:02 PM
@BBSHOPSINGER You're putting too much into your libname statement. It's either PCFILES or XLSX not both.
libname out XLSX 'path to my file';
This worked as of SAS 9.3 so even if you don't have the latest version of SAS 9.4 it should work.
11-21-2016 02:23 PM
My "xlsx" was intended to be the libname. I experimented with several libnames with the same result. No errors in the log, it appears to work fine, but the resulting Excel file will not open when using the pcfiles code.
When I use the xlsx method, it also appears to work, but again I get unreadable content in the excel file. The message here is "Excel found unreadable content in <filename>. Do you want to recover the contents of this workbook?" If I click yes, it opens, but with unreadable content.
If I try to use the method that worked fine in 9.3 (just using a libname statement without an "xlsx" or "pcfiles") it gives me a SAS error:
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
I think I'm going to have to find a different method here. Could the issue be 64-bit SAS vs. 32-bit Office?