NOTE: Business Intelligence Notes

Tricia Aanderud (@TAanderud) commented on my post about the late Dennis Ritchie. Her observation that she frequently finds herself creating elaborate SAS programs to avoid using the dreaded command line rang true with me. Big time!

Tricia and Angela Hall (@Angela, Real BI for Real Users blog) are working on a new SAS publication entitled Building Business Intelligence Using SAS. Judging by their recent tweets it'll be published soon. The advance information I've seen makes the book look a real winner.

And another winner is their joint web site. It'd somehow passed me by until Tricia subtly introduced it in her comment. In addition to being beautifully presented, http://www.bi-notes.com/ is full of useful BI tips and tricks - tasters of what is to come in their book. I've already added it to my Google Reader subscriptions, and I've added it to NOTE:'s blogroll too. Recommended.

From a quick overview of SAS EBI to squeezing more visible columns into your Web Report Studio output, there's plenty of interest.

NOTE: There's No In In Macro (or is there?!)

I love the SAS macro language and the power it gives you. Being able to control the creation of a SAS program, or its execution path, is immensely valuable. The SAS macro language contains the vast majority of the functional and logic capabilities of the Base SAS language but one notable exception is the ability to use the IN operator in a %do / %end statement. However, you might be pleased to know there's a little-known option to allow the use of IN in macros.

SAS v9.2 introduced the MINOPERATOR system option. It defaults to NO, but if you switch it on then you gain the use of the IN operator in SAS macro language. See the code example in the SAS log below:

26 option minoperator;
27
28 %macro babbage;
29
30   %if &sysver in 9.2 9.3 %then
31     %put I have the use of MINOPERATOR! ;
32   %else
33     %put I have no MINOPERATOR option ;
34
35 %mend babbage;
36
37 %babbage
I have the use of MINOPERATOR!

The SAS macro IN operator has had a checkered history. Introduced in v9.0/9.1 (together with a lot of other nice new macro features), it disappeared in later versions of 9.1 (for instance, the copy of 9.1.3 I have in front of me does not support IN). Regardless, it seems to be back to stay now! I presume there are some possible syntax clashes with existing SAS programs and that's why it firstly disappeared and secondly reappeared as a switched-off option..

You'll notice that the syntax differs a little from Base SAS, but it's all described in the online help. In short, there's no brackets around the list of values, and the values are separated by spaces. You can use the MINDELIMITER system option to specify an alternate delimiter. You can use the hash character (#) instead of "in", but I suggest that would just make your code hard to read.

If you're not already a keen user of SAS macro, find out more and see what you're missing!

NOTE: Updated Pocket Reference

When Rick Aster first saw his book Professional SAS Programmer’s Pocket Reference published can he have imagined that a sixth edition would be published nearly twenty years later? Well, it has. Suitably updated for SAS 9.3.

There's a good reason why Rick's book is still in print: people find it an invaluable tool. The book offers key information about SAS statements, options, routines, and more, all in a compact, easy-to-use, handily-sized format.

To find out more about Rick, and to see the variety of other SAS resources on offer, see Rick's Global Statements web site. For some crosswords with a SAS and technology theme (and some other R&R activities), see Rick's PROC RASTINATE page. Don't delay.

Dennis Ritchie (1941-2011)

I was saddened to hear of the death of Dennis Ritchie recently. As creator of the C programming language and co-creator of unix, there are elements of his work throughout my personal and professional life: Android and Apple iOS are based on linux (a flavour of unix), and many of my clients run unix or linux servers for SAS software. As a Computer Science student between 1980 and 1983, C and unix were my bread and butter.

As somebody who has struggled, not yet mastered, but at least come to terms with unix, I'm amused by a quote associated with Dennis:

UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity

Dennis's name and work may not be as well recognised as others with greater marketing and self-promotion skills, but rest assured that the scope and influence of his work is far greater.

Dennis, we salute you. RIP.

NOTE: (Constructive) Feedback is Good

Sometimes it's adequate that SAS simply does what you ask. On other occasions, it's useful to know a little more detail of what SAS is doing on your behalf - maybe because it's making decisions for you, or maybe you're trying to figure-out a problem with your code.

Adding the FEEDBACK keyword to a PROC SQL statement will result in a bunch of useful information being written to the SAS log:

• Any asterisk (for example, SELECT *) is expanded into the list of qualified columns that it represents. 
• Any PROC SQL view is expanded into the underlying query. 
• Macro variables are resolved. 
• Parentheses are shown around all expressions to further indicate their order of evaluation. 
• Comments are removed.

Here's an example log, showing the SELECT * being expanded and a macro variable being fully resolved (note how the column name is prefixed with the table name, and the sort order (ASC) is shown):


15 %let ob = age; 
16 proc sql feedback; 
17   select * 
18     from sashelp.class 
19     order by &ob 
20     ; 
NOTE: Statement transforms to: 


        select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight 
          from SASHELP.CLASS 
      order by CLASS.Age asc; 


 21 quit; 

Quite apart from revealing some of what SAS is doing behind the scenes, the expansion of SELECT * into the log allows you to subsequently copy/paste the column names back into your program and then remove one or two that you don't want to keep.

Another good option for revealing information is OPTION MSGLEVEL=I. Setting this option will also result in some hitherto "secret" information being written to the SAS log. For example, SAS will tell you if it has used an index (and it will tell you which index it has used). Here's an example log (demonstrating how indexed data sets can be successfully read through a BY statement even if the data set is not physically sorted):

15 option msglevel=i; 
16 data holiday; 
17   set maps.spain2; 
18   by regname; 
19 run; 
INFO: Index REGNAME selected for BY clause processing. 
NOTE: There were 53 observations read from the data set MAPS.SPAIN2. 
NOTE: The data set WORK.HOLIDAY has 53 observations and 10 variables.

The FEEDBACK and MSGLEVEL options are both useful techniques to get more of an insight into what SAS is doing and hence what your program is doing.

NOTE: SAS Global Forum (SGF) 2012 Call For Papers

Next year's SAS Global Forum will be held in Orlando, Florida, USA from April 22 to 25. As ever, it will be your best opportunity of the year to get up-to-date with SAS news and latest releases; pick up a bucket-load of hints, tips, and experiences; meet SAS's developers and pick their brains.

Why not make your own contribution to the event? Consider submitting a paper or poster. You have until November 14 to make a proposal. If you've not done such a thing in the past, fear not, some of the section heads are very supportive of new blood and will provide invaluable help, advice and feedback. Just go to the event home page and click the Get Involved link near the top of the page.

Will I see you there?