NOTE: The blog of RTSL.eu - Development with SAS®

NOTE: Code Kata #1 Follow-Up

2012-02-07T09:00:00.000Z

A few weeks ago I published the first in an irregular series of code katas. In a previous post I had described code katas as a form of training for programmers.

I saw a lot of interest around the post.

Richard Koopman posted an elegant response on his blog, including an additional solution for US coinage. His solution was neat, making good use of arrays and iterative coding, but my attention was also caught by Richard's home-made phone stand for his iPhone. Nice work Richard.

My old friend Dave Booth emailed me a solution that he had written off the top of his head, with no access to SAS at the time. It worked. I was suitably impressed. Dave's solution, like Richard's, used what Dave describes as a greedy algorithm. This is a very descriptive name and the Wikipedia entry offers the "coin change" challenge as an example. Dave also made a good point about handling errors elagantly:

Putting something in the code to try to trap unforeseen outcomes is a good habit to get into.

I agree, and code katas are a good opportunity to practice error trapping strategies.

Finally, Dave drew my attention to an excellent source of puzzles that could form code katas: ProjectEuler.net. The site offers a series of challenges that require a mixture of mathematical and computer science skills to solve. Take a look at the list of challenges and you'll see some good starting problems such as "Add all the natural numbers below one thousand that are multiples of 3 or 5".

In summary, don't wait for me to post more code katas. There are plenty out there on the web, and they all offer great opportunities for you to improve your coding skills.

"The Delegator" - Positive or Negative Epithet?

2012-01-31T12:00:00.001Z

In a recent meeting, an action arose and I asked one of my team (PJ) to catch it and complete it. He (tongue in cheek) commented to all those at the meeting that I was "the best delegator" he'd ever worked with. His humorous implication was that I don't do any work, I just get other people to do it for me. I've known PJ for years and worked on many successful projects with him, so I was happy to be the butt of his joke. However, I see delegation is a positive thing, so it set me thinking about how different people understand the verb "delegate".

The dictionary says it means "to give a particular job, duty, right, etc. to someone else so that they do it for you". The fact that one is giving one's work to somebody else to do doesn't sound altogether positive, but if we accept that a team can do more work than one individual, and if we accept that a team needs a leader to coordinate and dispatch work around the team, then the team leader isn't delegating his/her own work, they are simply spreading the team's work around the members of the team.

However, in my mind, delegation is a tool to help the team learn and grow. And that means that I choose to delegate some of the management work to members of the team also. By doing so, I can coach them to learn new skills; and I inevitably learn new things as we go along too.

Not everyone wants to be a manager, but everybody needs to understand the objectives and challenges of management. Armed with knowledge of management, one can work in harmony with one's leaders and management. Moreover, it is often the case that the perception of the team leader's role is not a match for the actuality. For me, team leadership is about a) achieving more than I can manage on my own, b challenging myself and my team to do new things, c) helping my team members to become the best they can become, d) learning from others, e) enjoying the benefits of being part of a successful team.

So, "The Delegator", is it a positive or a negative epithet? In my mind it is a distinctively positive name. It means I'm helping my team to learn and grow, and I'm helping them with their career and their ability to enjoy their working days (and at the same time they are helping me to do the same).

You Can't Fire Everyone

2012-01-24T12:00:00.002Z

Some years ago I was taught a memorable and valuable lesson by my erstwhile programme manager (we'll call him BJ). I was brought into the project to provide knowledge of i) business intelligence, and ii) software documentation appropriate for the regulated pharmaceutical industry. After a while on the project, BJ asked me to act as BI Delivery Lead. He promised me the ability to recruit a team but initially supplied me with two existing staff members.

After a week I went back to BJ and said he'd have to provide me with two different staff members because the first two a) knew nothing about BI, and b) were at each other's throats all day long. BJ told me that nobody else was available; I'd have to make it work. Then he put his hand on my shoulder and told me he'd especially chosen me to act as BI Delivery Lead because he knew I had good people skills. Flattery is a good management technique! But more importantly, BJ was teaching me an important team leadership lesson: developing people is just as important as developing software.

It's a lesson I'll never forget and, as I worked with my two staff members over the following months and we all achieved increasing degrees of success, I discovered that developing people can be at least as satisfying as developing software.

I was reminded of this specific lesson when I read Roy Osherove's You Can't Fire Everyone post on his 5Whys blog in December. Roy's blog is a treasure trove for those who want to become a better team leader. Roy typifies the process of team development as following three phases: the chaos phase, the learning phase, and the self-leading phase. Roy describes how to recognise which phase you're currently in, and how to progress and develop your team. Recommended reading.

NOTE: Monitoring the SAS 9.2 Metadata Server

2012-01-24T12:00:00.000Z

There's a nice walkthrough of some of the features of the SAS 9.2 metadata server on the SAS Professionals web site in the Social Media / Videos section. You'll have to be a SAS Professionals member to view it from the link I've provided, but if you're not already a member it's a good reason to join now (it's free).

