NOTE: BI Adoption

I spotted a nice post from Lisa Pappas in the SAS Voices blog recently. It's entitled "Boost your BI adoption rates with mobility & collaboration, part 1" and I'm looking forward to the second (and final) part.

Lisa describes how recent developments in four key areas will help promote the adoption of BI by a much wider audience. Using Roambi to illustrate her points, Lisa talks us through Mobile BI and Collaboration in part 1; part 2 will cover Interactive Data Visualisation and Search.

The first article is brief (I guess the second will be too), but it's an interesting reminder of current BI trends.

NOTE: CALCULATED in SQL

If you use SQL, either hand-cranked from the Editor, or generated via Enterprise Guide or DI Studio, you're probably familiar with creating new columns ("computed columns"). However, have you ever struggled to create a computed column from a computed column?

You can do this easily in SQL, but the syntax isn't immediately obvious. For example, calculating body mass index could be attempted with a two-stage calculation:

18 /* Assume height measured in metres, weight measured in kilos, */
19 /* then BMI = weight / height^2 */
20 proc sql;
21   create table alpha as
22     select  class.*
23            ,height * height  as heightSquared
24            ,weight / heightSquared  as bmi
25     from sashelp.class;
ERROR: The following columns were not found in the contributing tables: heightSquared.

We can create a "heightSquared" computed column, but trying to use heightSquared to create bmi fails.

The solution is simple: just add the keyword "calculated" in front of the computed column when you want to refer to it. Here's the corrected select expression:

     select  class.*
            ,height * height  as heightSquared
            ,weight / CALCULATED heightSquared  as bmi

You can use the CALCULATED keyword in all places where you can use SQL in SAS.

NOTE: SAS Blogs Have Moved

I'm rather late in highlighting this, but "better late than never".

The SAS blogs have moved their locations. The web pages have moved, and so have the RSS feeds. So, for instance, the blogs of Angela and Chris, who I mentioned yesterday, have moved to the following locations:

Real BI for Real Users (Angela Hall): http://blogs.sas.com/content/bi/

The SAS Dummy (Chris Hemedinger): http://blogs.sas.com/content/sasdummy/

Both blogs are highly recommended and you can always find their latest posts on the home page of NOTE:.

NOTE: Charity Drive Starts Tomorrow

David Shannon, known far and wide in the UK SAS world (and beyond), is starting a charity drive from Land's End to John'O'Groats (LEJOG) tomorrow, i.e. from the most south westerly tip of Britain to the furthest point in the north westerly direction.

You may recall I mentioned this back in January, and also highlighted the fact that my wife had done the same trip (1,000+ miles) on a bike in ten days. David and Paul plan to do it in five days.

The drive will be fun(!) but is also in aid of Cancer Research UK. Never one to shirk a challenge, David (and his brother, Paul) will be making the journey in a 1974 MGB Roadster, avoiding motorways and A-roads.

You can follow David and Paul's adventure via their blog. If you'd like to offer them support and encouragement, you can contribute to Cancer Research UK via their JustGiving page.

Good luck chaps!

NOTE: Keyboard Shortcuts in the SAS Enhanced Editor (revisited)

My recent article on keyboard shortcuts generated a number of valuable responses.

PeterC (seconded by Chris@SAS) mentioned that Ctrl+i, available in EG 4.3, would automatically re-indent my code and thereby tidy it up. A very recommendation, but sadly the client in question is still running SAS 9.1.2 and hence EG 4.1.

Angela@SAS said that one of her favourite keyboard shortcuts is ctrl+? (properly known as ctrl+/). It's probably my most-used shortcut too. Ctrl+/ turns the selected lines into comments; and ctrl+shift+/ removes comments from the selected lines.

NOTE: Upgrading to SAS 9.3

Paul Homes of Metacoda in Australia (suppliers of the Metacoda Security Plug-Ins for SAS) recently published a very well written blog article on SAS 9.3, the installation thereof. Amongst all of the other good information and advice from Paul, I was particularly struck by his recommendations about the install. I've quoted them below.

Whilst SAS installations/migrations are getting more straightforward, I’d still recommend getting SAS Professional Services or one of the SAS partners to help – being businesses that install, migrate and configure SAS software on a daily basis they are very efficient and have extensive support resources at their disposal. I can’t imagine how it could be cost effective for SAS customers to acquire those skills when it’s something they might only do once or twice every few years.

Paul's comments surprised me, but after I'd given them a little more thought I concluded that I was inclined to agree. SAS UK (and many other local SAS offices) has a specialised team of installers, and many partners (such as Amadeus in the UK) offer a specialised install service. So, I certainly can't disagree with Paul's assertion that SAS and their partners do installs on a daily basis and can thus do a better job than most clients.

I'd offer some further advice though. Be sure that your chosen installers:

1. have a clear and detailed architectural plan to work from (either supplied by you, or created by your installers and handed-over to you), and
2. provide you with a clear and detailed report on i) the steps they took during the install, ii) the key options they selected during the install, and iii) deviations from the original architectural plan (with reasons for doing so).

I've seen several clients where they've out-sourced an install and then found themselves with a system of unknown provenance that they are unable to replicate for new development, test and/or production environments.

The typical SAS 9 install (if there is such a thing) is a far more complex beast than was version 8. SAS 9 deployments incorporate a lot of different technologies from a number of suppliers. The benefit of this is a SAS system that can deliver its insights in a far broader and deeper fashion than ever before; the cost of this is the increased complexity. Be prepared to out-source the install, but also make sure you're able to maintain the environment yourself after your installer has packed-up and left your site.

NOTE: Keyboard Shortcuts in the SAS Enhanced Editor

I was recently looking at a long piece of macro code where each level of indentation only used one space. It was very difficult to see where some of the %do / %end blocks began or ended. I was reminded that there's a keyboard shortcut for jumping between matching do / end blocks, so I hunted-down the complete list of shortcuts for use in the Enhanced Editor.

Sadly, I didn't find the ability to skip between matching %do / %end (it only works for non-macro do / end blocks)!! but I was reminded of Alt+[ for moving between matching do / end, and ctrl+[ for moving between matching brackets. PLUS, not spotted before, I found the ability to use F2 to add one or more (non-permanent) line markers that you can then use F2 (or shift+F2) to quickly jump to. Surely this is very useful for working on two disparate areas of a large piece of code and repeatedly jumping between them (or three, or more).

Looking over the whole list of shortcuts, it's probably not strictly necessary to document the fact that pressing the Enter key will "insert a carriage return"! But it's probably better to have too much documentation than too little.

I use ctrl+home and ctrl+end a lot in very many Windows apps to get to the top and bottom of a file. And I use ctrl+f in many Windows apps to give me the find dialog too.

Finally, I was surprised to find shortcuts for recording and playing macros. Very useful for doing repeated editing of your code. I seem to recall this was available throughout Windows but I don't see it any more. My thanks to SAS for retaining its availability.

Predictive Modelling Competition

One of my clients is running a predictive modelling competition. The competition is free to enter and has a $10,000 prize fund. Interested? Read on...

The competition is being hosted at Kaggle. It's very simple to register and to enter. The challenge is to predict when supermarket shoppers will next visit a store and how much they will spend. You're provided with a set of modelling data, and a test data set. You can enter the competition as many times as you like, there's a leaderboard that shows the score (percentage of correctly forecast visits) you need to beat, and you get immediate feedback on how well you've done.

I don't have any modelling skills but I gave it a go. I scored 8.4, placing me in 90th position. The current leader has managed to score 17.5. Can you top that?...