Thanks LabSug!

Thank you Los Angeles Basin SAS User Group for listening to my talk on SAS MACRO: Beyond The Basics.

The room provided by RAND was excellent. Everything was well organized and the day went without a hitch. The audience was very engaged and had great questions (definitely not the sleepy group I was expecting right after lunch!).

Overall I had a great time, and look forward to getting the chance to speak again!

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.

How To Get What You Want Out of a Data Step Merge

This Fall my daughter will be going to kindergarden. So like all other hyper-attentive parents we have started introducing her to the concept of homework. The other night I got out her crayons and introduced her to set theory. After about an hour I finally got her to draw her Venn diagrams with the the corresponding SAS data step code for a merge. I was so proud I decided to post it here.

In her drawing data set A is red and B is blue. The shaded area is what's kept.
Disregard the part where she drew herself building a sand castle on the beach. It has nothing to do with Venn diagrams, or SAS data step merging code.


What? You don't believe my five year old daughter drew it? :)

Just a quick refresher: The A and B business refers to the automatic variables that are created when you use the IN= data set option. Essentially the variable A will be "true" whenever that data set contributes an observation to the merge (or join). Also, I don't know why everyone uses A and B-- you can set these variables to anything (left|right, paid|due, etc); but I've always seen A and B so I will stick with convention.

data merged;
  merge someData(in=A) otherData(in=B);
  by someKey;
  if a and (not b); * just keep the observations in A that do not match anything to B;
run;

Sorting Pitfalls

SAS Proc sort is probably one of the easiest procs to use. And sorting in other programs/software is generally pretty straightforward. However, there are a few things I always remind myself when sorting.

When you use proc sort you don't know who is doing the sorting. Does this surprise you? Don't worry-- I don't know who's doing the sorting either!

It could be a low level routine called by SAS (a .dll in Windows). On the mainframe it could be an entirely different proc (syncsort). It could be a specialized routine written to take advantage of specific hardware efficiencies. It could be a different data base (SQL pass thru). You get the idea.

This tells me not to assume anything about the sort.

Like what kind of assumptions? Generally anything that's not specified in the sort is open to interpretation by the procedure/dll/db/whatever.

A specific example:
Let's say you have a data set with 3 variables (ID, NAME, Q_DATE) and 5
observations:

1 stephen 01apr2010
2 brian   01apr2010
1 stephen 05apr2010
1 stephen 20apr2010
2 brian   25apr2010

First of all notice this data set is already sorted by Q_DATE.

I am going to sort by ID.
Now my data set looks like this:

1 stephen 01apr2010
1 stephen 05apr2010
1 stephen 20apr2010
2 brian   01apr2010
2 brian   25apr2010

Later on in my code I want to remove the duplicates and get the first occurrence of Q_DATE.

data myData;
 set myData;
 by id;
 if first.id;
run;

What if the sort doesn't maintain the order of observations within the by group? In my example, the Q_DATE is still in sorted order, but that is not guaranteed to always be the case. As you can see, this code has the potential for getting me into some very difficult debugging that may not even show up until years later.

This also applies to the NODUPKEY option. It is not guaranteed that nodupkey will keep the first observation from a group of duplicates.

Unfortunately, sorting of any kind is one of the most expensive low-level routines in computer science. So when you review code looking for efficiency gains, there is a strong temptation to remove as many sorts as possible. In the scenario above, the code is pretty effecient because we are making use of the fact that the data set is already sorted by Q_DATE. However, we need to tell the sort routine to maintain the integrity of the observations within the by group.
To do this we can use the EQUALS option:

proc sort data = myData equals;
 by id;
run;

Personally, I would only recommend such chicanery for absolutely, positively, critically important efficient code. I guarantee you'll sleep better at night if you make the code more bulletproof (who's to say your data set will always come in sorted by Q_DATE?) and readable by including it in your by group. You could even leave a handy comment for the next programmer that comes along and feels the need to over-optimize the code:

proc sort data = myData;
 by id q_date;  ** yes yes i know the ds is supposed to already be sorted
by q_date, but there is no guarantee the sort will maintain the order;
run;

Happy coding!

Cloud App

Oh man it has been a long time since I have updated this blog. So what's new?

I have a new son!
The band I am playing in recently played a gig at the Whisky A Go Go in Hollywood. We're playing at The Cat Club in Hollywood this Thur come one come all, promotion, promotion, promotion and all that :)

So between playing bass and changing countless diapers I have found my free time has been severely limited. Right now I am into anything that lets me get things done simply and quickly.

I recently found a new application for storing and sharing files online called CloudApp. I'll let their site explain themselves, but basically it is a simple web front end that lets you quickly upload files to Amazon's S3 cloud storage. And it's free!