The 7 minute video is particularly useful if you've recently upgraded from 9.1 and would like a reminder of the nice new features you now have available to you.

It particularly caught my eye because I've just recently added the SAS Platform Administration: Fast Track (SPAFT) five-day course to my training repertoire. So, if you plan to attend this SAS UK course this year, you might just find me teaching you (please don't let that put you off booking!).

Code Kata #1

2012-01-18T12:00:00.000Z

In yesterday's post I described Code Katas and how they can be a useful form of programming practice. Dave Watson (the "inventor" of Code Katas) offers 21 on his Code Kata blog. I intend to publish my own from time-to-time, and here is the first. Enjoy...

Imagine you have to write some code for a vending machine. You are given a) the price of the product to be vended, and b) the amount of money tendered by the customer. Your task is to deduce what change to offer, i.e. how many of each type of coin. Optimise your result by minimising the number of coins given in change. Assume your vending machine has unlimited stocks of the following denomination: 1p, 2p, 5p, 10p, 20p, 50p, £1, £2.

Two examples of input and output follow:

i) Product price is 70p; money tendered is £1, i.e. 100p. Optimal output is 1 x 10p and 1 x 20p

ii) Product price is 63p; money tendered is 80p. Optimal output is 1 x 10p, 1 x 5p, 1 x 2p

This challenge is entirely language-independent, so your solution can be produced in the manner that best suits your regular coding activities. Hence, you might write your solution using DATA Steps and/or you might use macros. Your input values might be provided as global macro variables, or parameters to your macro(s), or supplied within a data set.

Once you've cracked the above, you might want to consider the following additions - perhaps on subsequent days.

A) Accept a "list" of coin denominations as additional input rather than hard-coding the list I supplied above. The list might be as a parameter to your macro, or in a data set

B) Rather than assume an unlimited stock of each coin in the machine, accept an input "list" that tells you how many coins of each denomination are in the machine. Consequently, you will need to consider the possibility that your result is a refusal to vend due to "insufficient change"

C) Rather than writing a simple textual result to the SAS log, produce some kind of coloured report or graph depicting the change given

You also might like to consider different solutions. The use of arrays or hash tables would be two alternatives.

Code Katas

2012-01-17T12:00:00.001Z

Last week I made a brief mention of Code Katas and how they can help you as a form code practice. Let's find out some more.

The basic concept surrounding Code Katas encapsulates the ideas that a) we are professional coders, b) practice improves one's skills. Wikipedia tells us "Code Kata is a term coined by Dave Thomas, co-author of the book The Pragmatic Programmer, in a bow to the Japanese concept of kata in the martial arts. A code kata is an exercise in programming which helps a programmer hone their skills through practice and repetition".

Dave has a blog for his books, and a sub-blog specifically about Code Katas. His Code Kata blog tells us all about the background and Dave's reasoning for using Katas.

I greatly enjoy the consultancy and the project management work that I do, but I get at least as much enjoyment from coding too. Not only coding to a specification, but coding for coding's sake. And that, in a nutshell, is what a Code Kata is - coding for coding's sake. If you were into tennis, you might focus on competitions wherein you have to give your best, but you'll be able to achieve better results in those competitions if you practice regularly. There are no prizes for practicing, but it sure helps.

Who benefits if you become a better coder? Your employer or client will certainly benefit, but your own career prospects will improve too. So, don't limit yourself to doing your Code Katas within core hours. Maybe it's something you might consider doing over lunch, or after hours.

Dave lists 21 Katas on his site, most of which I've found applicable to SAS. I'll offer my own contributions from time-to-time here on NOTE:, starting tomorrow. In the meantime, please feel free to send your own Code Katas to me. I'll publish some of them.

Engage More Closely With NOTE:

2012-01-16T12:00:00.000Z

The NOTE: blog is powered by Google Blogger. Google have rolled out a number of improvements to Blogger recently. One of those improvements is support for threaded commenting, which means that it is now much easier to differentiate between whether someone is making a general comment on the thread, or responding to another comment on the thread.

I hope this will mean that we can engage in a more full and lively conversation in the comments on each posting.

What do you think?

Do You Read? (And Write?)

2012-01-11T12:00:00.000Z

Whilst New Year's resolutions are still in our minds, I'm thinking about my training plans for 2012. One key element of my plan is attendance of SAS Global Forum in April, but at the other end of the scale is my choice of professional reading material - not just for the flight to Orlando.

