DATA Step, Macro, Functions and more

Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Reply
New Contributor
Posts: 3

Libname method using PCFiles engine for Excel creates Excel files that can't be opened

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?

Super User
Posts: 17,912

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Try using XLSX and not PCFILES. 

Super Contributor
Posts: 268

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

Take out the "xls".

 

Instead:  libname pcfiles path="<spreadsheet name>.xlsx"

Respected Advisor
Posts: 3,900

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

I'm with @Reeza

If you're using a current SAS release then use the XLSX enging and not PCFILES

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0oj9f6i838mymn148...

 

New Contributor
Posts: 3

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

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?

 

Super Contributor
Posts: 268

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

You need a libref.

 

libname MYDATA pcfiles path='....';

 

Super User
Posts: 17,912

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

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

New Contributor
Posts: 3

Re: Libname method using PCFiles engine for Excel creates Excel files that can't be opened

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?

 

Ask a Question
Discussion stats
  • 7 replies
  • 262 views
  • 0 likes
  • 4 in conversation