Now I can record my band's live shows, practices, etc and share them with all the band members online in seconds. It even automatically creates a shortened URL. And if you use Mac, there is a desktop app that makes uploading and sharing files crazy simple.

I quickly skimmed their TOS and I didn't see anything in there about encryption, privacy, etc so I wouldn't use it to pass data sets that contain social security numbers or any other sensitive information. But it is still dead handy for all the other large data sets you've been working on...

Finding the Max Value In An Array

The max() function makes it easy to find the maximum value in a SAS array.

Given an array like:

array x[*] x1-x10;

maxValue = max(of x[*]);

Pretty slick, eh? Remember, it doesn't return the position of the max element, just the max value.

This can be pretty useful if you want to find out if at least one element of an array has a value. Like if you have an array of answers and you want to find out if there is at least one answer in the array.

hasAnAnswer = max(of answers[*]);

or in logic:

if max(of answers[*])
 then do;
        * yes, they have answered at least one question;
      end;

*NOTE: I have decided to keep this SAS programming blog SAS focused and move my Ruby on Rails writing to a Rails oriented blog. I am a guest contributor at www.HABTMProjects.com. If you were interested in following along with my RoR projects then please hop on over there and sign-up.*

Design

You are at the gym running on a treadmill listening to your iPhone through the earbuds. You're having one of those good, easy runs. Maybe you're listening to an interesting podcast like WNYC's Radio Lab, or some good running music. You're focused. You've got a nice rhythm going and your body feels relaxed. You push the pace a little more than normal. Sweat is dripping off you. Endorphines begin flooding your system. You have declared war on holiday snacks and Christmas dinners. And now on this treadmill you are winning the war. Suddenly your iPhone stops playing. You are pulled out of your running reverie and glance down at your now-beeping phone. Voice control has been activated. You fumble for the controls. You stumble on the treadmill. The pace is too fast for multi-tasking. You slow the pace down on the treadmill and flip the phone back to iPod. The phone takes on a life of it's own, pausing and clicking and going back to voice control again. You try to run without, but it's just not the same. Your running rhythm won't return. Everything hurts. You're exhausted. You hit stop on the treadmill and step off defeated.

Apparently there is a design flaw on the iPhone earbuds where moisture causes the little clicker to send random clicker signals to the iPhone.

A design flaw on an Apple product! And Apple is good at design.

So now that I've gotten my preliminary decisions out of the way in the previous post, I can start working on designing my app/site. Luckily Rails encourages agile development which is all about procrastinating on difficult design decisions. Rather than try to design your entire project at the beginning, you take a very minimalist approach and assume the details will reveal themselves as you go.

My minimalist design will consist of two pieces: a short paragraph describing the site, and a piece of paper with drawings of the layout of the pages.

The description of the site:
The site will display SAS jobs. Job seekers will be able to search the jobs by zip code. Recruiters will be able to post jobs. The jobs will expire after a certain amount of time. There will also be some admin pages to control Recruiters and the Jobs they post.

Now I'm taking out a piece of paper and drawing some rough sketches of the main pages.... Done.

From the description I see there are three types of users with three main actions: job seekers find, recruiters post and admins admin. I also see that there are only two models that I need in my data base: recruiters and job postings.

That's pretty much it for the design. Now I can begin coding.

Some Preliminaries

As I mentioned in an earlier post, I am going to build a new site dedicated to SAS jobs. Before I actually start coding the site, there are some preliminary decisions that need to be made. I have already decided to use Ruby on Rails for the framework. I have just started learning it and so far I like what I have seen. I have also decided on my host already. Although nearly every host says they support hosting RoR, most don'tdo a very good job. I have test driven heroku.com with another small site I built and they do a great job. They will essentially host everything for free while you mock it up and you can buy more resources if you ever need to. They are also an exclusive RoR host so they have tailored the develop, test, deploy environment to Rails' unique agile nature. Choosing heroku.com as my provider also forces me to use git as my version control. Heroku seamlessly uses git as part of the workflow and it works very very nicely.

Now that I have gotten the preliminary decisions out of the way I will be able to develop some simple use cases and models and start coding my app.

Oh, and I will be doing all of the development on my macbook running OSX with Xcode3 and using mySql as the development database.

Below are the books that I am using to help guide me along the learning path. I have all three books on my desk and I can say with confidence that all three are very good.

Books:

• Pragmatic Version Control Using Git (Pragmatic Starter Kit)


• Agile Web Development with Rails, Third Edition


• Programming Ruby 1.9: The Pragmatic Programmers' Guide (Facets of Ruby)

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!