Stephen King is widely quoted as saying "If you want to be a writer, you must do two things above all others: read a lot and write a lot." Stephen King is undoubtedly a successful author so I must assume he knows what he's talking about; and I think there is a significant degree of similarity between writing programs and writing novels.

Let's start with reading. In my experience, very few SAS developers own a SAS-related book or a software engineering book. If developers don’t know about the fundamentals of software engineering (not just code syntax but also issues like configuration management and testing) how can they explain them to non-technical folks like sales and upper management once they’ve become technical leaders?

SAS Publishing doesn't offer too many titles that address software engineering for SAS projects, but don't let that hold you back. There are plenty of good books that offer insight that is relevant to the SAS practitioner. My own bookshelf includes classics and contemporary books alike (in no particular order):

The Mythical Man Month, by Fred Brooks [amazon.co.uk]
Refactoring: Improving the Design of Existing Code, by Martin Fowler [amazon.co.uk]
Planning Extreme Programming (XP), by Kent Beck & Martin Fowler [amazon.co.uk]
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, by Ralph Kimball [amazon.co.uk]
The Data Warehouse Lifecycle Toolkit, by Ralph Kimball [amazon.co.uk]
Waltzing with Bears: Managing Risk on Software Projects, by Tom DeMarco and Timothy Lister [amazon.co.uk]
Head First Software Development, by Dan Pilone & Russ Miles [amazon.co.uk]
Algorithms + Data Structures = Programs, by Niklaus Wirth [amazon.co.uk]
What is Lean Six Sigma, by Mike George, Dave Rowlands & Bill Kastle [amazon.co.uk]
UML Distilled, by Martin Fowler [amazon.co.uk]
Information Revolution: Using the Information Evolution Model to Grow Your Business, by Jim Davis, Gloria Miller & Allan Russell [SAS bookstore]

And what about code reading? Peer reviews are a great driver for reading the code of your colleagues and learning from them, but if you want to really open your eyes to new and different techniques you might consider taking a look at the code of one or two of the many open source projects available on the web.

With regard to writing, you might feel you already write enough code to be able to tick this box. Have you ever stepped back to consider the amount of time spent reading and writing emails, reading and writing specifications, and attending meetings? Perhaps you don't spend as much time actually coding as you thought. Maybe you should consider doing “Code Katas” — little practice sessions, based on a concept borrowed from karate and other martial arts, where the practitioner fights against an imaginary opponent.

Practice for the sake of practice is commonplace in sports and many other pursuits, why not programming? The term coined by Dave Thomas, co-author of the book The Pragmatic Programmer, in a nod to the Japanese concept of kata in the martial arts. A code kata is an exercise in programming which helps a programmer hone their skills through practice and repetition. I'll return to this subject in subsequent articles.

In summary, don't leave your professional development in the hands of your manager. Consider adding a professional book to your bookshelf (and read it too). And challenge yourself to do a little off-topic coding in order to broaden your knowledge and experience. You'll thank me for it!

NOTE: Programming for Job Security (Golden Oldies)

2012-01-10T12:30:00.000Z

This post ends the series of golden oldies. Looking through past conference papers has revealed many interesting nuggets. I was interested to read SAS co-founder John Sall's paper on PROC MATRIX, the forerunner of SAS/IML. This paper was presented at the first SUGI, in 1976 - known SAS One.

I was also pleased to see some opinion pieces too. I saw two papers by Grant Blank on the subject of the obsolescence of SAS (SUGI 10 in 1985 and SUGI 16 in 1991), and another by Barbara Ison & Dania Ferguson comparing the newly-introduced macro facility with the existing ability to use LINK statements within DATA steps (SUGI 17 in 1992). I don't necessarily agree with too much of their arguments, but I firmly respect their freedom to offer their opinions. Discussion and opinion seems to have been lost in modern SAS conferences. The focus of papers is now on case studies and product features. Both important and valuable in their own right, but I miss the variety that used to exist.

Whilst there is a wealth of good quality papers and articles available, no series of golden oldies could end without the inclusion of Art Carpenter's seminal series on Job Security. Software engineering can be a pretty dry and humourless topic, but Art managed to inject some real humour into the proceedings of several conferences. His first paper on the subject was at SUGI 18 in 1993, but I choose to highlight the paper he co-presented with my old friend Tony Payne at SUGI 23 (1998) in Nashville, Programming for Job Security Revisited: Even More Tips and Techniques to Maximise Your Indispensability. Comedy gold, but also a series of high quality tips for making sure your programs are successfully peer-reviewed.

NOTE: Keeping Track of Defects

