I have a data set of sales data by day. Unfortunately the names of the columns represent the dates. In order to work with the data, I need to transform the data set so each day represents an observation.

The data set looks something like this:

store _010_05_01 _010_05_02 _010_05_03 ....
1     8          5          6
2     6          9          3
3     7          9          8
....

As you can see, I have a store variable and several variables that are named by the date and contain the number of sales for that day. I need it to look like this:

store date   sales
1     5/1/10 8
1     5/2/10 5
1     5/3/10 6
2     5/1/10 6
2     5/2/10 9
2     5/3/10 3
....

Unfortunately this is a pretty common problem when you receive data from vendors who aren't sure how you are going to work with it. Luckily the transformation is pretty easy:

1) data byDay;
2)   set myData;
3)   array t[*] $ _010: ;
4)   do i = 1 to dim(t);
5)     sales = t[i];
6)     date  = vName( t[i] );
7)     output;
8)   end;
9) run;

This little data step creates an array to hold our date variables. (3)We use a little syntax sugar to keep from having to type out all the variable names _010:. The colon tells the SAS compiler to list out all the variables that have the _010 prefix. (4)Then we loop through all the elements in our t[] array. We use the vName() function to get the name of the variable that t[] is referring to (6). And finally we output once for each iteration of our loop (7).

Obviously if your date columns aren't named uniformly (_010:) then things won't work as nicely. And you would likely want to add a KEEP statement to just keep the variables you are creating along with any others you might be interested in.

If you made it this far, you may have noticed I am not quite finished with my transformation. Line (6) assigns the value of date to something like '_010_05_01'. Not quite what we wanted. We need to turn that into a SAS date. I need to substr() the first two characters off to remove them (_0) and then input() the result using the appropriate informat. So line (6) should really be:

6) date = input( substr( vName( t[i] ), 3), yymmdd8. );

A little more complicated, but hopefully still understandable.

Hopefully you find this useful! Comment with any comments/observations.

Some Fun with SAS and Perl Regular Expressions

This post assumes you have a little understanding of how regular expressions work and specifically how SAS implements regular expressions. I recently did something like this and thought it would be good to share. Suppose you have a program that searches through a big text field for a specific word. That's pretty easy to code and you can even get away with just using a simple indexW() function. The problem is when you look at the text field on your report, your eyes glaze over as you scan for the word to make sure you are capturing the correct output. If only there was some easy way to make the word stand out from its neighbors.

I used the prxchange() function to search for a pattern and then replace it with another pattern. In this case, I am outputting HTML so I can wrap my search word in <b> tags. First I will give a little example code, then I will break down what the code is doing and finally show some easy improvements. For the sake of clarity and brevity, I am only showing the code that highlights the search word. I am not showing the code that subsets the data based on the search term.

Example 1:

data _null_;
  input text $80.;
  put "The text before matching " text=  ;
  text = prxchange('s/(battery)/<b>$1<\/b>/', -1, text);
  put "The text after matching " text= //;
datalines;
This battery is dead.
Batteries are in the box.
;

Output in the log:
The text before matching text=This battery is dead.
The text after matching text=This <b>battery</b> is dead.


The text before matching text=Batteries are in the box.
The text after matching text=Batteries are in the box.

Looking at the code above, you can see that the only interesting thing happening is the prxchange() function. The prxchange function takes a regular expression as its first argument. The regular expression uses a substitution syntax with a generic look of

s/(something to look for)/numbered capture buffers/.

So in my example above, the word (or pattern really) I am looking for is battery. I put () around it to specify that it's the first capture buffer: $1. Then I wrap $1 with bold tags. You can see I had to escape the / in the closing tag because it is a special regular expression character. So my regular expression is:

s/(battery)/<b>$1<\/b>/

and reads as: look for the pattern 'battery', store it in $1 and substitute it with <b>$1</b>.

The second parameter to the prxchange() function is -1 and just tells the function to keep searching the source, finding and replacing every occurrence till you get to the end of source. The third parameter 'text' just tells the function what text source to search.

Make sense?

Now there are a couple things that can easily be added to the regular expression to make the code a little more robust and efficient. First of all, the regular expression is recompiled on every loop of the data step. In our case, we don't need that so we can add the /o option to the end of the regular expression to tell it to just compile it once:

s/(battery)/<b>$1<\/b>/o

Also, our regular expression is caSe SensiTive. We can tell it to ignore case by adding the ignore case option (/i) to the end of the regular expression:

s/(battery)/<b>$1<\/b>/oi

Now it will match battery, Battery, BATTERY, etc.

But wait! We also want to match Batteries. What to do? We could shorten our regular expression to:

s/(batter)/<b>$1<\/b>/oi

