NOTE: Christmas Reading

If you have some spare time between Christmas and New Year you could do a lot worse than take a look at the latest issue of Views News. Issue 52 of the newsletter and tips sheet became available last week and includes articles on displaying multiple trend lines in graphs, creating word clouds in SAS, the accuracy of balanced scorecards, and more besides.

Back-issues can be found on sasCommunity.org: http://www.sascommunity.org/wiki/VIEWS_News_backissues.

NOTE: What's Happening (On Windows)?

If you care about what SAS is doing on your Windows server, you need to read Chris Hemedinger's posting on Microsoft's Process Explorer V14.01 for Windows. It seems that PE is a free utility that is downloadable from Microsoft. It's not a pretty tool, instead the information it offers could make the difference some day when your SAS service is running slow and you can't figure-out why.

QueryContest - Round 2

My post regarding QueryContest got an unexpected amount of interest. Quite apart from Chris@SAS's comment regarding his use of GoogleFight, Neil emailed me to say:

Just been reading your blog. Similar fun can be had with these…

http://www.google.com/intl/en/press/zeitgeist2010/
http://comparemyradio.com/compare/BBC_6_Music/XFM_London
http://booksearch.blogspot.com/2010/12/find-out-whats-in-word-or-five-with.html

They're an interesting collection of tools that do similar but distinctly different things to QueryContest. Thanks Neil!

NOTE: Promoting Portal Content

With serendipitous timing, Paul Homes issued a new blog post last week about Updated SAS Admin documents. He highlights the fact that there is an extra chapter in the SAS 9.2 Intelligence Platform: Web Application Administration Guide, Fourth Edition about the new facilities for Promoting Portal Content (Chapter 24).

This week I am embarking on a new engagement that includes the production of a Configuration Management Plan for a large SAS Grid deployment. My experience of doing a similar thing a couple of years ago with SAS 9.1.3 was mixed. Steve O'Donoghue (SAS UK) and I described it in our 2009 SAS Global Forum paper Configuration Management for SAS Software Projects.

Managing the bread-and-butter items such as DI jobs, stored processes, and SAS macros was fine, but we came to a halt with the Information Delivery Portal. Portal elements such as page layouts and corporate branding could not be migrated from one environment to another, e.g. development to test, or test to production. The only means of doing this was to manually make changes in each environment, i.e. manually regenerating the content and structure.

The portal promotion tools described in the Web Application Admin Guide describe how to promote the following items:

• Portal Application Tree
• User or Group Permissions Tree
• Portal content object
• Portal page
• Portal page template
• Portlet instance

I'm grateful to Paul for drawing my attention to this new information. Aren't blogs wonderful!

The Joy of Stats - Income vs Health

Chris@SAS recently tweeted a link to this: http://www.youtube.com/watch?v=jbkSRLYSojo. I found it one of the most fascinating video clips I've seen all year, on many levels. It offers a fascinating commentary on the development of the world over the last 200 years, but it is also a wonderful example of data visualisation - something that most of us in the SAS world hold a keen interest in.

Query Contest

Okay, I'm being a bit selfish with this post, but it may also give you some fun (and waste a lot of your time) over Christmas.

www.QueryContest.com compares the number of hits returned from a web search for two different terms, e.g. Beatles versus Rolling Stones, or "go boldly" versus "boldly go". QueryContest shows the number of hits for each of the two search terms - from Google, Bing and Yahoo.

In my experience, web sites of this type tend to come and go quite quickly. Either that or I keep losing the web address and cannot find it the next time I want to use it - hence the selfish idea of blogging it so I don't lose the URL!

QueryContest is limited to telling us the number of hits found worldwide for any search term, so it does not necessarily give a definitive answer to any question. Indeed, sometimes the three search engines return contradictory results (as shown for Beatles versus Rolling Stones). Nonetheless, it is useful for measuring popularity of people, things and places, and it can offer an indication of the likely correct spelling or grammar when comparing two words or phrases. For definitive answers I use dictionary.cambridge.org, but for new and emerging words and terms that are not yet in the dictionary, QueryContest helps a lot.

Have fun!

NOTE: Web Report Studio Performance in SAS TALKS

This Thursday (16th December) sees SAS's Angela Hall talk about Web Report Studio Performance Improvements in the latest of the SAS TALKS series of webinars. It's free. Just visit the SAS TALKS home page to register.

Angela is well-known through her excellent SAS-BI Blog. In her webinar, Angela will touch upon many topics for enhancing WRS performance. These will include SQL pass-through, information maps and SAS/ACCESS technologies. Book your "seat" early!

Don't forget to check-out the previous SAS TALKS webinars that are available on-demand. Just click the On-Demand TALKS tab to list them all. Finally, in January SAS's Chris Hemedinger will be talking about Enterprise Guide 4.3's new features for programmers and how you can exploit them to the max.

Browsers and the Web - Explained

I came across a nice, informative and educational web site last week. Google's Chrome team have produced 20 Things I Learned About Browsers and the Web. Needless to say, it two of its early topics are cloud computing and web apps. Nonetheless, it is very nicely illustrated and presented (using HTML 5) and I have already recommended it to a number of people I know - young and old. Take a look!

NOTE: Peek At Your Data Details Before Opening (in EG)

I spotted a nice post from Chris on the SAS Dummy blog. Enterprise Guide 4.2 saw the introduction of a feature that allows you to view the details of your data sets (such as the data set labels, how many records the data sets contain, and more) while you browse the contents of a SAS library. You can see the column names and column types right from within the file dialog. But, the feature is not turned on by default.

See Chris's article to find out more of what the feature offers, how to turn it on, and why it's not turned on by default.

NOTE: I've won a prize!!

I've just been informed that I am one of the winners from day #1 of the SAS Professionals advent calendar. I've won a copy of the book Scoring Points! The book tells how two of the UK's biggest SAS customers (Tesco supermarkets and Dunnhumby customer insights) got to where they are today. I'm looking forward to reading it.

I'm going to be busy because I'm already reading two other books for review purposes. You'll read about them in NOTE: in the next few weeks:

1) The Design of Information Dashboards Using SAS, by Christopher Simien Ph.D.

2) Performance Dashboards: Measuring, Monitoring, and Managing Your Business, Second Edition, by Wayne Eckerson

Have a good weekend...

NOTE: The SAS Professionals Advent Calendar Starts Today!