2012-01-10T11:30:00.000Z

I recently stumbled across an article on David Biesack's Peer Revue blog regarding SAS's defect tracking process. David provides a detailed and extensive insight into how the guys and girls in Cary (and beyond) deal with defects, from beginning to end.

If you've ever raised a Track that was acknowledged as a bug, this article tells you what went on in the background prior to you getting a hotfix or a new version of SAS code. It's interesting reading, plus there's plenty of good practice that we can all use in our own development efforts. David highlights a number of things:

Effective tracking of defects (through their whole lifecycle)
The use of a source management system to keep track of versions and releases of code
Adoption of the Open/Closed Principle whereby code should be open for extension, but closed for modification. Expressed another way: when a single change to a program results in a cascade of changes to dependent modules (e.g. other macros or DI Studio jobs), that program exhibits the undesirable attributes that we have come to associate with “bad” design. The program becomes fragile, rigid, unpredictable and unreusable. The open/closed principle attacks this in a very straightforward way. It says that you should design modules that never change. When requirements change, you extend the behavior of such modules by adding new code, not by changing old code that already works. I wrote about this in NOTE: Coupling, Bad back in May of this year

David's article mentions the SAS Quality Imperative - SAS's commitment to producing quality software and services. This is an informative white paper and well worth a read. David's link is broken but you can find the paper here.

Learn How to Code in 2012

2012-01-10T09:16:00.001Z

Do you know someone who could benefit from learning how to code? Are your parents looking for a new hobby in retirement? Are your kids looking for wider career options? Send them along to http://www.codeyear.com/ and encourage them to sign-up for the codecademy course. It's free.

Back in November there was an interesting piece from the BBC’s technology correspondent, Rory Cellan-Jones, entitled Coding – the new Latin. Rory referenced a report from NESTA (the UK’s National Endowment for Science, Technology and the Arts) called Next Gen. Transforming the UK into the world’s leading talent hub for the video games and visual effects industries needs a generation of school leavers who have some skill and knowledge of coding. There seems to be a worldwide push to reinstroduce some kind of computer coding education back into schools. I support that. I certainly enjoyed learning computer skills when I was at school, and I know it benefited me in my career.

NOTE: Coding "Missing Values" (Golden Oldies)

2012-01-03T12:00:00.001Z

My mini-series of hints and tips from past decades would not be complete without a contribution from the SAS-L mailing list - the 25 years old list server that continues to provide immense value to SAS practitioners worldwide. Joined by the comp.soft-sys.sas newsgroup in 1993, these two services have worked hand-in-hand to answer questions and share SAS knowledge.

Matthew Healy's 1996 contribution to SAS-L, entitled "Coding Missing Values", offers timeless advice for data model design.

NOTE: Analytics 2012 Conference Series, Germany, 14-15 June

2012-01-03T12:00:00.000Z

The turn of a New Year is a good time to be thinking about your training plans for the year. Training can take several forms, it doesn't have to be limited to classroom sessions. Think more broadly. Are there any SAS-related books, or IT-related books that you would benefit from reading?

And be sure to include conferences in your short-list of training activities. SAS Global Forum 2012 should be high on your list. If you're into analytics then you should also consider the Analytics Conference Series. The next event is the European event in Cologne, Germany, between 14th and 15th June. For statisticians, analysts, forecasters, data miners and their managers, this is likely to be the premier analytics event for SAS practitioners in Europe this year. Book before the end of February to get an early bird discount. And, before you book, check-out the pre-conference training offers for further value-for-money.

NOTE: Booked for SAS Global Forum 2012

2012-01-03T11:30:00.000Z

I've just booked my trip to SAS Global Forum (SGF) 2012. Sadly my kids go back to school on the very day that SGF starts - Monday 23rd April - so they won't be coming with me to Walt Disney World in Florida.

I don't recall booking so many different travel components when I travelled with my wife to my first SUGI/SGF in New York for SUGI 18 in 1993. We arrived a few days early, toured the length and breadth of Manhattan, and had a great time. However, by the time the conference started I was glad of the opportunity to sit down.

This year I booked flight and accommodation as a "holiday" package with Virgin Holidays (including free return transfer from airport to hotel). I also booked:

Car parking for my car to be left in the UK
Lounges with wi-fi so I can make best use of my time whilst hanging around the airports waiting for flights
An application for entry to the US through the Electronic System for Travel Authorisation (ESTA) - thankfully, last year's application was still valid
Travel insurance
Conference registration
Did I forget anything??

On reflection, our 1993 trip was booked by a travel agent, and my 2012 trip was done by me on the internet. The travel agent handled all of the individual bits and pieces of the trip. I haven't used a travel agent for years. Maybe I should give it a try next year.

