BookmarkSubscribeRSS Feed
BBSHOPSINGER
Calcite | Level 5

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?

7 REPLIES 7
GreggB
Pyrite | Level 9

Take out the "xls".

 

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

BBSHOPSINGER
Calcite | Level 5

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?

 

GreggB
Pyrite | Level 9

You need a libref.

 

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

 

Reeza
Super User

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

BBSHOPSINGER
Calcite | Level 5

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?

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1387 views
  • 0 likes
  • 4 in conversation