If you like a bit of festive fun (and who doesn't) I suggest you hop over to the SAS Professionals web site and open the first door of the advent calendar.

The SAS Professionals web site also contains a broad mix of groups and activities, forums and SAS-related information. It's worth joining the community...

Seasons greetings!

NOTE: Whither the WEEK Format?

My friend Eli recently wrote to tell me of a very successful 9.1 to 9.2 upgrade that was marred by just one issue. His colleague Sarah discovered that the WEEK format disappeared between 9.1 and 9.2. Sarah had been using the WEEK format to produce week numbers from dates.

With some research, Sarah managed to find an explanation in SAS usage note 34978. It tells an apparently odd tale:

For many years SAS did not have a format called WEEK. Customers often created user-defined formats with PROC FORMAT and used the format name of WEEK.

Beginning in SAS 9.1, a SAS WEEKw. format and informat were implemented but mistakenly not documented. If a customer had created a user-defined format called WEEK and attempted to use it in a program, SAS used its format called WEEK rather than the user-defined format by the same name.

Since the WEEK format and informat weren't documented anyway, the decision was made to remove them from the software beginning in SAS 9.2. This decision allows legacy code, that includes a user-defined format called WEEK, to run as expected. Now, when customers create their own format called WEEK, they will use it when they reference the WEEK format in code.

If you simply want to produce the week number for a SAS date, here is some sample code.

/* PROC FORMAT with the PICTURE statement creates a custom format. The %U directive writes the week number of the year (Sunday as the first day of the week) with no leading zero.
Today's date is written out using the WEEK format */

proc format;
  picture week '%U'(Datatype=Date);
run;

data a;
  x=today();
  put x week.;
run;

The tale seems odd because SAS took the format away completely; one might have expected them to keep it, or to offer it with a different name. However, further research shows that SAS includes several other facilities for managing week number, including new formats:

• The WEEK function - returns the week number value in varying styles
• WEEKU, WEEKV, and WEEKW formats and informats - reads and writes week numbers in varying styles

So, they took it away, but they didn't really! One might speculate that the WEEK format was added by a SAS staffer who wasn't aware of the other formats (and function). Anyway, if you're still on SAS V9.1 and you use the undocumented WEEK format or informat, you have been warned!

Keys to Success: People, Process, Technology

This blog is focused upon best practice for software development - typically using SAS software. It can sometimes seem like an eclectic mix of stuff, but there are an awful lot of things that contribute to making great software. But think about it, it's not just about making great software. Most of us have discovered that building a great new software tool will not always produce a successful project outcome. The saying "a fool with a tool is still a fool" springs to mind. The tool is only as good as the Processes that wrap around it. Taking it a stage further, the Processes are only as good as the People that use them.

People, Process, Technology - PPT

The most important factor is People; get this right and the rest will follow. You need to be sure that the users of your new, shiny SAS system are properly trained in all aspects of the system and have the appropriate professional skills, experience and qualifications too. For example, making the most effective use of SAS's data mining technology requires data mining skills, not just a knowledge of what the buttons and menus offer.

Beyond people, Process is crucial. You need to build and document your business processes and workflows before you get into detailed design of your technology, else you'll end-up with the tail wagging the dog! Train your people on your new processes, but be sure to involve them in the development of those processes in order to get their buy-in.

But, what is a business process? Put simply, it's a sequence of activities, started by a trigger event, and ending in a defined output and/or output, with specified steps performed by specified people/roles. Some examples would include:

Process#1: Produce new churn report
Trigger: Request from head of marketing
Output: New, scheduled churn report delivered to information delivery portal

Process#2: Credit score a potential new loan customer
Trigger: Potential customer telephones call centre
Output: Accept/reject potential customer's request for a loan

You need to define processes for a variety of aspects of your system, not just the business outputs. Make sure you have processes that cover support and administration too. Earlier this year I did a post-implementation review of a multi-million pound SAS analytics platform whose development had focused on technology and pushed people and processes to the side. The first few weeks after go-live had been very painful for users, IT and sponsors alike. Seeing the problems, everybody had pitched-in and had stabilised the system such that it could be used productively, but the post-implementation review warned that the system would not remain stable if  proper processes were not put in place quickly.

Some of the things highlighted in the post-implementation review were:

• No data governance model, including an absence of data ownership/stewardship/custodianship, and an absence of a strategy for dealing with data quality and data loading issues
• No change control processes for handling new groups of users, requests for new data sources, etc
• No defined support processes specifying single points of contact for key areas

At its simplest, you can document a process as a series of steps, with one or more trigger events, with inputs and outputs for each step, with named people or job roles for each step. Swim lane diagrams are often used to document this information. Start by capturing the big steps in the process, then drill into the detail.

Ignore processes and people at your peril: the success of your project depends on them far more than the technology!

NOTE: A New SAS Blog Aggregator (SAS-X)

If you like to get all of your SAS news and views from one single place, the new SAS-X blog aggregation web site (and email and RSS feed) might just be the ideal place for you.

It's not the only SAS blog aggregator available (see sasCommunity.org Planet), but it does seem to offer some unique features, such as the ability to subscribe i) to an email feed, and ii) via Facebook.

NOTE: Are Your PCs Flash Enough for BI Dashboard 4.3?

Mid-November saw the release of version 4.3 of the SAS BI clients. BI Dashboard 4.3 has been adapted to use Flash technology – that means you can now use Flash interactivity to gain insight. There's a very informative SAS Global Forum paper on this subject by SAS's Michael Thomas and Gordon Hirsch; it's entitled Interactive Dashboards: Powered by Flash and the SAS Programmer.

The use of Flash makes a tremendous positive difference to the appearance and usability of SAS dashboards. However, be aware that you'll need Flash version 10 to make use of BI Dashboard 4.3. Without this pre-requisite you'll not get to use BI Dashboard 4.3! You can check your version of Flash (in Internet Explorer) by right-clicking on a Flash element on any web page, e.g. at the top of the Adobe Flash Player home page. If you have version 10 you will see "About Adobe Flash Player 10..." in the pop-up menu.

If you're planning an upgrade to version 4.3 of BI Dashboard, make sure you read Knowledge Base article 40474. SAS BI Dashboard 4.3 moved to an RIA architecture (as well as other significant back end changes), which means that there are some major usage differences between version 4.3 and version 4.2. The SAS note explains some of the differences, and provides a single point of reference to other usage notes that highlight the differences between version 4.2 and version 4.3. Despite the interface change, the core engine remained in place so the high performance of previous versions is maintained.

NOTE: Latest SAS-Related Periodicals (plus IN in macro)

The last few weeks have seen a couple of SAS-related periodicals published:

• Issue 51 of VIEWS News is available. As ever, the archive of back-issues is a valuable resource
• System Seminar Consultants released the autumn 2010 issue of The Missing Semicolon.

I was particularly interested to read in Views News that Amadeus offer some video podcasts:

• Using the Macro IN Operator
  
• Using Proc SCAPROC to Analyse the Inputs and Outputs of SAS Programs
• Controlling SAS System Options with GETOPTION
• Improving Performance with the SASFILE Statement

I watched Ann White's Using the Macro IN Operator and was introduced to the ability to use the IN operator in macro by specifying OPTIONS MINOPERATOR. It was nicely paced and very clearly presented. Recommended.

NOTE: What Would You Add or Change? SASware Ballot is Open

The annual SASware ballot is now open for business. The ballot is SAS's annual survey of customers to see what new features and enhanced features they would like. The results are traditionally presented at SAS Global Forum. Democracy in action!

NOTE: BI Clients V4.3 Available; SAS in Microsoft Outlook (SAS Central)

Version 4.3 of the SAS BI clients was released in the last couple of days. Read more in the What's Different (Planning for Maintenance) page on the SAS web site.

Not only did I miss SAS Global Forum this year, but I also missed the announcement of the addition of Outlook integration into the SAS Add-in for Microsoft Office 4.3. The Outlook add-in integrates with a very familiar interface using the same types of behaviors expected in the application. Reports, stored processes, and dashboards are accessible from folders, similar to e-mail, organized under a special folder called SAS Central.

At each SGF, the SAS Global Forum Technology Connection is used to unveil a year’s worth of SAS R&D’s exciting new stuff. In the 2010 session, I-Kong Fu, Business Intelligence Product Manager, presented SAS Central, a new collaborative feature of SAS BI Dashboard 4.3 that provides the ability to send dashboard reports to the Outlook inbox.

A user can now check reports including KPIs, model configuration reports and sales reports from this section. One of the key benefits is that these messages are stored on the SAS server, so they don’t take up space in your inbox. SAS Central takes advantage of Outlook’s options: When you see something that needs an action, just right click and set a meeting, send an e-mail or take advantage of many other Outlook options.

You'll find lots more detail in I-Kong Fu's paper entitled "Better Decision Making with SAS Enterprise Business Intelligence and Microsoft Outlook".

I was particularly impressed by the Gadget Pane. The Gadget Pane is a separate (optional) sub-window in Outlook that can be used to display KPIs or other high-value dashboard elements. Using the Gadget Pane allows users to monitor their key metrics whilst doing other things in Outlook.

NOTE: World Programming vs SAS - Both Sides Claim Victory!

The court case between SAS and World Programming Ltd (WPL) recently ended with both sides claiming victory. No surprise there then! Outside the court, it became a battle of the press releases...

WPL's press release told of David slaying Goliath as a 30 year monopoly was ended and the High Court saying that WPS is a lawful clone of the SAS system.

Meanwhile, SAS's press release spoke of an infringement of SAS’s copyright, WPL acting outside the scope of its license agreement, and the High Court refering the case to the European Court of Justice (ECJ).

Phil Rack (US distributor of WPS) offered his own opinions in his blog.

Perhaps the most independent view was offered by UK-based IT and e-commerce law firm Pinsent-Masons in their OUT-LAW news article.

I'll leave you to draw your own opinions.

NOTE: Blog Roll Additions

I've added a couple of Australasian blogs to this site's blog roll (look at the very end of the home page to find the blog roll and a list of recommended web sites).

• At http://blog.saasinct.com/, Shane Gibson has been writing about SAS-related things since December 2007
  
• At http://platformadmin.com/blogs/paul/, Paul Homes has been writing about SAS platform administration & metadata since April this year

Both are a good read.

NOTE: More Enterprise Guide Musings

Earlier in the year I offered some reflections on Enterprise Guide, having had the benefit of spending some solid time with it as a user. There are a few more things worth mentioning...

Enterprise Guide is usually introduced as a tool that encourages the use of visual coding. Visual coding has a great number of benefits, but some of the constraints are worth mentioning here. These relate to the fact that the EG visual code editor prefers to generate SQL code.

• Each SQL node can only output one table. A single DATA step, on the other hand, can create more than one output table. This can make SQL inefficient when it needs to read an input table more than once in order to create multiple output tables - when a DATA step can read the input table just once in order to achieve the same outputs
  
• Beware the Append node. Functionally it works, but it can be very inefficient when compared with PROC APPEND. Yes, that's right, the Append node doesn't use PROC APPEND, it uses SQL. And, while PROC APPEND simply adds rows to the end of an existing table (avoiding any need to re-write the existing table), EG's Append node re-writes the whole output table (existing rows plus new rows)

The reason EG prefers to generate SQL is clear - it offers a clearer, simpler means of accessing non-SAS tables, i.e. tables stored in relational databases. And, for the most part, SQL is a very efficient means of doing data access. However, the EG visual programmer needs to be aware of the code that is being generated if their resulting program is to operate efficiently. This may be particularly true of those who are familiar with Base SAS - those new to SAS coding might not make the same (false) assumptions about how things such as Append work.

Supporting the 2010 Poppy Appeal

You may have noticed that our twitter avatar is sporting a poppy this week. This is in support of the Royal British Legion's 2010 poppy appeal - commemorating those who have lost their lives in defence of their country, and providing financial, social and emotional support to those who have served in the Armed Forces, and their dependants.

We added the poppy (temporarily) using Twibbon. It's a great way to show your support, and optionally make a donation.

You can even donate via your mobile. Just text the word POPPY to 70090 to make a donation to The Royal British Legion. Texts cost £5 plus your standard network message charge; at least £4 goes directly to the Legion.

NOTE: Registration for SAS Global Forum 2011 is OPEN

Registration for SAS Global Forum 2011 opens today - November 8th.

The 2011 SAS Global Forum (SGF) will be in Las Vegas, USA, from April 4th to 7th. SGF is the SAS professionals event of the year. Packed with papers from SAS staff and fellow professionals, it's a great opportunity to listen and learn - and to get to know other SAS practitioners. If you want to keep up-to-date with SAS knowledge, Las Vegas is the place to be in April 2011.

A basic registration for the whole conference will cost $745. This is reduced for teams of 4 or more, but it goes up(!) if you register after February 28, 2011.

Make the most of your trip and book a training session too. The opening session for the conference is at 7pm on Monday 4th, but there are plenty of seminars and courses running throughout that day.

Visit the conference web site for more details. Why not get involved and offer to be a volunteer at the event?

NOTE: Multiple KPIs in HTML

One of the most popular posts of all time on NOTE: is PROC GKPI Does Dashboards. I was recently alerted to some interesting further information by a tweet from @Technolero (Fred Janssen). In the tweet, Fred said that SAS Sample 41421 describes putting multiple PROC GKPI outputs on the same HTML page using the HTMLPANEL tagset.

I'd not previously heard of the HTMLPANEL tagset but I found some background information in the R&D area of the SAS web site. In days of yore, SAS programmers used to use PROC GREPLAY to place more than one SAS graphic onto a single page; more advanced programmers may have used the DATA Step Graphics Interface (DSGI). Now it seems that HTMLPANEL is a modern way of doing this.

You can see a couple of good examples of its use on Rob Allison's excellent site. Rob offers a SAS/GRAPH imitation of Google Analytics dashboard and a SAS/GRAPH ODS htmlpanel dashboard. Sanjaya Kumar's NESUG '06 paper described usage of HTMLPANEL in nice step-by-step fashion. Recommended.

The HTMLPANEL tagset is supported in SAS 9.1 and later.

NOTE: The SAS Experts Are In Town!

If you live or work near London or Manchester then you may be interested to know that SAS are offering a free half-day training session called the SAS Experts Roadshow and it'll be in Central London on Thursday 2nd December and Manchester on Thursday 16th December. There are a limited number of places on both sessions, so I recommend you register early.

The agenda for both is pretty similar. There's a 45 minute keynote session entitled "Becoming a SAS Expert", this is then followed by a one hour session for which you must choose between three different topics:

• The SAS Expert Channel Live - Make more of your SAS technology
• The Programming Pundit - Comparing SAS data step code with PROC SQL
• The Guide Guru - Easily access advanced programming techniques using SAS Enterprise Guide

Living up to its name, the event will be led by some of SAS UK's expert consultants. This is a great opportunity to improve your SAS skills and knowledge - for free!

NOTE: Enterprise Guide 4.1 Expires December 1st UNLESS...

You may recall my post this time last year in which I highlighted the fact that Enterprise Guide 4.1 will expire and stop working on December 1st 2010 unless you have applied 9 or later. Today is November 1st, so you have one month left if you haven't applied a suitable hotfix.

Without the hotfix, you will receive the following message, beginning on December 1st:

---------------------------
Error
---------------------------
This SAS Institute Inc. product expired on December 1st 2010.
---------------------------
OK
---------------------------

Applying the hot fix changes the expiration date to December 1, 2015.

EG 4.2 and 4.3 are set to expire on December 1st 2015.

NOTE: DATA Step's _N_ is PROC SQL's Monotonic

I've made a few posts over the last few months about SQL (particularly its use within Enterprise Guide). One of my unstated issues with SQL is the inability to do the equivalent of DATA step's _N_ to count unique rows. The ability to add a sequence number to a table, or as a contribution to a calculated column (perhaps to create a unique key), is something that I occasionally need.

I recently found PROC SQL's monotonic function in the Tips and Techniques section of the Amadeus web site. It's not documented or supported, so it should be used with caution.

proc sql;
  create table notecolon as
    select * , monotonic() as counter
    from mylib.mytable;
run;

SAS Usage Note 15138 says that the monotonic function is not supported in PROC SQL and that using the monotonic function in PROC SQL can cause missing or non-sequential values to be returned.

In the right circumstances, the monotonic function can be useful. And Amadeus have plenty more useful tips and techniques to peruse and use!

NOTE: SAS TALKS Celebrates One Year

At the same time as we mark the passing of Benoît Mandelbrot, SAS TALKS is celebrating its first birthday.

SAS TALKS is a series of free webinars offered by SAS and featuring SAS technical experts. You can watch any of the last year's ten webinars from the SAS TALKS archive. The eleventh and most recent ("Feeding Output from PROC SUMMARY into a SAS9 OLAP Cube" by Michelle Buchecker from SAS) will be available soon.

These webinars are usually presented very well and contain useful, insightful information. If you attend the live versions you can ask questions too.

The next live webinar to watch will be "That's Not What I Meant: Adventures in Defining New Variables" by A. John Bailer from Miami University. This will be broadcast on Thursday November 11th and you can sign-up on SAS TALKS' Featured page. John will shows tips and tricks for correctly constructing and recoding variables, plus pits and traps to avoid.

In December, SAS's Angela Hall (authoress of the SAS-BI blog) will talk about "SAS Web Report Studio Performance Improvement".

Benoît Mandelbrot (1924 – 2010)

I was saddened to see reports of the recent death of Benoît Mandelbrot. I'm not clever enough to understand much of his work, but I do understand that his pioneering work on fractals (fractal geometry) led not only to some beautiful images but also to highly significant computer science uses in image compression and in image generation within video games.

NOTE: Just 3 Days to Submit Your SGF 2011 Paper!

I just finished fettling my SAS Global Forum 2011 paper submission and I have submitted it. My fingers are crossed.

The deadline for papers is Monday October 25th. Have you submitted yours yet?

UN World Statistics Day

We're told that SAS no longer stands for Statistical Analysis System, but it's still used for an awful lot of statistical analysis. So, it's worth noting that tomorrow (Wednesday October 20th) has been named World Statistics Day (WSD) by the United Nations. The UN's intent is to raise awareness of the importance of official statistics and the contribution of statisticians to our society.

The day will be celebrated worldwide. In the UK, the Office for National Statistics will be working hard to use national and regional media to publicise the work of statisticians, and the Royal Statistical Society (RSS) is planning to launch a ten-year campaign to improve statistical literacy.

Jim Goodnight, SAS CEO, has published a YouTube video to commemorate the day. And you can follow WSD on Twitter: @WorldStatDay

NOTE: More on sorting and SQL

Further to my recent post on telling SAS that your data is pre-sorted, it's worth mentioning the SORTVALIDATE system option. This yes/no option tells SAS whether the SORT procedure should verify if a data set is sorted according to the variables in the BY statement when SORTEDBY has been used. There's an entertaining SAS Global Forum paper on this general subject entitled "Dear Miss SAS/Answers: A Guide to Sorting Your Data".

It's a subject worth being on top of because it can save a great deal of time and resource for your jobs.

Finally, if you're using the SORTEDBY option to avoid sorting being done within SQL joins, remember that you can you use PROC SQL's _method option to show what sorting activity is or isn't being done. Kirk Lafler presented a good paper on this undocumented feature at SAS Global Forum 2008.

NOTE: What is PROC SORTT?

My friend and ex-colleague CB drew my attention to an intriguing thread on SAS-L recently. It asked "What is PROC SORTT" and drew a response from Paul Kent

At times we've issued "the same proc with a new name" as a way of allowing us to get feedback on extensions to that procedure, without completely putting everyones production workload at risk. In the past we've seen TREPORT - new features in proc report; TFREQ - new statistics in proc freq, and SORTT - the threaded implementation... Sometimes we can prototype new features simply with new options in the existing PROC. It's usually a judgement call related to how invasive the new feature is to the structure of the production code.

We want power users to kick the tyres (for my UK friends) on some new features or implementation before they become permanent... So they remain undocumented (mostly) because the features are "transient" in the sense that they migrate into the "official" versions of the procedures, or they were not such good ideas to start with. And they remain implemented (mostly) so as not to overly in-convenience any early adopters who have been so bold as to use them in production jobs... Hope that helps y'all understand our motivation (at least a little bit)

I've not found a use for this information yet, but I thought I'd share it with you nonetheless!

NOTE: SAS 9.2 Procedures by Name and Product

I came across a list of SAS procedures by name and by product the other day. It was very useful to me at the time and I thought I'd pass it on...

http://support.sas.com/documentation/cdl/en/allprodsproc/63875/HTML/default/viewer.htm#/documentation/cdl/en/allprodsproc/63875/HTML/default/a003178332.htm

I was trying to establish which SAS programmers used which products. Most programmers can tell you what PROCs they use, but they don't know what products these PROCs belong to. Hence my pleasure at finding the nice PROC/product look-up! I was able to get programmers send me a list of PROCs typed into spreadsheets, and I was able to read the web page into a SAS data set, and could then do cross-references between my data set and their spreadsheets (read into SAS of course!).

BetterManagement Moves to the SAS Knowledge Exchange

I noticed that the oft valuable contributions at BetterManagement have moved to the SAS Knowledge Exchange web site. This was announced in a recent post from BetterManagement.

BetterManagement.com was formed in 2000 by ABC Technologies Inc.The aim was to launch an online service that provided a soft-sell style of message to potential customers. SAS acquired ABC in 2002, and hence BetterManagement too. The BM web site hosts an excellent collection of articles and on-demand webinars on a wide variety of topics, all of which gently lead the reader to consider the role of SAS software in the subject area. It will continue to be available until July 2011.

SAS tell me that content is being shifted to the SAS web site. To date, not much has been shifted. Searching the BM web site for "bicc" produces four hits, yet the SAS KE site produces none. Not a scientific test, but indicative nonetheless I think.

The four hits on the BM web site include an excellent business intelligence competency centre (BICC) audio & slides presentation by SAS's Stefanie Gerlach and Dagmar Bräutigam that I've watched previously. However, upon doing research for this article I was unable to get the presentation to roll. I hope this isn't a sign on an earlier than planned end for BM! On the plus-side, "Beyond Spreadsheets: Risks and Productivity in the Real World (video interview)" worked fine for me.

NOTE: Sort Your Sorted Data

In my previous post I spoke of using views to give you access in EG visual coding to tables with dynamic names. This works well, but there's one final observation to make if you want to avoid poor performance in some circumstances.

If you're used to using DATA step merges then you'll also be used to sorting your input data sets prior to the merge. If you're using EG visual coding then you'll have realised that you don't need to sort the data sets that you use as input to your joins. Why? Because SQL will automatically sort your input data sets for you.

Neat huh? Well, yes it is, except in cases where your input data is already properly sorted and re-sorting is a waste of resource and causes unnecessary delays in the execution of your job.

So, can we tell SQL that one or more input data sets are already sorted? Yes, we can, by using the SORTEDBY data set option.

NOTE: Marking Time with Enterprise Guide

In my recent review of a client's SAS/Base jobs' suitability for EG visual coding, I wasn't surprised to come across some jobs that created new tables each month where the table names included the date. The table names were of the form MASTER.SALES_2010_08, indicating sales for August 2010. However, the fact that the table was referenced with the use of a macro variable, i.e. MASTER.SALES_&month., meant that EG was less than happy.

This style of table naming doesn't sit well with EG's visual coding (nor DI Studio) because the process flow needs a static table name for inputs and outputs. You can use the Query Builder to create an output table whose names includes a macro variable. The code will execute and the table will be created, but it won't appear in the process flow. And you can't use a macro variable in the name of an input table for visual coding.

There are at least two approaches to take to this problem...

Project Management, Churchill-Style

Having recently marked the 70th anniversary of the start of World War II's Battle of Britain, my interest was piqued by a book sub-titled "Project Lessons from Winston Churchill and Battle of Britain". The idea of viewing World War II as a project surprised me. But the more you think about it, the more you see that, in detail, the requirements of executing a war are no different to any other project - you need leadership and governance, and you have to manage resources and tasks whilst keeping your eye on delivering the project's objectives.

Clearly, Winston Churchill's project had a higher cost of failure than any project I'll ever manage, and the risks and tasks are of a different nature to those I'm familiar with, but I can see the similarities too. So, the book's on order and I'm looking forward to reading it.

"Agile Leadership and Management of Change - Project Lessons from Winston Churchill and Battle of Britain" was written by Mark Kozak-Holland as part of the Lessons from History series.

NOTE: Celebrating Our 1st Birthday

Though I did a couple of test posts in July 2009, NOTE: really got started with its first post on Friday September 4th 2009. So, today we celebrate our first birthday.

In that year we've built a loyal band of followers. Thank you to all of you.

NOTE: is accessible in many different ways:

Just under 200 people use an RSS reader to get their SAS fix.
100 people get an automatic email copy of posts on any day in which an article is published.
73 people follow us on Twitter and get a near-instant tweet every time a new posts appears on NOTE:.
We get an average of 12 visitors to the web site each day, and they've created over 8,000 page views in the last year

I'm glad you find the content interesting. Please help us to grow further by telling your fellow SAS practitioners how informative NOTE: is, and by telling us what additional topics you'd like to read about.

Cheers!

NOTE: Reflecting on EG, Here's 4.3

I knew that EG V4.3 was due soon, but I thought I'd have time to post some EG V4.2 observations before V4.3 saw the light of day. Wrong!

Chris Hemedinger highlighted yesterday that EG V4.3 is available to those who want it.

It has much to recommend it. Most notable to me was the emphasis on new features for users of the SAS program editor. It is heartening to see that SAS recognises that visual-coding does not (yet) satisfy the needs of all SAS programmers. Thanks SAS!

See the What's New section of the SAS V9.2 documentation for the low-down on the new features.

NOTE: Reflections on Enterprise Guide (EG)

I've recently had the benefit of spending some "quality time" with Enterprise Guide V4.2 (and SAS V9.2). I've enjoyed migrating a selected sample of my client's SAS/Base programs into EG projects. The foibles of my client's code gave me a great opportunity to re-evaluate EG's visual-coding, and to inform my client of the benefits and challenges of adopting visual-coding wholesale.

Visual-coding is the term I use to describe the creation of programs by the visual connection of process nodes with data nodes using a drag-and-drop graphical user interface. A visually-coded program usually results in a flow chart-like diagram of the program's activity.

The first point I should make is that EG does not force you to adopt visual-coding. You can use EG's program node with its code editor and ignore visual-coding altogether. Compared with SAS's classic display manager interface, you'll lose the ability to use the interactive DATA step debugger and to view the content of SAS catalogs, you'll gain an integrated FTP client, seamless remote submission of your code, and you can use visual-coding for wizard-like code generation for thoser PROCs you're less familiar with.

My two primary observations about EG are:

1) It's not a visual ETL tool. If you want to generate ETL in a visual manner, get DI Studio. EG's in-built task nodes do not provide any means of updating your data. Your only output options are a) create a new data set, or b) completely over-write an existing data set. In some cases option (b) is not available within a single task (most notably in the APPEND task). In many cases EG's options will be sufficient, but the absence of SQL's INSERT and UPDATE statements, and DATA step's MODIFY and UPDATE statements, means that it may not be the best tool for developing visual ETL.