Side Projects

A few years ago I created a site that lets users donate their SAS expertise by creating online documentation. Users could put in their own example code, explain potential pitfalls, share tips, etc. I coded it all by hand in Perl with MySQL, JavaScript and HTML. I even included some nifty AJAX for logging in, etc. And it had a trendy name too: iDoc. As in "I document" verb, or "Internet Documentation" noun. After a lot of programming and evenings with O'Reilly books, I felt that it was ready to be released to the world. I tentatively exposed the URL and wrote an introductory email to SAS-L. The response was....

virtual silence.

Ho Hum. Crickets chirping. Nothing. Well, there was one person who railed against my decision to not be cross-browser compatible. Specifically, the site worked well with IE, not so well with others. As all of you who have developed anything more complex than the most generic HTML page knows, cross-browser compatibility is a nightmare. To describe it as a pain in the ass is a disservice to donkeys. I digress...

Anyways, the _site_ was a failure. But the _project_ was a success in that it taught me a bunch of stuff that I was able to incorporate into my day-to-day programming. And the things I learned have dovetailed into other side projects.

Fast forward to today. Today I am starting another side project. It will be written in Ruby on Rails. I bought a Rails book two months ago and have written only one little site so far. But I like it. It's clean, it's fast to develop with and it's easy to learn. I don't even know Ruby. I'll be learning that along the way too. I'll try to share what I learn as I go. I'll tag the posts with something like "Side Project" to differentiate them from the normal SAS postings.

The side project I am starting today is a SAS specific job site. I know there are others out there and some good ones too, but I think there might be enough room for one more. And even if the site fails, the time will be well spent learning a new language and platform.

If you could, pop over to the comments and let me know if hearing about a side project written in Ruby on Rails is the least bit interesting to you. As much
as I like the idea of sharing what I learn, I don't want to fill a SAS programming blog with a bunch of posts about a topic nobody cares about. Thanks! -s

Keep and Rename Data Set Options

I always forget which gets applied first when using the keep= and rename= data set options on the same data set. So I thought I'd just put it here so I will remember:

Keep happens before the rename.

Keep happens before the rename.

Keep happens before the rename.

There. Now I won't forget.

A little test code to prove it:

data test;
  length x y z $5;
run;

data test;
  set test(keep=x y z rename=(x=x2));
run;

{ old=>'datasteps.blogspot.com' , new=>'www.sasCoders.com' }

A few months ago I got an email from my service provider vaguely stating they would no longer exist as a company and I better find an alternative. Yikes! I am still trying to sort everything out, but so far being forced to move has been a good thing. It has given me the impetus to focus, evaluate and expand my little web presence.

One of the tasks I put off for too long was to link this blog to its own unique URL. Apparently having its own URL gives your blog a feeling of gravitas and professionalism. Now I can finally cross that off my checklist. While the new URL is www.sasCoders.com, the old datasteps.blogspot.com will still continue to work. And the name of the blog will still stay the same. Eventually as I sort things out I will probably move it to its own subdomain (datasteps.sasCoders.com). Would you believe datasteps.com is already taken?


Dear Mark Fitzgerald, er, I mean CodePanther: I am assuming you probably registered a bunch of URLs based on existing blogspot.com blogs? In the hopes that I will eventually want that name and contact you to see how much you will sell it for? What a great strategy! Hope it works well for you. Oh wait! Maybe you specifically chose data steps because you wanted to be associated with this super successful blog? Woohoo! Rock on CodePanther!


Anyways, hopefully the switch is seamless and I won't lose any readers or google juice. :)

SAS Job Searching

Where do you search for SAS jobs? I have been lucky during the downturn and stayed employed, but I know there are a lot of others out there who haven't been as fortunate. When I did look for a job I generally used dice.com, but that
was mainly out of habit more than anything else. Do you have any specific sites that you use? Any tips to share with other SAS programmers looking for work?

Telecommuting

If you work in a large city there is a good chance you have to commute to work. It is also likely that your commute time is non-trivial. Here in Los Angeles it is not uncommon for people to spend over an hour in their car everyday. No fun!

If you search dice.com for sas jobs 1115 jobs are returned. If you tell dice to restrict to telecommuting jobs a whopping 0 are returned(!). So why isn't telecommuting more of an option? We have laptops, cell phones, secure VPN, high speed internet, Skype, etc. Why hasn't the distributed work force become the norm? As a SAS programmer, do you telecommute? If not, why not?

If you do telecommute, could you share your setup with us? What has worked for you and what hasn't? The more specific you can be the better. If I can get enough feedback I will put something together along the lines of A SAS Programmer Telecommuting/Home Office Best Practices.

