libname excel error

[frankg80]

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

[ballardw]

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.

[frankg80]

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.  

[Reeza]

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?

[frankg80]

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.  

[Reeza]

What happens if you run the following?

data want;
set camlis.datalist;
run;

[frankg80]

Hi,  I tried that and here is my results:

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

[Reeza]

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

[Jagadishkatam]

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

proc datasets lib=camplis; quit;

[frankg80]

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	.

[Jagadishkatam]

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

[frankg80]

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

[SASKiwi]

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.

[frankg80]

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.

[Reeza]

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	.

---