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?
Try using XLSX and not PCFILES.
Take out the "xls".
Instead: libname pcfiles path="<spreadsheet name>.xlsx"
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?
You need a libref.
libname MYDATA pcfiles path='....';
@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.
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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.