2) You need to become competent with SQL if you want to use EG's visual-coding in an effective manner. Most of EG's data manipulation tasks are based upon SQL. Particularly the Query Builder task. If you want to go beyond the basics you need to understand how to use SQL syntax such as DISTINCT, GROUP and CASE. And you need to understand what each of the different types of joins do. If you're an experienced SAS programmer you won't struggle to get to terms with the new syntax, but you do need to know it.

I'll offer some more specific findings in the next few posts. In the meantime, if you have any observations of your own, please provide a comment to this post and start a conversation. You can also make use of the EG discussion forum on the SAS Support web site; and learn lots from Chris Hemedinger's blog.

NOTE: SAS Global Forum Call for Papers

I just noticed that the Call for Papers for SAS Global Forum 2011 has opened. Details are available from the SGF Blog.

Submitting a paper (or poster) and presenting it at SAS Global Forum is a great way to share your knowledge of SAS. You have until October 25th to get your submission completed.

The internet: Everything you ever need to know

A colleague of mine pointed-out a most thought-provoking article from the Observer newspaper (my colleague knew I'd not have seen it because he knew the Observer was too erudite to be on my reading list!). The article author, John Naughton, offers nine key steps to understanding the most powerful tool of our age – and where it's taking us.

1. Take the long view
2. The web isn't the net
3. Disruption is a feature, not a bug
4. Think ecology, not economics
5. Complexity is the new reality
6. The network is now the computer
7. The web is changing
8. Huxley and Orwell are the bookends of our future
9. Our intellectual property regime is no longer fit for purpose

The article isn't short, but it's worth taking the time to read it. It doesn't cover everything there is to know about the internet, but it covers enough, in a well-considered and insightful manner, to have left me feeling more open-minded and keen to learn more about something I thought I knew well.

NOTE: SAS UK at Wittington House

Remarkably, I've never visited SAS's global HQ in Cary, North Carolina. From what I've seen and read, it's quite some place. I've driven past it whilst visiting clients in the Research Triangle Park (RTP) area, so I have some conception of the size of the estate.

SAS UK's HQ is not as large, but it's pretty grand. Take a look at this PDF to see some nice pictures and to understand the history of the Thames-side, 110 acre estate, located west of London. Originally owned by Lord Devonport who made his fortune from tea, the house features its own ghost: the "grey lady".

I'm told that part of the deal to buy the adjacent land in 1997 was a promise to retain the cricket pitch and its availability for the local village cricket team. True to their word, SAS have restored the pitch, built a new pavilion, and made all of this available to SAS staff, the local cricket team, and the local Scout group.

In SAS's care since 1985, a visit to Wittington house is always a pleasure.

NOTE: Maintenance & Hotfix, Backup & Recovery Policies

Talk in the office today led to a discussion on hotfix policy. SAS encourage their customers to apply maintenance releases when they become available, but I err on the "if it ain't broke, don't fix it" side of the fence. Any change to your production systems must necessarily involve planning and testing - and that all takes time and money.

SAS V9.2's third maintenance release shipped in May. If you're tempted to deploy it, don't forget to test it first. No matter how much testing SAS have done, it does not equate to testing at your own site.

Plus, give a second thought to backups and disaster recovery. The complexity of SAS deployments increased significantly between V8 and V9. If you have a SAS system problem, or an environmental problem, do you know how you would individually recover your data, your metadata, your WebDAV data, etc? And do you know how you would recover them so that they are synchronised with each other? SAS Global Forum 2008's paper entitled "Backup and Disaster Recovery: When Disaster Strikes, What Will You Do? What Will You Do?" is a must-read for those planning their first SAS V9 deployment. If you've already deployed V9, read the paper quickly and review your deployment!

NOTE: What program is running? aka Who am I? (SAUSAG Tips)

Having mentioned SAUSAG recently, it's worth highlighting the tips at http://sausag.sasusers.net/tips.htm.

Three of my favourites are:

1. What Program is Running?! - It can be useful for your SAS program to know its own name and location
2. PUT it _ALL_ out to the Log - in style - Here's an easy way to make that PUT _ALL_ a lot more readable
3. No more macro variable litter - Using macro variables extensively can get messy, and start to chew up memory. Since V8.2 CALL SYMDEL has enabled macro variables to be deleted

NOTE: GOPTION ACCESSIBILITY

One of my current responsibilities is to project manage a UK government procurement for statistical software. One of the software requirements is for compliance with the UK's Disability Discrimination Act (DDA). I was interested to see SAS's ACCESSIBILITY graphics option. In specific circumstances, GOPTION ACCESSIBILITY instructs SAS/GRAPH to generate descriptive text and data that is relevant to the graph, such as the summary statistics that are represented by a bar chart.

The intriguing aspect of this option is that the accessibility information that SAS/GRAPH writes to the graph's output HTML file and footnote is not visible in the output HTML file, but both are detected by an accessibility aid, such as a screen reader. The graph footnote has an "invisible" link to the chart data. Check-out the link, run the sample code and see for yourself.

I'm sure this option has uses beyond accessibility. Any thoughts?

NOTE: ...And There's a SAS Analytics Facebook Page - Should I be Excited?

It took me some while to appreciate the merits of Twitter; I'm now a fan. But I still fail to see the benefits of Facebook. If you can shed some light on it for me, please let me know. However, I feel compelled to report that I discovered that there's a SAS Analytics facebook account.

So, come on, explain to me why I should become SAS Analytics' new best "friend"! I don't get it.

I do have a Facebook account of my own, but only so I can politely accept infrequent friend-requests from friends and colleagues.

NOTE: SAS Talks Has It's Own Twitter Account!

I'm a keen fan of SAS's series of web seminars named SAS Talks. I just discovered that it has a Twitter account (@SAStalks, of course) so now I have no excuse for missing the next instalment in the series: CSSSTYLE: Stylish Output with ODS and SAS 9.2 on July 22nd.

NOTE: SAS Take World Programming to Court - Verdict Awaits

A tweet from @PhilRack alerted me to the fact that a verdict is imminent for the court case between SAS and World Programming Ltd (WPL). If you're a regular reader of NOTE: you'll already know that i) WPL's World Programming System is a SAS work-alike that appears to offer a cheaper alternative to the licensing of SAS/BASE and other specific modules, and ii) SAS filed a lawsuit in the High Court in London against World Programming Ltd in November 2009 for breach of license and copyright infringement.

