topic Re: libname excel error in SAS Enterprise Guide

libname excel error

frankg80 — Wed, 15 Jul 2020 16:22:49 GMT

Hi I'm using SAS EG 7.1 (64 bit) and trying to create a libname to access an excel spreadsheet (2016 xlsx) on my local drive. I believe SAS is also on my machine, not on a server. Below is the code that I'm using. I noticed that the libname does create the library called "CAMPLIS" in the server window but it does not contain the worksheet called DATALIST. I get an error when trying to run a proc contents step on the DATALIST worksheet within the excel preadsheet. Please see attached screenshots.

Here is my code:

options validvarname=v7;

libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";

proc contents data=CAMPLIS.DATALIST;
run;

libname CAMPLIS CLEAR;

Here is the error:

1 The SAS System 09:47 Wednesday, July 15, 2020

1 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
5 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
40
41 options validvarname=v7;
42
43 libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";
NOTE: Libref CAMPLIS was successfully assigned as follows:
Engine: XLSX
Physical Name: \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx
44

45 proc contents data=CAMPLIS.DATALIST;
ERROR: File CAMPLIS.DATALIST.DATA does not exist.
46 run;

NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
47
48 libname CAMPLIS CLEAR;
NOTE: Libref CAMPLIS has been deassigned.
49
50 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
65
66
67 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
70

Re: libname excel error

ballardw — Wed, 15 Jul 2020 16:59:46 GMT

Before you clear the library did you open and see what the various data set names actually are?

You might also want Options validmemname=compatible when using that library.

Re: libname excel error

Reeza — Wed, 15 Jul 2020 17:02:23 GMT

I've found that PROC CONTENTS doesn't always work but if I try to access a sheet and know the name it works fine.

Have you tried accessing a table you know exists in the excel workbook?

Re: libname excel error

Jagadishkatam — Wed, 15 Jul 2020 17:03:09 GMT

please replace the proc contents with proc datasets and share the screen shot of the contents

proc datasets lib=camplis; quit;

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 19:36:46 GMT

Thanks for your reply.

Here is the code I ran.

options validvarname=v7;
*options validmemname=compatible;

libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";

proc datasets lib=camplis; quit;

Here is the log result

43 options validvarname=v7;
44 *options validmemname=compatible;
45
46 libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";
NOTE: Libref CAMPLIS was successfully assigned as follows:
Engine: XLSX
Physical Name: \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx
47
48
49 proc datasets lib=camplis;
WARNING: No matching members in directory.
49 ! quit;

NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

Here is the SAS report result.

LibrefCAMPLISDirectory

Engine	XLSX
Physical Name	\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx
Por	.

Re: libname excel error

Jagadishkatam — Wed, 15 Jul 2020 19:41:32 GMT

Seems like there is no data within the excel file, could you please confirm this, it says no members, so no tabs within excel

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 19:52:53 GMT

Yes, it has data. Here is a screenshot of the excel file.

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 19:55:01 GMT

Tried that and it didn't work. The library doesn't open. It just has the library "CAMPLIS". When I click on the drill down, nothing shows below it.

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 19:57:24 GMT

Yes, I tried a proc print data=CAMPLIS.DATALIST;

run;

but it gave me an error saying, "File CAMLIS.DATALIST.DATA does not exist. It adds ".DATA" at the end. Not sure if that has anything to do with it.

Re: libname excel error

Reeza — Wed, 15 Jul 2020 20:00:45 GMT

What happens if you run the following?

data want;
set camlis.datalist;
run;

Re: libname excel error

SASKiwi — Wed, 15 Jul 2020 20:04:57 GMT

That screenshot doesn't show the worksheet name. Is it actually DATALIST or something else? Please ensure worksheet names are valid SAS names and don't include spaces or special characters and are no more than 32 characters long.

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 20:12:30 GMT

Hi, I tried that and here is my results:

41 data want;
42 set camlis.datalist;
ERROR: Libref CAMLIS is not assigned.
43 run;

Re: libname excel error

Reeza — Wed, 15 Jul 2020 20:20:39 GMT

Typo - your library is camplist not camlis, can you fix that and try it again please.

Re: libname excel error

Tom — Wed, 15 Jul 2020 20:14:47 GMT

Make sure that your SAS session can actually see the same file as you are looking. You might also want to make sure that you don't have the file open somewhere else that is preventing SAS from getting access. Note that the LIBNAME will work if the file does not exists since at the point SAS does not know that you want to read from the file and not create a new file.

If you are sure the file is actually there then try this method to see what sheets are in it.

https://communities.sas.com/t5/SAS-Programming/How-to-import-second-sheet-from-Excel-workbook-in-to-SAS-without/m-p/669273/highlight/true#M200749

Re: libname excel error

frankg80 — Wed, 15 Jul 2020 20:42:48 GMT

Yes, the tab is called "DATALIST". I attached another screenshot where you can see the tab name. Also, all the column headings are less than 32 characters with no spaces. It contains underscores and letters only.

Re: libname excel error

Reeza — Wed, 15 Jul 2020 21:00:01 GMT

libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";

data want;
set CAMPLIS.datalist;
run;

@frankg80 wrote:

Thanks for your reply.

Here is the code I ran.

options validvarname=v7;
*options validmemname=compatible;

libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";

proc datasets lib=camplis; quit;

Here is the log result

43 options validvarname=v7;
44 *options validmemname=compatible;
45
46 libname CAMPLIS xlsx "\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx";
NOTE: Libref CAMPLIS was successfully assigned as follows:
Engine: XLSX
Physical Name: \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx
47
48
49 proc datasets lib=camplis;
WARNING: No matching members in directory.
49 ! quit;

NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

Here is the SAS report result.

LibrefCAMPLISDirectory

Engine XLSX

Physical Name \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx

Por .

Re: libname excel error

Kurt_Bremser — Thu, 16 Jul 2020 08:58:00 GMT

The fact that LIBNAME XLSX succeeds does not mean that the file actually exists, since LIBNAME XLSX can be used to create a new file. The Excel file will then only be written once data is written to this library.

To check if your account on the server has access to the shared resource (and that the file is actually there), run this first:

data _null_;
if fileexist('\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx')
then put "Found";
else put "Not found";
run;

Re: libname excel error

frankg80 — Thu, 16 Jul 2020 16:02:51 GMT

Thank you for your reply. I ran the code and received this message below. Why wouldnt the file be found? How do I correct this so that SAS finds the file?

41 data _null_;
42 if fileexist('\\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx')
43 then put "Found";
44 else put "Not found";
45 run;

Not found
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: libname excel error

SASKiwi — Sun, 19 Jul 2020 00:10:24 GMT

Where do your EG SAS sessions run? On a remote SAS server or locally on your PC (if you are using an EG connection profile then you have a remote SAS server)? If they are running on a remote SAS server then it is quite possible that it doesn't have the correct permissions to read your Excel file location. You would need to progress this with your SAS administrator.

Re: libname excel error

Kurt_Bremser — Sun, 19 Jul 2020 06:44:18 GMT

Have you ever been able to access this file from any platform (e. g. opening it with Excel from your desktop)?