But that would match batter and batter is a liquid mixture, usually based on one or more flours combined with liquids such as water, milk or beer. That's definetly not what we are looking for. We want to search for batter followed by at least one or more [a-z] characters:

s/(batter[a-z]+)/<b>$1<\/b>/oi


Now our example code looks like:

data _null_;
  input text $80.;
  put "The text before matching " text=  ;
  text = prxchange('s/(batter[a-z]+)/<b>$1<\/b>/oi', -1, text);
  put "The text after matching " text= //;
datalines;
This battery is dead.
Batteries are in the box.
Do not eat the cookie batter before it is cooked.
;

Output in the log:
The text before matching text=This battery is dead.
The text after matching text=This <b>battery</b> is dead.


The text before matching text=Batteries are in the box.
The text after matching text=<b>Batteries</b> are in the box.


The text before matching text=Do not eat the cookie batter before it is cooked.
The text after matching text=Do not eat the cookie batter before it is cooked.

And finally, you sharp SAS coders probably don't want to hardcode the search term. More likely it would be stored in a variable and then you could construct the regular expression like you would any other text variable:

mySearch = 'batter';
rx = "s/(" ||
      mySearch ||
      "[a-z]+)/<b>$1<\/b>/oi";

Or something like that. Also, you can search for more than one thing. Just enclose each pattern in () and refer to them as $1, $2, etc. Play around with it. Have fun. Thanks for reading!

The Cats() Function

One of my favorite new functions is the cats() function available in SAS v9. It is a compress() like function in that it removes leading and
trailing blanks, but it also concatenates the results. CATS() stands for concatenate and strip. Basically the cats() function takes this
type of assignment statement:

key = trim(left(firstName)) || trim(left(lastName)) || 
      trim(left(phone)) || trim(left(zip)) ;

and changes it to this:

key = cats(firstName, lastName, phone, zip);

Generally, the cats() function will return a value with a length of 32767 in the data step. So as always, it's a good idea to use
a length statement on the variable you are assigning to. In this example I might use something like:

length key $200;

The cats() function belongs to a family of cat() functions each doing it's own version of concatenate: cat(), cats(), catt(), catx().

Coming up in version 10, the dog() family of functions! :)

The =: Operator

Most people who are familiar with programming SAS are familiar with the equal colon operator ( =: ). There are a couple different colon operators in SAS, but in this post I am only talking about the comparison operator. The equal colon operator works much like the substr() function. It is used to compare substrings for equality.

Here is a quick little example:

data _null_;
  x = 'abcdefg';
  if x =: 'abc'
   then put 'The substrings match.';
run;

As you can see if you run the little data step above the substring 'abc' matches in 'abcdefg'. A better way to think of it is that it "starts with" the substring. The same could also be accomplished by this statement:

  if substr(x,1,3) = 'abc'
   then put 'The substrings match.';

There is one big difference between the =: operator and using substr(). With the substr() function you tell it exactly how many characters to look for the substring. In the example above it was three. For the =: operator it has to figure out how many characters to search. It does this by (somewhat counter-intuitively) looking at _both_ sides of the operator to find the shortest length. Here is an example:

data _null_;
  x = 'abcdefg';
  if x =: 'abc'
   then put 'The substrings match.';
run;

If you run the above data step you will see that they match. It looks a little funny because most of us assume that SAS is looking for 'abcdefg' within 'abc', but that's not really what's happening. SAS uses the shortest string to decide what to look for, no matter which side of the equals sign it is on.

Oh yeah, the =: operator also works in list context such as:

  if x in: ('abc', 'xyz', 'def');

That's it for today's post. Happy coding!

Cool V9 SAS Compress() Function Tricks

In SAS Version 9 there is a new option available for the compress() function. This new third option allows you to use "modifiers" to modify what compress() is doing. There are too many modifiers to list here, but they are worth looking up in the SAS V9 documentation.

Here is an example of a snippet of code I recently created to get rid of "non-printable" hex chars. This is a pretty standard data cleaning routine and is quite useful when some bad hex chars can creep into your text data. Instead of hunting and pecking for the funky hex chars you can just tell compress() to keep only the
"printable characters".

data _null_;
  x = 'A '  '16'x  'bad'  '18'x  ' sequence, with puncuation?';

  put x=;
  x = compress(x,,"kw");  * k is for keep, w is for "write-able";
  put x=;
run;

Notice in the compress() function there is no second parameter, and there is a new third parameter specified: "kw".
K is for keep, and W is for write-able. So this reads as keep only
a-zA-Zwhitespace0-9punctuation.

Pretty nice, eh?

As I said, there is a bunch of other modifiers available so take a look at the documentation. And happy coding!

Also, there are more examples of using the compress function with the optional third argument at my i-Doc site: http://idoc.pelicanprogramming.com/functions/COMPRESS.html