NOTE: Get Your Charts in Order

I recently saw a demo of a SAS competitor's BI product. The demo was very good but marred by the fact that the date-based horizontal axis of a bar chart was in alphabetical order and the presenter didn't know how to change it. I dare say it's possible to do it, but the fact that the presenter couldn't do it on the spot suggests the product's interface is not as intuitive as it ought to be.

I was reminded of this faux pas when Angela Hall (author of the SAS-BI blog) tweeted yesterday (coincidentally) how to avoid similar embarrassment with SAS... http://support.sas.com/kb/39/904.html

NOTE: Test Your SAS Knowledge

Following-on from yesterday's article about papers from SAUSAG (a regional Australian SAS users group), test your SAS knowledge by trying their word search.

Or try one of the SAS-themed crosswords at my own web site.

Have fun!

NOTE: SAUSAG

I'm currently awaiting papers from the recent May conference organised by SAUSAG - one of the Australian regional user groups. The quarterly SAUSAG meats(!) always deliver some useful information.

However, while I wait, there's plenty of good, old stuff on their web site, including a paper entitled Reporting from your SAS BI Metadata from my old colleague Jerry Le Breton.

For those with an interest in data integration, the following two papers are recommeded too:

• DI Studio: iterative jobs to process all files in a directory - Iterative DI studio jobs to automatically find and process all files in a directory
• To Code or not to Code - A question of DI