109 days to departure!

NOTE: The Hits of 2011

2011-12-31T12:00:00.000Z

To join all of the others on the internet, here's an "end of year" list... Blogger tells me that the most popular posts on the NOTE: blog this year were:

1. NOTE: SAS Global Forum 2011 - Update #3, 1,069 page views
2. NOTE: SAS Global Forum 2011 - Update #1, 929 page views
3. NOTE: SAS Global Forum 2011 - Update #2, 826 page views
4. NOTE: SAS Global Forum 2011 - Back-Ups, and The Last Word, 770 page views
5. NOTE: SAS Professionals Convention 2011 - Registration is Open, 753 page views

It's interesting to see that all of the posts relate to conferences - SGF in particular. I guess that's a reflection of our shared interest in the latest SAS news. I have every intention of being at SGF 2012 in Florida, USA between April 22 -25. And I'll endeavour to post all the latest news as soon as I find it.

Thanks for your continued support of NOTE:. Here's hoping we all have a healthy and happy New Year.

NOTE: Decoding the _TYPE_ Variable (Golden Oldies)

2011-12-27T12:00:00.000Z

Our mini-series of hints and tips from past decades continues with 1999...

Appearing in the same issue of The Missing Semicolon (TMS) was i) an advertisement for Year 2000 Support (do you remember "Y2K"?), and ii) an article entitled "Decoding the _TYPE_ Variable (in PROC SUMMARY and PROC MEANS)" on page 5.

The issue in question was June, 1999: Volume 2, Number 2. The Missing Semicolon, published by Systems Seminar Consultants, has been informing SAS practitioners since June 1998 (just a few months after the first edition of VIEWS News was put to print and posted to its subscribers). It continues to this day and offers free subscriptions to all SAS practitioners.

Contemporary users of PROC MEANS and SUMMARY have the benefit of the CHARTYPE option, but the principles of understanding the values of the numeric _TYPE_ variable or the 1's and 0's of the character _TYPE_ are the same.

NOTE: 180 Terrific Ideas for Using and Learning the SAS System (Golden Oldies)

2011-12-20T12:00:00.000Z

Continuing my mini-series of hints and tips from previous decades, here's a paper from my first SAS Users Group International (SUGI) conference. The 18th SUGI (predecessor to today's SAS Global Forum (SGF)) was held in 1993 in New York. Helen and Ginger Carey's paper was boldly entitled "180 Terrific Ideas for Using and Learning the SAS System", and it didn't disappoint. Whilst not all of their ideas are useful today, a significant number of them are. Note in particular their ideas regarding a) work habits, b) testing and debugging, and c) programming style.

NOTE: BY Variables in Titles (Golden Oldies)

2011-12-15T12:00:00.001Z

Whilst the capabilities of SAS software increase markedly from release to release, the core capabilities remain the same. And that means that many hints and tips from years gone by are still valid today. Starting today I'm running a mini-series of tips from past decades, starting with an article by myself in the first ever issue of VIEWS News (winter 1998).

VIEWS News was a printed and posted newsletter that was published quarterly by VIEWS - the UK's independent user group for SAS practitioners.

BY Variables in Titles

You can insert labels and/or values of BY variables into titles. For example, suppose you have a data set sorted by the variable TRTSEQ, with label "Group" and formatted values "Placebo", "Wonder Drug", etc.

options nobyline;

proc print;
  title "List for #byvar1 - #byval1";
  by trtseq;
  var var1-var10;
run;

For each BY group, you'll get the titles:

List for Group - Placebo
List for Group - Wonder Drug
etc.

The NOBYLINE option suppresses the normal printing of the BY lines, so you don't see "Group=Placebo." You can turn it back on with OPTIONS BYLINE.

Testing - Peer Reviews

2011-12-13T12:00:00.001Z

In my recent series on testing I concentrated on dynamic testing, i.e. executing some or all of your code to see if it works. The other type of testing is static testing. This was featured briefly in my "SAS Software Development With The V-Model" paper at this year's SAS Global Forum ( SGF).

The principle form of static testing is peer review. Like all tests and reviews, you have to have something to test or review against. For testing you will have a set of requirements or a set of specifications (ref: the V-Model). For peer review, a fellow programmer will take a look at your code and offer opinion on whether it meets the team’s coding guidelines and other standards (and whether it gives the impression of meeting the unit specification). Thus, to perform an effective peer review, you need some documented standards against which the review can be set.