Some questions I am thinking of:
Do you have a plan for backing up data? Is it local? There's a lot of really good online backup that is really cheap.
How do you protect your data? Encryption tools?
Do you use a revision control system to track your source code (Git, Subversion)?
How do you connect to servers? VPN, SSH?

Any other issues I am not thinking of?

SAS Orphaned Workspace

Sometimes SAS doesn't shut down correctly and you get stuck with orphaned workspace. These orphaned workspace directories should be cleaned out now and again. You can run this code in SAS to find out where your workspace is and then go to the directory and delete any old ones.

data _null_;
w = getoption('work');
put w=;
run;


On my windows machine this gets me something like:

w=C:\DOCUME~1\STEPHE~1\LOCALS~1\Temp\SAS Temporary Files\_TD3516


Now I can go to C:\DOCUME~1\STEPHE~1\LOCALS~1\Temp\SAS Temporary Files\ and delete all the old ones.

Note, this is straightforward on a single user machine like my windows laptop, but you have to be more careful in multi-user environments where you don't want to delete active workspaces that others are using.

For multi-user environments like unix there is a utility that SAS provides called cleanwork.

Gliffy

Occasionally I like to sketch out data extraction flows, database schemas, use cases, etc. I prefer pencil and paper to the industrial strength tools like visio. But sometimes I need to share my little pictures and mailing my scratch paper just won't cut it. I recently found a great free online site that provides a diagram editor that is easy to use and lets me share with people over the internet.

www.gliffy.com

So far I have only used the free account, but it has worked great.

Proc FCMP

A long time ago I figured out how to write functions for SAS using SAS/Toolkit. It was not a straightforward process and not very useful. In fact, someone recently asked me about it and I was unable to share much useful info. However, I did find proc FCMP which does look to be very useful-- if you're into writing your own functions, that is!

Here is some online info:
http://support.sas.com/rnd/base/datastep/user-written-functions.pdf
http://support.sas.com/documentation/onlinedoc/base/91/fcmp.pdf


If you have SAS 9.1 you can use it to write functions that can be used in some
procedures, but not within the data step. However, if you have SAS 9.2 you can use them in a data step. And it uses data step syntax so you don't have to know C!

Thanks San Diego

Thank you to everybody at the San Diego SAS User's Group. I had a great time meeting everyone and presenting.

The paper and code can be found on my site at:
http://www.pelicanprogramming.com/sgf08/sgf08.html


I will also put the power point up when I get a chance.

Thanks again for hosting me as a speaker! And thanks to Kirk Paul Lafler of Software Intelligence Corp for letting me use your laptop so I didn't have to do any projector fiddling.

A Great Blog From A SAS User

I just checked out Ann Maria's Blog from the Julia Group and was so impressed with her writing that I had to share it.

I particulary liked The Dangers and Wonders of Statistics Using SAS post.

Go check it out.

Now.

JavaScript Object Notation

I use Javascript quite a bit for my i-Doc site and some of the projects I work on. I really like the language because it's syntax is comfortable and you can do a bunch with it without having to know a lot about it. And getting stuff done quickly is pretty much the whole reason for programming.

But the "looseness" of Javascript can also tempt you to fall into programming habits that don't scale well.

When I first started learning Javascript I approached it as a purely function based language. Probably because I was already familiar with SCL (Screen Control Language) in SAS/AF and that's what I likened Javascript to. But anyways, all my JS code looked like this:

function doSomething(someVal) {
  var someLocal;
  // do some stuff
  return rValue;
}

function doSomethingElse(someVal) {
  var someLocal;
  // do some other stuff
  return rValue;
}

And I would store it in a file and include the file as a link in my header tags.
This works perfectly well and so I had no incentive to change it. Until I started getting lots of functions in lots of files. It doesn't scale well. But by changing the coding style just a little bit, I am able to write my JS code so it is much easier to maintain. Using JavaScript Object Notation (or JSON) I can fake namespaces. This lets me take more control over the design of my JS code. Using JSON the above would be rewritten:

myNameSpace = {
  doSomething function(someVal) {
     var someLocal;
     // do some stuff
     return rValue;
  } ,
  doSomethingElse function(someVal) {
     var someLocal;
     // do some other stuff
     return rValue;
  }
};

Then when I want to use one of the functions, I just preface it with the object name ( myNameSpace.doSomething(withThis); )
I usually choose the object name to be the same as the name of the javascript file. That way I avoid name collisions, and I can quickly find where a function is defined if I need to look at the source code.

Certainly, this is not a great leap forward in web programming. But I still see so much function-style javascript online that I thought it would be useful to pass it along.