NOTE: More Blogs of Note

I noticed that fellow blogger Chris Hemedinger recently recommended some SAS-related blogs. One was mine (my cheeks are turning a humble shade of red), another was Susan Slaughter's (mentioned in NOTE: before), and two were new to me.

I'm a follower of @AnnMariaStat on Twitter, and Chris is right to point-out her blog for those who have an interest in SAS and/or statistics and/or education. However, she went down in my estimation when she tweeted earlier today to say that she thought the upcoming Australia & New Zealand SAS Forum sounds better than the recent SAS Professional's Convention in the UK. Just kidding!

And Chris is right to highlight Stephen Philp's recent return to the blogging fold. Started in February 2005, it is a large repository of knowledge. Congratulations on the recent arrival, Stephen. Now it's time to get back to the blogging :)

The End Marks a New Beginning - With You?

Can you make use of my services? My current contract assignment is due to finish very soon. If you need a short/medium-term project manager for your SAS-related project, or you need a technical lead or development team lead, please contact me to discuss details.

Coincidentally, SAS have just issued a press release on the Basel II project that I successfully managed on their behalf recently. The press release describes how the SAS solution was tailored to provide Barclays with a global credit risk solution. Steering the SAS development team to deliver such a great success was very pleasing for me.

My most recent projects have included a SAS version upgrade; architecting a migration from a PC-centric deployment to a server-centric deployment; introducing configuration management to a development team; delivering a multi-million pound license fee saving by replacing a third-party tabulation and visualisation product with SAS software; mentoring SAS developers; and procuring statistical software for a national statistics institution (following EU procurement regulations).