I wrote a NOTE: post on peer review back in 2009, so I won't repeat those thoughts here; however, I did see a good post from Tricia on the BI Notes blog back on October. Tricia used the post to run through a number of potential pitfalls for those who are new to the process. In the article, Tricia offered valuable tips on how to keep the review focused & at the right level, and how to be properly prepared.

Done properly, peer reviews offer increased code quality, reduced cost of rework, increased amounts of shared knowledge of your applications, and increased sharing of code and design good practice.

NOTE: Programming Languages Can Be Copied...

2011-12-13T11:30:00.000Z

... so says Yves Bot, advocate general at the Court of Justice of the European Union (CJEU). Mr Bot's guidance was issued in response to the July 2010 High Court case between SAS and World Programming Limited (WPL). The CJEU still needs to make a full judgement before handing things back to the High Court, but WPL will doubtless be pleased that Mr Bot appears to have sided with them (along with a US court in March 2011).

However, whilst the language itself cannot be protected, the implementation of the language *is* protected. So, the code that SAS's staff in Cary (and around the world) have written in order to implement the language cannot be copied. Anybody who wishes to produce software that can interpret the SAS language must write that interpreter all by themselves. As a result, you might expect the bigger company to be able to invest more time and effort into optimising the speed and efficiency of their product.

So, before you rush out hoping to find a cheaper, better solution, be sure to do your full due diligence on all of the candidates. Check that all elements of the language are implemented; be sure to perform some performance and reliability testing; and probe the support provisions of your potential suppliers. These things are less easy to duplicate. Caveat emptor.

NOTE: Hash Tables, An Introduction

2011-12-08T12:00:00.001Z

In my recent series of articles on testing I used the hash table to provide a means to write/update a table from within a DATA step without demanding changes to the DATA statement, etc. I've had some very kind feedback on the series, plus some requests to explain what the hash table does and how it works. So here goes...

The hash table was introduced to the DATA step as a means of performing fast, in-memory lookups. The fact that the hash table is a "component object" means that there's a steeper than usual learning curve for this feature. The SAS Language Reference: Concepts manual provides an introductory section on the hash table, but for many DATA step coders the hash table remains unused. A better route to getting started with hash tables is the "Getting Started with the DATA Step Hash Object" paper in the DATA Step sub-section of R&D's SAS/BASE section of the SAS Support web site (follow the link to Component Objects and Dot Notation).

In a nutshell, the hash table is a lookup table that's stored (temporarily) in memory and allows you to search for values within it and thereby get associated values returned. Let's introduce ourselves to the hash table by taking a two step approach: firstly we'll create the hash table, secondly we'll use it for lookups against each of the rows in our input table. Our DATA Step will look like this:

data result;
  set input;
  if _n_ eq 1 then
  do; /* It's first time through, so let's create hash table */

    <create the hash table>
  end;
  /* For each row in input table, do a lookup in the hash table */

  <do a lookup>
run;

Let's make ourselves some test data and assume it contains the sales from our small car sales operation last week:

data SalesThisWeek;
  length make $13 model $40;
  infile cards dsd;
  input make model;
  cards;
Jaguar,XJR 4dr
Land Rover,Discovery SE
Jaguar,XKR convertible 2dr
;
run;

We have a price list for all of the cars we sell; it's in sashelp.cars and contains variables named make, model and invoice. Frustratingly, the MODEL column contains leading blanks, so we use a quick DATA Step to get rid of them, thereby creating work.cars.

data work.cars; set sashelp.cars; model=left(model); run;

We want to load the price list into a hash table, then lookup each of our sold cars to find its invoice value. Here's the code to <create the hash table>:

  DECLARE HASH notehash (DATASET:'work.cars');
  notehash.DEFINEKEY('make','model');
  notehash.DEFINEDATA('invoice');
  notehash.DEFINEDONE();

Woh! That code looks like no other SAS code we've ever seen!! That's because the hash table is a "component object" and the syntax for dealing with components objects differs from mainstream DATA Step syntax. It's called "dot notation". It quickly makes sense once you get over the fact that it's different.

The first line tells SAS that we want to create a new hash table. Hash tables only exist temporarily in memory for the duration of the DATA Step. We use the DECLARE statement to begin to create a new component object; the first parameter (HASH) says what kind of component object we want to create; the second parameter (notehash) is an arbitrary name that we have chosen for the hash table; within the brackets we have told SAS that we're going to use some of the columns of the work.cars table as our lookup table.

The following two lines tell SAS a bit more about how we'd like to load and use the hash table; the fourth line (with DEFINEDONE) tells SAS we've nothing more to tell it about the hash table.

When we use dot notation we type i) the name of a component object, ii) an action we want to perform on the object, and optionally iii) parameters for the action. Parts (i) and (ii) are separated by dots, and the parameters (iii) are enclosed in brackets.

