Accessing Excel sheet in Enterprise 7.1

[katies]

How do I access an Excel sheet in SAS Enterprise 7.1?

I'm using the code below. The log says that the libname was successfully assigned, but the proc contents step gives me an error that says, "File mylib.sheetname.DATA does not exist."

libname mylib XLSX "file-path.xlsx";

Proc contents data=mylib.sheetname;
Run;

[SASKiwi]

Open the library MYLIB in your EG server list. Any existing sheet names should be listed there. Choose one to test with PROC CONTENTS.

[katies]

When I open the MYLIB library, there are no sheets there.

Could the problem be the filepath I'm specifying? I'm using a UNIX server and accessing a file on my Desktop with the filepath: "C:\Users\username\Desktop\filename.xlsx" 

[katies]

It looks like the filepath could be the problem. I tried accessing the data with a proc import step, but got an error saying Physical file does not exist, server-name/C:/Users/username/Desktop/filename.xlsx 

How do I set the filepath so that SAS references a file on my desktop, not a file on the server? 

Proc import datafile="C:/Users/username/Desktop/filename.xlsx"
dbms=xlsx
out=outpath;
Run; 

I also tried using the import data wizard to import the file to one of my libraries but got an error saying that Excel needed to be installed to access it (even though Excel is installed on my computer and I can access it there without an issue)

[SASKiwi]

You can't reference your desktop from a Unix SAS server. Use the Copy Files task to move the spreadsheet from your desktop to a SAS server folder. Then reference the server folder in your program (/Myfolder/MySpreadsheet.xlsx).

[SASKiwi]

That problem is likely caused by trying you use 64-bit EG with 32-bit MS Office. The bitness needs to be the same for it to work.

[Kurt_Bremser · Accepted solution]

---

@katies wrote:

When I open the MYLIB library, there are no sheets there.

Could the problem be the filepath I'm specifying? I'm using a UNIX server and accessing a file on my Desktop with the filepath: "C:\Users\username\Desktop\filename.xlsx" 

---

You can only access locations that are mounted on the server, and you have to use the UNIX operating system's syntax for it. Since desktops (by default) offer no shared resources, it is highly improbable that your C: drive is accessible anywhere in the network.

What can you do?

• move the file to the server. EG 7 has the "Copy Files" task built in, so you can use that as part of a process flow
• store your file in a location that is accessible both from your desktop and the server. Get in touch with your admins to see if such a location exists

Note that the UNIX file system does not have the concept of drives (therefore no drive letters), and uses forward instead of back slashes to separate directories in a pathname.