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
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.
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.
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.
Hi, I tried that and here is my results:
41 data want;
42 set camlis.datalist;
ERROR: Libref CAMLIS is not assigned.
43 run;
Typo - your library is camplist not camlis, can you fix that and try it again please.
please replace the proc contents with proc datasets and share the screen shot of the contents
proc datasets lib=camplis; quit;
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.
Engine | XLSX |
Physical Name | \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx |
Por | . |
Yes, it has data. Here is a screenshot of the excel file.
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.
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.
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 secondsHere is the SAS report result.
LibrefCAMPLISDirectory
Engine XLSX Physical Name \\cala801nvs1.wellsfargo.com\C_RBGBBG_Users\galeanfr\My Documents\SAS\Campaigns.xlsx Por .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: