Libname is created but the proc Contents doesnt recognize the worksheet name

[java1982]

The library was created successfully with the following tables:

parks$

species$

species$_xlnm#_FilterDatabase

visits$

This is the code i used to create the libname, but it gets an error reading the contents due to the "$" in the table name.   I have removed the $ from the code to not include the $ and i still get an error:  

ERROR:  File NPD.parks.DATA does not exist.  Has anyone received this issue?  How an I get past this issue?

libname npd "S:/Workshop/EPG1V2/data/np_info.xlsx"
options validvarname=v7;
proc contents data=npd.parks$;
run;
libname npd clear;

[sbxkoenk · Accepted solution]

Hello,

If you do not specify a libname engine, the libname engine is 'V9'.

You need an EXCEL LIBNAME engine.

Something like 

libname xdb excel "\\mydriver\pcfdata\Invoice.xlsx";

or

libname myhr XLSX "/usr/wolfen/benefits/HRworkbook.xlsx";

Good luck,

Koen

[java1982]

Thank you so much... i did look past the engine portion of the syntax. An easy issue for your to recognize and a good lesson for me as a newbie. Thank you so much for your help and mentorship!

[Kurt_Bremser]

libname npd "S:/Workshop/EPG1V2/data/np_info.xlsx"
options validvarname=v7;

This code will result in a syntax error because of the missing semicolon for the LIBNAME statement.

Anyway, your LIBNAME cannot work because it tries to assign a V9 Base library to an Excel file. This is because you did not include the XLSX engine option like this:

libname npd xlsx "S:\Workshop\EPG1V2\data\np_info.xlsx";

(forward slashes changed to backward because of Windows)

[java1982]

Thank you for your post, the Library was getting generated in SAS Studio. I did forget to key the semi-colon when transferring the code. 😞

Thank you for your assistance and help here. Its really nice of you and I do appreciate all of this information.

[Tom]

Since you didn't tell it what database engine to use it will default to EXCEL because you pointed the libref at an XLSX file.

Try using XLSX engine instead.

Try copying all of the sheets and see what it finds.

libname npd xlsx "S:\Workshop\EPG1V2\data\np_info.xlsx";
proc copy inlib=npd outlib=work;
run;

If that doesn't solve it try using a name literal for your range references.  Note you probably will need to set the VALIDMEMNAME=EXTEND option first.

options validmemname=extend;
data parks;
  set npd."'parks$'"n;
run;

[Quentin]

Whenever I'm unsure about how an engine will deal with Excel sheet names, I run:

proc contents data=npd._ALL_;
run;

Early in the output, it will give you a list of the table names (translated sheet names) it sees, followed by the typical CONTENTS output for each table. 

[java1982]

Thank you so much for this tip/trick! Its amazing will definitely keep this in my daily usage. 🙂 Awesome!

[Cynthia_sas]

Hi:

  It looks like you're working in the Programming 1 e-learning course. The syntax you show is not exactly the syntax that we recommend in Lesson 2.

  I believe that if you review the videos in the class, you'll see that we frequently use "S:\workshop" as an example drive location. However, you ONLY have that location for the data if you are using the Virtual Lab in the e-learning class or if you are taking a Live Web version of the class and using the Classroom lab image.

  We recommend that you explicitly use the XLSX engine in the LIBNAME statement and that is the syntax that we show in the activity program that you use as the starter file for this task:

libname np xlsx "s:/workshop/data/np_info.xlsx";  <-- classroom example

Some alternate LIBNAME statements on other systems might be:

libname np xlsx "~/EPG1V2/data/np_info.xlsx";  <-- SAS OnDemand for Academics example

libname np xlsx "c:\SAS_Class]EPG1V2\data\np_info.xlsx";  <-- local C: drive example

  I would recommend following the appropriate LIBNAME statement with an exploratory PROC CONTENTS step:

options validvarname=v7;

proc contents data=np._all_ nods;

run;

This would show you the names of the worksheets. If you use the XLSX engine to read the data, you should not need to put a $ after the sheet name. In fact, when I run the above code, this is what I see in the PROC CONTENTS output:

(Notice that I used a different C: drive location on my work machine to point to the full path to the XLSX file). Now that I've done that, I  can submit modified code to see just the contents for the PARKS sheet:

  So, as you can see, without using the $ in the DATA= option and pointing to the location of the XLSX file correctly for my method of using SAS, I was able to run this activity in the class.

Cynthia

[java1982]

Cynthia - yes. I did miss the definition of the engine. Thank you for all of this information and your assistance. It is greatly appreciated.