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;
@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?
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.
Open the library MYLIB in your EG server list. Any existing sheet names should be listed there. Choose one to test with PROC CONTENTS.
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"
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)
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).
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.
@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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.