It’s in the Details: Keeping The Little SAS Book Accurate

The Little SAS Book, Sixth Edition is now a year old.  I have already written posts about What’s New in this edition, and the very cool XLSX LIBNAME engine.  So what more is there to say?  A lot, it turns out.  The Sixth Edition was our biggest rewrite since the Second Edition introduced the new (at the time) Output Delivery System. This post covers a few of the changes you probably didn’t notice:

• Default output has changed. You probably are aware that the default output has long been HTML (or SASREPORT in Enterprise Guide).  What most SAS users don’t know is that technically it was HTML4, but is now HTML5 (including in Enterprise Guide).  A few years ago, the default changed from HTML3 to HTML4.  If you didn’t notice the change from 3 to 4, then you probably won’t care about the change from 4 to 5 either. But it was a big deal to us because HTML4 stored images in separate files while HTML5 embeds images in the same files as text. This required us to completely rewrite section 8.12 with its discussion of saving graphics output. That complicated our lives, but it simplifies life for you if you use SAS to create HTML pages with graphics.  No more need to worry about links to your graphics files; now your graphics will be saved inside your HTML pages.

• Footnotes are gone (except the few ones under tables and chapter quotes).  In an effort to maximize readability, we worked important information into the text and deleted the rest.

• Default ODS style for PDF output has changed from PRINTER to PEARL.

• Some built-in styles have disappeared entirely including one we used a lot, D3D.  To see the current built-in styles on your system, run this: PROC TEMPLATE; LIST STYLES; RUN;

• ODS HTML statement requires PATH= in some situations when it didn’t before.  It’s complicated so just include a PATH= option, ok?

• PROC REPORT no longer requires the NOWINDOWS option to avoid opening a Report window.

• Some ODS style attribute options have new names.  For example, the option FONT_SIZE= has changed to FONTSIZE=, and BACKGROUND= changed to BACKGROUNDCOLOR=.  The old option names still work, but the new ones are considered more correct.

• There is a new way to make suggestions for the SASware Ballot.  Instead of emailing your ideas, you submit them on SAS Communities.

• Ellis Island National Monument merged with Statue of Liberty National Monument.  Also Hawaii Volcanoes National Park lost one of its museums.  (The Jaggar Museum was damaged by the eruption of Kilauea.)  We updated our data accordingly.

• The SAS family grew.  The number of SAS installations increased from 60,000 sites in 134 countries to 83,000 sites in 147 countries.

I admit that most of these are minor changes for most SAS users, but we pride ourselves on impeccable attention to detail because for programmers sometimes the details matter very much.

Accessing Excel Files Using LIBNAME XLSX

If you have been using SAS for long, you have probably noticed that there is generally more than one way to do anything. The Little SAS Book has long covered reading and writing Microsoft Excel files with the IMPORT and EXPORT procedures, but for the Sixth Edition we decided it was time to add two more ways: The ODS EXCEL destination makes it easy to convert procedure output into Excel files, while the XLSX LIBNAME engine allows you to access Excel files as if they were SAS data sets.

With the XLSX LIBNAME engine, you can convert an Excel file to a SAS data set (or vice versa) if you want to, but you can also access an Excel file directly without the need for a SAS data set. This engine works for files created using any version of Microsoft Excel 2007 or later in the Windows or UNIX operating environments. You must have SAS 9.4M2 or higher and SAS/ACCESS Interface to PC Files software. A nice thing about this engine is that it works with any combination of 32 bit and 64 bit systems.

The XLSX LIBNAME engine uses the first line in your file for the variable names, scans each full column to determine the variable type (character or numeric), assigns lengths to character variables, and recognizes dates and numeric values containing commas or dollar signs. While the XLSX LIBNAME engine does not offer many options, because you are using an Excel file like a SAS data set, you can use some standard data set options. For example, you can use the RENAME= data set option to change the names of variables, and FIRSTOBS= and OBS= to select a subset of rows.

Reading an Excel file as is 

Suppose you have the following Excel file containing data about magnolia trees:

With the XLSX LIBNAME engine, SAS can read the file, without first converting it to a SAS data set. Here is a PROC PRINT that prints the data directly from the Excel file.

* Read Excel spreadsheet using XLSX LIBNAME;
LIBNAME exfiles XLSX ‘c:\MyExcel\Trees.xlsx’;
PROC PRINT DATA = exfiles.sheet1;
   TITLE ‘PROC PRINT of Excel File’;
RUN;

Here are the results of the PROC PRINT. Notice that the variable names were taken from the first row in the file.

Converting an Excel file to a SAS data set 

If you want to convert an Excel file to a SAS data set, you can do that too. Here is a DATA step that reads the Excel file. The RENAME= data set option changes the variable name MaxHeight to MaxHeightFeet. Then a new variable is computed which is equal to the height in meters.

* Import Excel into a SAS data set;
DATA magnolia;
SET exfiles.sheet1 (RENAME = (MaxHeight = MaxHeightFeet));
MaxHeightMeters = ROUND(MaxHeightFeet * 0.3048);
RUN;

Here is the SAS data set with the new variable:

