SAS Base questions from 5th Edition Book

[cosmid]

Hi, I'm studying for my SAS Base certification exam using the 5th edition of the SAS Prep Guide. I got a little confused on page 50, 51, and 52. I was wondering if there's anyone out there that's also studying for the exam can help me out.

Page 50: This got me confused on Page 51

It's given an example of an Excel worksheet with only two sheets named Tests and Adv.

libname certxl XLSX 'C:\Users\Student1\cert\exercise.xlsx';

Page 51:

It's given an example to use the SET statement to indicate which worksheet in the Excel file you want to read.

data work.stress;

  set certxl.ActivityLevels;

run;

I am confused of where this worksheet ActivityLevels is coming from. If I understand it right, the libref certxl is pointing to a single Excel file with only two worksheets named Tests and Adv. If someone can help me explain this that would be great.

Page 52:

Here's exactly what was in the book...

In the following example, the PROC PRINT statement refers to the worksheet Boot Sales and prints the contents of the Excel worksheet that was referenced by the SAS/ACCESS LIBNAME statement.

libname certxl xlsx 'C:\Users\Student1\cert\stock.xlsx';

proc print data=cerxl.'boots stock'n;

run;

Question regarding Page 52:

Is that a typo in the print statement? Should the print statement be

proc print data=cerxl.'Boot Sales'n;

If someone can help me clarify these that would be great. Thanks in advance!

[ballardw]

Was there any example of using something like
data certxl.ActivityLevels;

   set <some source>;

or INPUT statements?

That could create an additional sheet.

Or ODS EXCEL to write to the Excel file?

[cosmid]

Nope. Here is Page 50 to Page 51 with everything, so you can see it more clear.

Page 50: And the workbook referenced here only has two worksheets named tests and adv, they even have a figure showing this.

To read in this workbook, create a libref to point to the workbook's location:

libname certxl XLSX 'C:\Users\Student1\cert\exercise.xlsx';

The SAS/ACCESS LIBNAME statement creates the libref Certxl, which points to the Excel workbook exercise.xlsx. The workbook contains two worksheets, Tests and Adv, which are now available in the new SAS library (Results) as data sets.

Page 51:

Set Statement

Use the SET statement to indicate which worksheet in the Excel file you want to read.

data work.stress;

  set certxl.ActivityLevels;

run;

1. Page 50, it said libref Certxl is created, wouldn't Certxl be a lowercase as certxl

2. Page 51It jumped from Tests and Adv to ActivityLevels, which didn't exist in the workbook. Wouldn't it be easier for the reader if they used certxl.Tests or certxl.adv instead? Now I have no idea what ActivityLevels is or where it comes from.

3. it said Tests and Adv are now available in the new SAS library (Results) as data sets. On Page 49, it has an example to demonstrate the XLSX engine requires quotation marks and the example is written as follows

            libname results XLSX 'C:\Users\Student1\cert\exercise.xlsx';

            Is the (Results) here referring to the sample on Page 49 or (Results) is a built in library? If it is referring to Page49, wouldn't it be clear if they use SAS library (certxl) here instead to be less confusing?

I haven't got that far in this book. But I find these few pages to be extremely confusing.

[ballardw · Accepted solution]

---

1. Page 50, it said libref Certxl is created, wouldn't Certxl be a lowercase as certxl

LIBREF and FILEREF are case insensitive in SAS. The following will all work to point to the same data set inside SAS code.

SASHELP.Class

sashelp.class

sasHelp.class

The defined paths or file names as the target of the Libname or Filename statements follow operating system rules and may be case sensitive.

2. Page 51It jumped from Tests and Adv to ActivityLevels, which didn't exist in the workbook. Wouldn't it be easier for the reader if they used certxl.Tests or certxl.adv instead?

You are correct. I think I would send a note to publisher. This has the earmarks of a change from the previous version that wasn't consistently applied. Perhaps the version 4 had ActivityLevels and they wanted shorter names in version 5 and missed this one.

3. it said Tests and Adv are now available in the new SAS library (Results) as data sets. On Page 49, it has an example to demonstrate the XLSX engine requires quotation marks and the example is written as follows

            libname results XLSX 'C:\Users\Student1\cert\exercise.xlsx';

            Is the (Results) here referring to the sample on Page 49 or (Results) is a built in library? If it is referring to Page49, wouldn't it be clear if they use SAS library (certxl) here instead to be less confusing?

They probably should have stuck to the same libname. With path based libraries you can have multiple libraries pointing to the same folder with different libnames. I would suggest that you run the two libname statements and attempt example code on the result and watch the log closely.

Again it might be worth bringing to the attention of the publisher.

Personally I would treat this as an example of the frustrations likely to evolve around using Excel files in general. They often are a pain to work with because there is no enforced structure and that will cause problems as sources. Also since Excel will often attempt to "help", you can find it changing values or appearance for things written to Excel.

[cosmid]

Thank you so much for clarifying these!

And for #3, I understand that result and certxl are both pointing to the same workbook. Technically what the book wrote is correct, but it is confusing to the reader with (Result) written there. It would be so much easier if they replace it with (Certxl) or simply remove the (Result) completely.

Also, you said you will contact the publisher, does that mean you are one of the authors? I'm sorry if my comments offended you in anyway. Other than these pages, the book is written really well!

Thanks again for your time in answering my questions!

[ballardw]

---

@cosmid wrote:

Thank you so much for clarifying these!

Also, you said you will contact the publisher, does that mean you are one of the authors? I'm sorry if my comments offended you in anyway. Other than these pages, the book is written really well!

---

I meant that You as the affected user should contact the publisher.

Not involved with this publication in any way. I have found similar disconnects in other publications or software and brought things to the attention of the publisher or developers, if for no other reason to vent frustration.

[cosmid]

I see. Thanks again for helping me with the explanation. I'll go look around to see if there's something like a discussion thread related to this book or exam.

Have a nice Weekend!