When we create a hash table, we have to declare it, then we have to specify a) the key column(s), i.e. the column(s) that we'll use to find something in the hash table, and b) the data column(s), i.e. the column(s) whose values will be returned once the key values are found in the hash table. In our case, MAKE and MODEL are our key columns, and INVOICE is our data column.

After specifying our key and data columns (with the DEFINEKEY and DEFINEDATA actions) we tell SAS that we're done by performing the DEFINEDONE action on the hash table.

The dot notation is different to what we're used to, but it's not too tricky to get your head around.

Now that we've created our hash table in memory, for use during the DATA Step, all we need to do now is use it. We lookup things in the table by performing the FIND action on the hash table. If SAS finds the key value(s) in the hash table, it will automatically put the associated data value(s) into the data variable(s) in the DATA Step. So, in our case, we need a variable in the DATA Step named INVOICE. If we don't create that variable prior to using the hash table we'll get an error.

When we do a FIND, we're given a return code value that tells us whether SAS found the key(s) in the hash table. A return code value of zero tells us that all is well and the value was found; any other value tells us that SAS did not find the value. So, our code to <do a lookup> will look like this:

  length invoice 8;
  rc = notehash.FIND();
  if rc ne 0 then 
    put "Oh dear, we sold something we can't identify";

Notice that there's no direct reference to INVOICE when we do the find. The fact that FIND will put a value into INVOICE is implicit from our preceding DEFINEDATA.

Put all together, our code looks like this:

/* Create our test data */
data SalesThisWeek;
  length make $13 model $40;
  infile cards dsd;
  input make model;
  put make $quote22. model $quote50.;
cards;
Jaguar,XJR 4dr
Land Rover,Discovery SE
Jaguar,XKR convertible 2dr
;
run;

/* Strip leading blanks from MODEL */
data work.cars; set sashelp.cars; model=left(model); run;

/* Add invoice values to sales by using lookup */
data result;
  set SalesThisWeek;
  keep make model invoice;
  if _n_ eq 1 then
  do; /* It's first time through, so let's create hash table */
    DECLARE HASH notehash (dataset:'work.cars');
notehash.DEFINEKEY('make','model');
notehash.DEFINEDATA('invoice');
notehash.DEFINEDONE();
  end;
  /* For each row in input table, do a lookup in the hash table */
  length invoice 8;
  rc = notehash.FIND();
  if rc ne 0 then
    put "Oh dear, we sold something we can't identify";
run;

Once you've got the basic hang of hash tables, the two best sources of reference information are:

a) The hash table tip sheet, available from R&D's SAS/BASE section of the SAS Support web site (see the link to the tip sheet over on the right hand side of the page)

b) Component Objects: Reference in the SAS Programmer's Bookshelf

There are many ways to perform lookups in SAS. Some examples would be i) formats, ii) the KEY= parameter of the SET statement, iii) table joins. The hash table is another option which can offer advantages in many cases. Have fun...

NOTE: Testing Macros - Parameters Revisited

2011-12-06T12:00:00.000Z

As my planned series on testing drew to a close last week, I got an email from Quentin McMullen with some very kind words about the NOTE: blog, but also some very erudite comments about my choice of parameters for my testing macros. Rather than paraphrase Quentin's comments, I decided to publish his email verbatim (with his permission). Here's the heart of Quentin's email, followed by a few brief comments from me.

Just a quick thought:

I have a similar macro to %assert_condition, but it only has one (main) parameter, &CONDITION, instead of three; &LEFT &OPERATOR &RIGHT. So it looks like:

%macro assert_condition(condition,tag=); if &CONDITION then put "TESTING: &sysmacroname: TAG=&tag, OUTCOME=PASS"; else put "TESTING: &sysmacroname: TAG=&tag, OUTCOME=FAIL"; %mend assert_condition;

So you can call it like:

%assert_condition(incount eq outcount)
or
%assert_condition (age > 0)
or
%assert_condition ( (incount=outcount) )

I tend to like the one parameter approach.

The only tricky part is if you have an equals sign in the condition, you have to put parentheses around the condition so the macro processor does not interpret the left side as a keyword parameter. The nifty thing is that the parentheses also mask any commas,e.g.:

%assert_condition(gender IN ("M","F") )

Do you see benefits to the 3 parameter approach vs 1 parameter?

Yes, Quentin, I do very much see the benefits of your approach. Your example, using the IN operator, is particularly well chosen. Rest assured I'll be adapting the McMullen approach in future. Thanks for the comments. Keep reading NOTE:!

NOTE: Testing (Presenting the Results)

2011-12-01T12:00:00.001Z