All of my current projects are almost complete, but it hasn't curbed my hunger to try to find a new opportunity that is equally as challenging and demanding as the last. Do you have such a project? Please email me or call me on 01322-525672 (+44-1322-525672 for callers outside of the UK) to discuss details.

Bug Safaris - A Useful Activity?

I was introduced to a new computing term the other day: bug safari. I wasn't convinced by the idea, but I'm keen to hear others' thoughts. Why not write a comment once you've read this article. Tell me, and your fellow readers, what you think.

I've been doing some work with a company named Space Time Research (STR). They're an Australian company who produce some rather good tabulation and visualisation software.

In a 2009 STR blog entry, Jo Deeker & Adrian Mirabelli describe how the STR quality team used a "bug safari" to enhance the quality of an upcoming release. Upon reading the blog entry for the first time, it sounded to me like they just arranged for some people to randomly use the software and deliberately try to find bugs. But reading it again more carefully I could see some structure and planning elements, and I could begin to see some merit.

Conventional, structured testing is focused upon the use of tests scripts which are themselves traceable to the elements of the requirements and/or specification. In this way, you can be sure you have planned and scripted at least one test for each functional element or design element (I shall talk about the V-model in a later blog article). On the face of it there is no value in any further testing since you believe you've tested everything. But software often incorporates complex paths through it, and testing rarely produces a comprehensive test of all paths (testing produces confidence, not guarantees). So, I can see merit in allowing users to go "off piste" with their testing and spend a limited amount of time just using it and trying to break it.

As I say, testing is about producing confidence not guarantees, and I see that bug safaris can generate confidence in some situations.

What do you think? Share your thoughts; write a comment...

NOTE: SAS Talks: Making Enterprise Guide the Center of Your Business

This Thursday (24th June) sees another event in the excellent SAS Talks webinar series. Entitled "Making SAS Enterprise Guide the Center of Your Business", SAS UK's Neil Constable (author of SAS Programming for Enterprise Guide Users) will explore how to analyse information in Enterprise Guide and publish it to your organisation quickly and effectively.

It's free, just register. You'll be given the opportunity to download an entry to your calendar, and you'll be sent an email with instructions. Then tune-in via your web browser at 6pm BST on Thursday.

Neil will describe how to:

• Create a report combining output from multiple tasks and export it to PDF or HTML.
• Redirect the formatted output from a task directly into Excel.
• Add extra formatting commands to your output, such as page orientation.
• Build linked reports and graphs.
• Publish from Enterprise Guide directly to the Web.
• Turn an Enterprise Guide task into a stored process, complete with dynamic and cascading prompts.

Neil will show you how to get the most out of your investment in SAS software and in your adoption of Enterprise Guide.

And, make a note in your diary for the next event in the series: "CSSSTYLE: Stylish Output with ODS and SAS 9.2" by Cynthia Zender on July 22nd (registration opens 8th July).

NOTE: SAS BI Logs Made Easy

I just found a couple of useful tools that I'm looking forward to using next time I have a problem with a SAS BI server!

I've been quiet over the last couple of weeks because I've been working from home and looking after the kids while my wife has cycled the length of Britain (from Land's End in the South West of England, to John O'Groats in North East Scotland). It's a 1,000 mile journey and Cathy did it in 10 days. I'm very proud of her, and so are the kids. You can see her blog of her journey at http://LeJogBlog.TheRatcliffes.net. (and you can still donate to her chosen charity and congratulate her on her success - don't forget to say you're a NOTE: reader)

Sadly, taking care of the kids' requirements for dropping-off at school, and picking-up from school, and dinner, and help with homework meant that I was unable to attend the SAS Professionals conference at Marlow. I will be there next year. It's an excellent forum to pick-up new SAS-related information and to catch-up with old friends from the SAS world.

Anyway, now that life in the Ratcliffe household is back to normal (well, as normal as it ever is) I have time to get back to the NOTE: blog...

...and I came across a SAS e-newsletter article from 2007 that described a couple of tools that SAS made available for reading SAS BI server logs.

• The SAS BI Color Coding Application color codes SAS BI log files and highlights important information. The application makes it easier to read and understand the log files

• The Object Spawner Request Summary Report Application reads SAS Object Spawner log files and produces reports and graphs of what it finds

Both sound useful. I'm just waiting for a problem so I can try them out for real!

NOTE: The APPEND & INSERT System Options

Did you spot the APPEND system option? And his compatriot INSERT? Both are extremely useful when you have a chain of configuration files. By default, the most recent specification of a system option (such as SASAUTOS) is the one that SAS will use. INSERT and APPEND allow you to add values to these options, either at the beginning (INSERT) or at the end (APPEND).