Writing to an Excel file 

It is just as easy to write to an Excel file as it is to read from it.

* Write a new sheet to the Excel file;
DATA exfiles.trees;
   SET magnolia;
RUN;
LIBNAME exfiles CLEAR;

Here is what the Excel file looks like with the new sheet. Notice that the new tab is labeled with the name of the SAS data set TREES.

Another nice thing about the XLSX LIBNAME is that it only locks a spreadsheet while SAS is accessing it. So generally speaking, it’s not necessary to issue a second LIBNAME statement to clear the libref. However, I did find, when I ran this in SAS Enterprise Guide, that I could not open the Excel spreadsheet unless I cleared the libref. So you can probably skip the LIBNAME CLEAR statement if you are using Display Manager or SAS Studio.

The XLSX LIBNAME engine is so flexible and easy to use that we think it’s a great addition to any SAS programmer’s skill set.

For more about the XLSX LIBNAME engine, I recommend this blog by Chris Hemedinger.

The Little SAS Book 6.0: What’s New

Six editions is a lot! If you had told us, back when we wrote the first edition of The Little SAS Book, that someday we would write a sixth; we would have wondered how we could possibly find that much to say. After all, it is supposed to be The Little SAS Book, isn’t it? But the developers at SAS Institute are constantly hard at work inventing new and better ways of analyzing and visualizing data. And some of those ways turn out to be so fundamental that they belong even in a little book about SAS.

Interface independence

One of the biggest changes to SAS software in recent years is the proliferation of interfaces. SAS programmers have more choices than ever before. Previous editions contained some sections specific to the SAS windowing environment (also called Display Manager). We wrote this edition for all SAS programmers whether you use SAS Studio, SAS Enterprise Guide, the SAS windowing environment, or run in batch. That sounds easy, but it wasn’t. There are differences in how SAS behaves with different interfaces, and these differences can be very fundamental. In particular, the system option that sets the rules for names of variables varies depending on how you run SAS. So old sections had to be rewritten, and we added a whole new section showing how to use variable names containing blanks and special characters.

New ways to read and write Microsoft Excel files

Previous editions already covered how to read and write Microsoft Excel files, but SAS developers have created some great new ways. This edition contains new sections about the XLSX LIBNAME engine and the ODS EXCEL destination.

More PROC SQL

From the very first edition, The Little SAS Book always covered PROC SQL. But it was in an appendix and over time we noticed that most people ignore appendices. So for this edition, we removed the appendix and added new sections on using PROC SQL to

• Subset your data
• Join data sets
• Add summary statistics to a data set
• Create macro variables with the INTO clause

For people who are new to SQL, these sections provide a good introduction; for people who already know SQL, they provide a model of how to leverage SQL in your SAS programs.

Updates and additions throughout the book

Almost every section in this edition has been changed in some way. We added new options, made sure everything is up-to-date, and ran every example in every SAS interface noting any differences. For example, PROC SGPLOT has some new options, the default ODS style for PDF has changed, and the LISTING destination behaves differently in different interfaces. Here’s a short list, in no particular order, of new or expanded topics in the sixth edition:

• More examples with permanent SAS data sets, CSV files, or tab-delimited files
• More log notes throughout the book showing what to look for
• LIKE or sounds-like (=*) operators in WHERE statements
• CROSSLIST, NOCUM, and NOPRINT options in PROC FREQ
• Grouping data with a user-defined format and the PUT function
• Iterative DO groups
• DO WHILE and DO UNTIL statements
• %DO statements

Even though we have added a lot to this edition, it is still a little book.  In fact, this edition is shorter than the last—by twelve pages! We think this is the best edition yet.

What’s Your SAS Interface?

These days SAS programmers have more choices than ever before about how to run SAS.  They can use the old Display Manager interface, or SAS Enterprise Guide, or the new kid on the block: SAS Studio.  All of these are included with Base SAS.

Display Manager / SAS Windowing Environment

SAS Enterprise Guide

SAS Studio

Once upon a time, the only choices were Display Manager (officially named the SAS windowing environment), or batch.  Then along came SAS Enterprise Guide.  (Ok, I know there were a few others, but I don’t count SAS/ASSIST which was rightly spurned by SAS users, or the Analyst application which was just a stopover on the highway to SAS Enterprise Guide.)

I recently asked a SAS user, “Which interface do you use for SAS programming?”

She replied, “Interface?  I just install SAS and use it.”

“You’re using Display Manager,” I explained, but she had no idea what I was talking about.

Trust me.  This person is an extremely sophisticated SAS user who does a lot of leading-edge mathematical programming, but she didn’t realize that Display Manager is not SAS.  It is just an interface to SAS.

This is where old timers like me have an advantage.  If you can remember running SAS in batch, then you know that Display Manager, SAS Enterprise Guide, and SAS Studio are just interfaces to SAS–wonderful, manna from heaven–but still just interfaces.  They are optional.  You could write SAS programs in Word or Notepad or some other editor, and submit them in batch–but why would you?  (I know someone is going to tell me that they do, in fact, do that, but the point is that it is not mainstream.  Only mega-nerds with the instincts of a true hacker do that these days.)