The preceding two articles in this series on testing presented a simple, generic macro for testing and recording test results. All that remains now is for us to tidy-up some loose ends.

Firstly, the macro assumes data set work.results already exists. And it also assumes that the data set contains appropriate variables named Tag and Result. We can quickly arrange that by being sure to include a call to the following macro in our testing code:

%macro assert_init(resultdata=work.results);
data &resultdata;
length Tag $32 Result $4;
stop;
run;
%mend assert_init;

Finally, we want to present our results. We can do this easily with a simple call to PROC REPORT:

%macro assert_term(resultdata=work.results);
title "Test Results";
proc report data=&resultdata;
columns tag result;
define tag / order;
run;
%mend assert_term;

Equipped thus, we can focus on our testing code, not the mechanics of collating and presenting results. For example, let's imagine we have some new code to test; the purpose of the code is to read a raw file (a.txt), create some computed columns, and write-out a SAS data set (perm.a). One of our tests is to check that the number of rows in the raw file matches the number of rows in the SAS data set. Here's our code to test this functionality:

%assert_init;

%include "code_to_be_tested.sas";

%assert_EqualRowCount(infile=a.txt,outdata=perm.a,tag=T01-1);

%assert_term;

We can make the results a tad more visual by colourising the pass/fail values:

%macro assert_term(resultdata=work.results);

  proc format;

    value $bkres 'PASS'='Lime'

                 'FAIL'='Red';

  run;



  title "Test Results";

  proc report data=&resultdata;

    columns tag result;

    define tag / order;

  define result / style(column)={background=$bkres.};

  run;

%mend assert_term;

This assumes you're using SAS Enterprise Guide. If not, you'll need to add some appropriate ODS statements around the PROC REPORT.

The downside of the macros as they stand at this point is that the results data set gets recreated every time we run the code. Maybe we don't want that because we want to collate test results from a number of separate bits of test code. So, finally, we can make the creation of the results data set conditional, i.e. if it doesn't exist we'll create, if it already exists then we'll leave it alone:

%macro assert_init(resultdata=work.results);

  %if not %sysfunc(exist(&resultdata)) %then

  %do;

    data &resultdata;

      length Tag $32 Result $4;

      stop;

    run;

  %end;

%mend assert_init;

NOTE: Advent Calendar 2011

2011-12-01T06:00:00.000Z

Today is 1st December, and that can mean only one thing: time to turn to the traditional SAS Professionals advent calendar! There will be prizes every day throughout December, so make sure you visit http://www.sasprofessionals.net/ every day.

And if you're not already a member, sign-up, check-out the Expert Channel, the forums, and benefit from the ability to network with fellow SAS practitioners online! Attend next year's SAS Professionals Convention in Marlow (July 10 to 12) and learn from papers delivered by a mixture of SAS staff and customers.

NOTE: Testing (Collating the Results)

2011-11-29T12:00:00.001Z

We began this series of posts on testing with an introduction to testing disciplines, and I followed that with a description of how we could quickly create some small macros that would allow us to automate some of our testing and present the results in a consistent fashion in the SAS log. However, putting the results into a SAS data set rather than the log was the next step to improve our efforts.

We can use the tag as a unique key for the test results data set so that the data set has the most recent test results for each and every test. We can re-run individual tests and have an individual rows in the results data set updated.

To give us the greatest flexibility to add more macros to our test suite, we don't want the process of writing results to a data set to interfere with activities that are external to the macro. So, using a SET statement, for example, would require the data set to be named in the DATA statement. This seems a good opportunity to use the OUTPUT method for a hash table. We can load the results data set into the hash table, use the TAG as the key for the table, and add/update a row with the result before outputting the hash table as the updated results data set. Here's the code:

%macro assert_condition(left,operator,right,tag=

                       ,resultdata=work.results);

  /* Load results into hash table */

  length Tag $32 Result $4;

  declare hash hrslt(dataset:"&resultdata");

  rc = hrslt.defineKey('TAG');

  rc = hrslt.defineData('TAG','RESULT');

  rc = hrslt.defineDone();

  /* Update the hash table */

  tag = "&tag";

  if &left &operator &right then

    result="PASS";

  else

    result="FAIL";

  rc=hrslt.replace(); /* Add/update */

  /* Write back the results data set */

  rc = hrslt.output(dataset:"&resultdata");

  rc = hrslt.delete();

%mend assert_condition;

By adding the maintenance of the results data set to our basic assert macro, the functionality gets inherited by any higher-level macro (such as yesterday's %assert_EqualRowCount).

Clearly, the new macro won't work if the results data set doesn't already exist, and we'd like to present the results in a format better than a plain data set. We'll cover that in the next post.