The full list of options to which INSERT and APPEND can be applied is: FMTSEARCH, HELPLOC, MAPS, MSG, SAMPLOC, SET, SASSCRIPT, SASAUTOS, or SASHELP.

So, for instance, if you have an installation-wide configuration file that specifies the standard SAS macro library concatenation, and you have an application-specific configuration file that needs to specify the application-specific macro library, you no longer need to repeat the standard SAS libraries in the app-specific config. Instead, you can just INSERT the app-specific library at the head of the concatenation and thereby de-couple your app-specific config file from the installation-wide config.

The SAS 9.2 Language Reference: Dictionary provides the low-down detail.

NOTE: SAS abbreviations

I'm familiar with little pop-up tips in many development environments for many languages. These tips spot that you're about to type a call to a function (for example) and show you the template syntax for the function. I didn't realise until recently that this was possible in SAS too.

I frequently struggle to remember the parameter order for the TRANWRD function - it seems counter-intuitive to me - but no longer because my abbreviation pops-up a prompt whenever I begin to type TRANWRD.

NOTE: SGF 2010 in Social Media: Archive

I got stuck whilst trawling though old tweets regarding this year's SAS Global Forum (SGF). I very quickly hit the following message from Twitter: Older tweets are temporarily unavailable. Repeated visits haven't resulted in a different outcome. But I have found a solution.

One of the last messages that's actually available revealed the solution: TwapperKeeper. It's a bit of a strange name but in essence it's an archive for tweets. So, if you visit http://www.twapperkeeper.com/hashtag/sgf10 you'll find an archive of SGF tweets. And you can create your own archives too. Looks useful. Give it a try and let me know...

NOTE: Stored Process Server vs Workspace Server - What's the difference?

A favourite interview question of mine is: Compare and contrast SAS 9's stored process server and workspace server. This question is very good at revealing whether candidates actually understand some of what's going on behind the scenes of SAS 9.

I ask this question of developers as well as administrators and architects. Whilst some developers don't need to know this behind-the-scenes information, the fact that a developer has an inquiring mind and has made the effort to find the information tells me something positive about that person. And, I'm not certain that developers don't need to know how their code is executed...

NOTE: SAS Editor Shortcuts (DMS only)

I stumbled across a very interesting paper the other day. Not only was it written (with others) by my old friend John (H) King but it spoke about something I've never explored before: customising the Display Manager (DMS). The paper is entitled "Automagically Copying and Pasting Variable Names" and was presented at Coders' Corner at this year's SAS Global Forum (SGF) in Seattle.

The paper describes two methods that use some less well known base SAS techniques that let you easily build tools to accomplish common tasks. One method lets users right-click on a file in a SAS explorer window and then select a task from a menu. The other lets you assign a key that, when pressed, gives you or your users a way to specify a one or two-level filename and then run a particular task on the file. Both methods give you an easy way to accomplish virtually any task, process or analysis.

Recommended reading!

Developer Testing

When I took over a "failing" development team in a high-profile banking project in London, I introduced a simple form for handing-over code from the development team to the system testing team. Apart from details such as why the change was being made and how the code should be transported from Dev to Test environments (and properly installed), the form included a tick-box to say that developer testing had been done (and fields to specify where the test code and test output was archived). Prior to this the developers had complained of being pressured to hand-over untested code in a management rush to get development work "completed".

I told the team that I wanted the form filled-in honestly and openly. I told them that if they had done no testing because they had been pressured into delivering before they had time to test it, they should write this fact on the form and must not tick the "tested" box. I told them they had my full support if anybody came back to them and complained about the quality of the code after they'd not tested it due to management pressure. Of course, I also told them I wanted to be told if they felt they were being pressured to skip testing. And finally, I told them that I expected them to include appropriate time for appropriate amounts of testing in any plans they put forward.

NOTE: SAS Professionals Convention 2010

If you are a SAS professional and you're within travelling distance of SAS's UK headquarters between June 15 and 17, you should give serious consideration to attending the 2010 SAS Professionals Convention. If you only have one shot at training this year, this should be your choice. My own experience from attending past conventions and SIG barbecues is that these events offer focused, detailed information about SAS software, and they offer excellent opportunities to talk to fellow professionals (often more valuable than the sessions), in a relaxed and comfortable atmosphere.

This year's three-day conference features a keynote session from Chris Hemedinger (a senior member of the team that builds SAS Enterprise Guide & SAS Add-In for Microsoft Office, and author of the Sasdummy blog and the SAS for Dummies book). The agenda shows one day focused upon industries, another focused upon technology, and a third focused upon career development.

The conference costs £75 for all three days if you book before 24th April. You cannot get better value than that! But I didn't yet mention that SAS are also offering free usage of the "SAS Expert Channel" until the end of the year. The expert channel is a series of live web training courses.

Will I see you there?...

Established at the start of 2009, the SAS Professionals network has grown from strength to strength. In fact, the SAS UK web site has an interesting review of the benefit it delivered to SAS and its customers across 2009.

Are you aware of PBLS, the Premier Business Leadership Series? It's a business conference for business leaders organised by SAS in three continents: Europe, North America, and Asia-Pacific (okay, four continents!). This year's European event is in Berlin from June 21 - 23.

NOTE: Go Green and Save Data Miles

Whilst attendees of SAS Global Forum (SGF) in Seattle, USA have been hearing about further SAS developments in in-database processing, I've been spending time back home in Blighty enlightening one of my clients' junior staff in the benefits of SQL pass-through. If your data is held outside of SAS (in Oracle or Teradata for example), you need to understand pass-through.

It's all about doing things in the best place. Typically, the channel that passes data from non-SAS data storage to SAS software is relatively slow when compared with the non-SAS data storage's ability to process data. Expressed more simply, you're better off asking Oracle to apply a where-clause and send the results to SAS than if you ask Oracle to send all records to SAS and allow SAS to apply the filtering. SAS may be able to filter data quickly, but you're better off not sending all that data down the pipe in the first place.

Pass-through is a SAS technology that passes SQL instructions to the data storage server for execution. There are two types of pass-through: implicit and explicit. Implicit pass-through is the cleverest. Explicit pass-through is where you offer statements to SAS that specifcialy (explicitly) instruct SAS to pass instructions to the data server; with implicit pass-through, you rely on SAS intelligence to look at standard SAS code and deduce that it could interpret some of it into SQL and pass it through to the data server. Steve Feder's NESUG 2007 paper succinctly describes alternative techniques and their respective merits.

In-database execution of SAS merely takes pass-through a step further. Pass-through allows execution of SQL within the database; in-database execution of SAS allows far more work to be done within the database server and thereby further reduce the amount of data sent down the long and slow pipe from database to SAS software.

Beginning with Teradata in 2008, SAS began partnering with database vendors to develop and deliver in-database execution of SAS. At this year's SGF, SAS announced the engagement of BlueCross Blue Shield of Tennessee in a pilot program embedding SAS Analytics within the company’s IBM DB2 enterprise data warehouse. SAS currently has plans to provide in-database execution of SAS software within Teradata, Netezza, IBM DB2, HP Neoview, Aster Data and Green Plum. These are exciting initiatives that will take the speed of SAS analytics to a new level.

NOTE: Roots and Powers in SAS MDX

I had a problem today whilst trying to calculate RSS (square root of the sum of the squares) in an OLAP cube. I'm the first to admit that I'm no MDX expert (Multi-Dimensional Expressions - the language for querying OLAP cubes). Creating the sum of the squares was okay, but getting the square root was more difficult than expected.

MDX is the de facto standard language for querying cubes, analogous to SQL's ubiquity for querying "two-dimensional" relational data. It emerged from Microsoft's OLD DB for OLAP and was first supported by SAS with the V8 version of OLAP Server. MDX provides powerful functions for handling multi-dimensional collections of values. SAS's support for MDX means that you can connect a wide range of OLAP clients to a SAS OLAP server. OLAP clients that support MDX (and can hence be used with SAS OLAP server) include Excel, Cognos, ProClarity and Business Objects.