Each of these interfaces has advantages and disadvantages.  I’m not going to list them all here, because this is a blog not an encyclopedia, but the tweet would be

“DM is the simplest, EG has projects, SS runs in browsers.”

Personally, I think all of these interfaces are keepers.  At least for the near future, all three of these interfaces will continue to be used.  What we are seeing here is a proliferation of choices, not displacement of one with another.

So what’s your SAS interface?

 

Your Resume–Selling Yourself Using SAS

Here is another presentation to which I have contributed for the Western Users of SAS Software 2013 conference.

Your Resume–Selling Yourself Using SAS

I am honored to have served as a co-author with Rebecca Ottesen on this highly original paper.  This paper shows how to use your SAS skills to create a resume that is clever, unique, and effective.

Here is an excerpt:

Your resume should demonstrate strengths and skills, cite meaningful performance metrics, quantify contributions to the organization, and set you apart from the competition, all while being concise and staying to the point.  As a SAS user, it is likely that the skill set you would like to showcase involves programming and data analysis, so it seems perfectly natural that you should use these skills to create content for your resume.  A well thought out SAS graphic or table might be the perfect selling point to catch the attention of a hiring manager.

Here is an example of a graphic showing a timeline for work and academic experience:

If you are at the conference, I hope you will attend our presentation Wednesday November 13, 2013 2:30-2:50pm. If not, then you can download the paper here.

Writing Code in SAS Enterprise Guide

With the Western Users of SAS Software 2013 conference coming soon, I am looking forward to being part of three presentations.  One of those presentations is

Writing Code in SAS Enterprise Guide

This is based on a paper written several years ago for SAS Enterprise Guide 4.1.  That paper became obsolete almost immediately.  So I’ve been wanting to update it for quite some time.  This new paper applies to Enterprise Guide 4.2, 4.3, 5.1 and 6.1.

Here’s an excerpt:

Using SAS Enterprise Guide, you can manipulate data and run reports without ever writing a single line of SAS code.  So it’s not surprising that many SAS programmers believe that SAS Enterprise Guide is only useful to non-programmers.  If you love the SAS language, why would you ever want to use SAS Enterprise Guide?

It turns out that there are several reasons why you might want to do just that.  SAS Enterprise Guide offers programmers a variety of ways to run code.  You can type a program like you do in Display Manager, but you can also use the point-and-click features of SAS Enterprise Guide to generate programs  that you can then modify.  In addition, SAS Enterprise Guide organizes your work into projects making it easy to find your programs, logs, and results; and the process flow diagrams show at a glance how everything in your project fits together.

However, writing programs in SAS Enterprise Guide does require learning a new environment with new windows and a new system for organizing your work.  As with any new skill, there is a learning curve.  The goal of this paper is to ease that transition by explaining SAS Enterprise Guide from a programmer’s perspective.

If you are at the conference, I hope you will attend my presentation Wednesday November 13, 2013 3:30-4:20pm. If not, then you can download the paper here.

And if you can’t attend my presentation, you may want to watch this video about coding with SAS Enterprise Guide.

Which Little SAS Book?

One of the problems that Lora Delwiche and I face as authors of two books with similar titles (The Little SAS Book and The Little SAS Book for Enterprise Guide) and multiple editions (five of LSB and three of LSBEG) is explaining how the books are different.

The two books are totally different–and complementary.

So I was delighted to see that someone at SAS Press has written a great summary comparing the various editions.

Did you know that the title The Little SAS Book was originally a joke? We explain that and give a little history on sasCommunity.org.

Sunil’s Top 10 SAS Papers: Guest Blog by Sunil Gupta

I recently learned about an interesting resource for SAS users.  Sunil Gupta—a SAS Press author and consultant—maintains lists of his favorite SAS papers on various SAS-related topics.  You can peruse these lists on his page on SAScommunity.org.

Here are some of Sunil’s Top 10s:

Sunil’s Top 10 PROC FORMAT/Functions Papers

Sunil’s Top 10 PROC COMPARE Papers

Sunil’s Top 10 SAS Macro Papers

Sunil’s Top 10 SAS Dictionary Tables Papers

Sunil’s Top 10 ODS Papers

Sunil’s Top 10 DATA Step/MERGE Papers

Sunil’s Top 10 SAS Certification Exam Papers

Sunil’s Top 10 SAS Enterprise Guide Papers

Sunil’s Top 10 Pharmaceutical Industry Papers

Save 30% on All SAS Publishing Titles

Still looking for that perfect holiday gift?  For a limited time you can save 30% on all books from SAS Publishing. Most of these books are a little big for stocking stuffers, but they fit just fine under a tree.  This offer expires Dec. 13, 2010 so don’t delay. Click here for details.

WUSS and My 7 Minutes and 39 Seconds of Fame

I have just returned from another great Western Users of SAS Software conference.  Normally at this point I would write a summary of the conference, but this year I don’t have to.   Instead, you can hear my summary in this interview by Sy Truong of Meta-Xceed, Inc.

To hear all of Sy’s interviews at the conference, click here.