RSS is a measure of accuracy (estimated uncertainty) and is calculated by taking the square root of the total sum of each of the observed values squared. More often it is the residual values after comparing a model with observed values that are squared, summed and then rooted. Either way, the underlying calculations consist of a) the sum of the squares of the individual values, and then b) the square root thereof.

Summing-up values is the kind of thing that SAS OLAP server eats for breakfast, and MDX (whilst not directly supporting a square root function) provides an exponentiation operator (^, caret), sometimes also implemented as a function (power). So, I was surprised when I used ^0.5 to request the calculation of a square root and I got an error message.

Investigation showed that SAS V9.1.3 SP4 did not originally provide a "power" operator or function (see the SAS 9.1 OLAP Server MDX Guide). As far as I can see, it still doesn't, but problem note 17440 describes how hot fix E9BA09 introduced a square root function (SQRT).

Having scanned the V9.2 MDX documentation quickly this evening, I see no reference to caret (^), power, or exponentiation, nor sqrt (problem note 17440 says it was fixed in V9.2 TS1M0).

It seems most strange to me that SAS should continue to deviate from a full implementation of MDX. Whilst MDX is not a formally ratified standard, it *is* a stable, de facto standard. It is disappointing to see gaps in SAS's support of MDX. I tried to find some information on further gaps in the SAS implementation of MDX, but none seems to be available.

What's your mileage with SAS MDX? Have you found any further gaps or differences? How do you do exponentiation?

NOTE: Clean-Up, But Don't Lose Anything (omabakup)

My talk of spring-cleaning reminds of days spent searching for stuff in my home-office only to find that my dear wife had spring-cleaned them to somewhere "more suitable" but less obvious to me. In these cases I hadn't actually lost them, but for a period of time I believed I had.

Losing some of your SAS data or metadata could be far more costly to your business than me losing my favourite pen from my desk, so you'd better to be doing good, reliable, regular and frequent backups. In most cases, sites are pretty good at backing-up disks using standard back-up routines, but the SAS metadata server's data needs special attention. Read on...

Before backing-up your metadata repositories, you need to pause the metadata server so that there are no attempts to change the data whilst it's being backed-up. Without pausing the metadata server you may find that the back-up fails or (worse) you may find that your back-ups are useless and cannot be restored.

The easiest and most effective means of pausing, backing-up, and then unpausing is to use SAS's supplied omabakup facilities. The SAS install/config process creates backupServer.sas and restoreServer.sas in your SASMeta\MetadataServer directory(s). These are pre-configured to suit your environment. backupServer.sas writes the backup files to a subdirectory called SASBackup in the MetadataServer directory. After backupServer.sas has run, your standard back-up routine can backup the contents of this directory at its leisure.

Alternatively, you can use the Backup Wizard from the Metadata Manager node in Management Console. This allows you to create jobs for immediate execution, or for later; and it automatically creates an equivalent restore job too. Tidy!

SAS offers advice about best practices for back-ups and restores in the SAS 9.2 Intelligence Platform: System Administration Guide. And, not surprisingly, Angela Hall's SAS-BI blog offers more than one valuable insight and tip for the use of omabakup.

Back-ups of your business data are crucial to the safe operation of your business, but they must be effective. Back-ups of your SAS metadata will not effective unless you are aware of the need to pause the metadata server beforehand. Using one of the facilities provided by SAS, you can be assured of reliable back-ups of your valuable metadata.

NOTE: Spring Cleaning

Spring has sprung, although it's difficult to detect here in South East London. With spring comes thoughts of spring cleaning. In the SAS context, the following two tips spring to mind:

1) Clean-Out Your Dead WORK Libraries

SAS uses the WORK library throughout the duration of your SAS session (local and remote). When your SAS session ends, SAS will automatically delete the WORK library. Specifically, your configuration options specify a "master WORK directory" where each new SAS session creates a sub-directory to use as its individual WORK library. Each individual SAS session deletes its individual WORK directory when it terminates. However, if SAS is cancelled or terminated abnormally then the individual WORK directory won't be deleted (nor its contents). Over time, the master WORK directory will get full of orphan WORK (sub)directories and their contents.

This applies not just to remote sessions belonging to SAS/CONNECT and workspaces pertaining to Enterprise guide; don't forget that your metadata server is a SAS session, as are your stored process server sessions, etc.

You need to regularly clear-out the master directory in order to avoid running-out of space, but you need to avoid accidentally deleting sub-directories that belong to active (non-orphan) SAS tasks. The SAS-supplied cleanwork utility is the answer. Yes, that's right, SAS supply a utility to keep your master directory clean, and it will check the provenance of each directory before deleting it, so it won't try to delete directories that belong to active SAS sessions. Whilst it can be run by individual users to tidy their own directories, it is best run as a scheduled admin task ans given appropriate privileges.

Check-out the operating system-specific SAS documentation for details. Read the documentation for Windows carefully - the last time I used cleanwork on Windows it was just a SAS program that deleted all directories older than a given date.

2) Tidy-Up As You Go

Help to reduce the overall size of WORK space usage by deleting temporary data sets as you go along. If I'm writing a modular program composed of a number of macros, I use the macro name as a prefix for all temporary tales created by the macro. By doing this I can easily delete all of a macro's temporary data sets at the end of the execution of the individual macro by using the colon (:) wild card in PROC DATASETS DELETE. The SYSMACRONAME automatic macro variable contains the name of the active macro, so it's easy to use the macro name as a prefix for all temporary data sets, and then delete them at the end of the macro. See the example below.

%macro leyland(...);

  data work.&sysmacroname._demotemp;
    set sashelp.class;
  run;

  ...

  proc datasets lib=work nolist;
    delete &sysmacroname.: ;
  quit;
%mend leyland;

Of course, this is not good for debugging! Deleting all of the temporary data sets automatically makes development and debugging very tricky. So, I add a parameter to each macro (passed from one embedded macro to another) that indicates whether the macro should tidy-up at its end. See below.

%macro leyland(tidy=y, ...);

  data work.&sysmacroname._demotemp;
    set sashelp.class;
  run;

  ...

  %if &tidy eq y %then
  %do;
    proc datasets lib=work nolist;
      delete &sysmacroname.: ;
    quit;
  %end;
%mend leyland;

Keeping your usage of WORK to a minimum will help your operational jobs run more reliably, and will save on the cost of disk space too. The two tips above will help you with the challenge.

NOTE: "SAS Talks" Has a Home

The excellent SAS Talks series of free webinars (previously featured in NOTE:) has now got a home page where you can find previously recorded talks, plus information about upcoming talks.

So, you can now easily catch-up with the previous talks on SAS Enterprise Guide, SAS/IML, ODS statistical graphics, and multi-sheet output for Microsoft Excel from SAS.

The page shows two upcoming talks: Modifying ODS Statistical Graphics Templates in V9.2 later in March, and Formats and the Format Procedure in April. Registration for the former opens today. If you watch it live you can question the speaker.

Project Plans in Excel - As a Chart

The series of posts on project planning in Excel with Gantt charts has been very popular, and one of the most popular questions has been "Why don't you use an Excel chart?" Well, the answer's simple, they don't work very well for large list of tasks. For small lists they look very nice, but they don't scale well, hence I prefer to keep my Gantt in the cells of the worksheet. However, for completeness I thought I'd offer this bonus post to show how it's done. You can see the end result alongside this paragraph (right). I'm using Excel 2003.

We'll start with the result from the last post, including the progress bars in the worksheet. You can see it alongside this paragraph (left). Since we used the cells to indicate progress, we were limited to showing progress in chunks of whole days. In the chart we will be able to show a more accurate picture of progress.

I'm going to start by removing the groups that we had in the last result - I've never explored how they can successfully be charted. So, let's select the input data area (A2 to F9), go to the Subtotals window, and click the Remove All button (then confirm that you understand that entire rows will be removed). Our chart collapses and looks as shown below.