Efficiently Drop/Keep SAS Data Set Columns

What is the most efficient way to drop/keep columns (variables) in sas tables (data sets)?

For the most part, we would correctly say "using a keep= option on the data set as it is being read into the current step." A quick example to illustrate:

  data someData;
    set myData(keep= x y z);
  run;

  proc sort data = myData(keep= x y z) out= someData;
    by x y;
  run;

In fact, I even wrote a whole paper on this for SUGI a few years back.
Programming with the KEEP, RENAME and DROP Data Set Options

However, what if you didn't have access to modify the code? You need to create a seperate step just to keep/drop variables. What's the most efficient way to do it? You could create another data step and use the keep= option on the set statement:

  data myData;
    * just dropping some variables....;
    set myData(keep=x y z);
  run;

That approach will work, but it's not very efficient. In fact, it's pretty horribly inefficient. The data set is read one observation at a time, which is usually IO intense. And IO is a big efficiency suck. Plus the single data step actually creates a copy of myData as it's being processed. After the step is finished, the temporary copy replaces the original data set.

As a general rule, the most efficient way to move SAS data sets around is to copy them. Copying is usually more efficient than reading them one observation at a time because the copy can use a better copy buffer resulting in less IO. This hints that we should avoid the data step and use a procedure that can copy the data.

Here is what I came up with:

* a little test data set;
data myData;
  do i = 1 to 5;
    a = 'blah blah blah';
    b = 'foo';
    c = 'bar';
    x = 'some data ';
    y = 'lovely data';
    z = 4;
    output;
  end;
run;

proc datasets;
  change myData= tempData;
  run;
  append base=work.myData
         data = tempData(keep = x y z)
         force;
  run;
  delete tempData;
quit;

As you can see, we are using proc datasets instead of a data step. First we are renaming the data set to something temporary (the CHANGE statement). This is a super cheap operation since only the data set header (metadata) is being modified. Then we append (copy) the temporary data set onto the non-existent original data set. Along the way we tell SAS exactly which variables to keep/drop. We have to use the FORCE option because the base data set no longer exists after the rename. And then finally we delete the temporary data set. Whew!

I haven't run any benchmarks to see how much time this method would save because I, uh, don't have time. But I'm pretty sure it would be much faster than an extra data step.

If you know of another way to accomplish this that is more efficient than a data step, please share!

Thank You Steve Jobs

Today I am going to dedicate my little slice of this giant internet to Steve Jobs. Like a lot of people around the world, I found out Steve Jobs passed away through an Apple product. His vision and creativity touched all of us.

Rather than me clumsily listing all the inspiring things he accomplished with his short time, I thought it would be better to share some of his own words. If you haven't seen or read his Commencement speech to Stanford from 2005, please take a few moments to do so now. Below is the text of his speech from:
http://news.stanford.edu/news/2005/june15/jobs-061505.html


I am honored to be with you today at your commencement from one of the finest universities in the world. I never graduated from college. Truth be told, this is the closest I've ever gotten to a college graduation. Today I want to tell you three stories from my life. That's it. No big deal. Just three stories.

The first story is about connecting the dots.

I dropped out of Reed College after the first 6 months, but then stayed around as a drop-in for another 18 months or so before I really quit. So why did I drop out?

It started before I was born. My biological mother was a young, unwed college graduate student, and she decided to put me up for adoption. She felt very strongly that I should be adopted by college graduates, so everything was all set for me to be adopted at birth by a lawyer and his wife. Except that when I popped out they decided at the last minute that they really wanted a girl. So my parents, who were on a waiting list, got a call in the middle of the night asking: "We have an unexpected baby boy; do you want him?" They said: "Of course." My biological mother later found out that my mother had never graduated from college and that my father had never graduated from high school. She refused to sign the final adoption papers. She only relented a few months later when my parents promised that I would someday go to college.

And 17 years later I did go to college. But I naively chose a college that was almost as expensive as Stanford, and all of my working-class parents' savings were being spent on my college tuition. After six months, I couldn't see the value in it. I had no idea what I wanted to do with my life and no idea how college was going to help me figure it out. And here I was spending all of the money my parents had saved their entire life. So I decided to drop out and trust that it would all work out OK. It was pretty scary at the time, but looking back it was one of the best decisions I ever made. The minute I dropped out I could stop taking the required classes that didn't interest me, and begin dropping in on the ones that looked interesting.

It wasn't all romantic. I didn't have a dorm room, so I slept on the floor in friends' rooms, I returned coke bottles for the 5¢ deposits to buy food with, and I would walk the 7 miles across town every Sunday night to get one good meal a week at the Hare Krishna temple. I loved it. And much of what I stumbled into by following my curiosity and intuition turned out to be priceless later on. Let me give you one example:

Reed College at that time offered perhaps the best calligraphy instruction in the country. Throughout the campus every poster, every label on every drawer, was beautifully hand calligraphed. Because I had dropped out and didn't have to take the normal classes, I decided to take a calligraphy class to learn how to do this. I learned about serif and san serif typefaces, about varying the amount of space between different letter combinations, about what makes great typography great. It was beautiful, historical, artistically subtle in a way that science can't capture, and I found it fascinating.

None of this had even a hope of any practical application in my life. But ten years later, when we were designing the first Macintosh computer, it all came back to me. And we designed it all into the Mac. It was the first computer with beautiful typography. If I had never dropped in on that single course in college, the Mac would have never had multiple typefaces or proportionally spaced fonts. And since Windows just copied the Mac, it's likely that no personal computer would have them. If I had never dropped out, I would have never dropped in on this calligraphy class, and personal computers might not have the wonderful typography that they do. Of course it was impossible to connect the dots looking forward when I was in college. But it was very, very clear looking backwards ten years later.

Again, you can't connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future. You have to trust in something — your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life.

My second story is about love and loss.

I was lucky — I found what I loved to do early in life. Woz and I started Apple in my parents garage when I was 20. We worked hard, and in 10 years Apple had grown from just the two of us in a garage into a $2 billion company with over 4000 employees. We had just released our finest creation — the Macintosh — a year earlier, and I had just turned 30. And then I got fired. How can you get fired from a company you started? Well, as Apple grew we hired someone who I thought was very talented to run the company with me, and for the first year or so things went well. But then our visions of the future began to diverge and eventually we had a falling out. When we did, our Board of Directors sided with him. So at 30 I was out. And very publicly out. What had been the focus of my entire adult life was gone, and it was devastating.

I really didn't know what to do for a few months. I felt that I had let the previous generation of entrepreneurs down - that I had dropped the baton as it was being passed to me. I met with David Packard and Bob Noyce and tried to apologize for screwing up so badly. I was a very public failure, and I even thought about running away from the valley. But something slowly began to dawn on me — I still loved what I did. The turn of events at Apple had not changed that one bit. I had been rejected, but I was still in love. And so I decided to start over.

I didn't see it then, but it turned out that getting fired from Apple was the best thing that could have ever happened to me. The heaviness of being successful was replaced by the lightness of being a beginner again, less sure about everything. It freed me to enter one of the most creative periods of my life.

During the next five years, I started a company named NeXT, another company named Pixar, and fell in love with an amazing woman who would become my wife. Pixar went on to create the worlds first computer animated feature film, Toy Story, and is now the most successful animation studio in the world. In a remarkable turn of events, Apple bought NeXT, I returned to Apple, and the technology we developed at NeXT is at the heart of Apple's current renaissance. And Laurene and I have a wonderful family together.

I'm pretty sure none of this would have happened if I hadn't been fired from Apple. It was awful tasting medicine, but I guess the patient needed it. Sometimes life hits you in the head with a brick. Don't lose faith. I'm convinced that the only thing that kept me going was that I loved what I did. You've got to find what you love. And that is as true for your work as it is for your lovers. Your work is going to fill a large part of your life, and the only way to be truly satisfied is to do what you believe is great work. And the only way to do great work is to love what you do. If you haven't found it yet, keep looking. Don't settle. As with all matters of the heart, you'll know when you find it. And, like any great relationship, it just gets better and better as the years roll on. So keep looking until you find it. Don't settle.

My third story is about death.

When I was 17, I read a quote that went something like: "If you live each day as if it was your last, someday you'll most certainly be right." It made an impression on me, and since then, for the past 33 years, I have looked in the mirror every morning and asked myself: "If today were the last day of my life, would I want to do what I am about to do today?" And whenever the answer has been "No" for too many days in a row, I know I need to change something.

Remembering that I'll be dead soon is the most important tool I've ever encountered to help me make the big choices in life. Because almost everything — all external expectations, all pride, all fear of embarrassment or failure - these things just fall away in the face of death, leaving only what is truly important. Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose. You are already naked. There is no reason not to follow your heart.

About a year ago I was diagnosed with cancer. I had a scan at 7:30 in the morning, and it clearly showed a tumor on my pancreas. I didn't even know what a pancreas was. The doctors told me this was almost certainly a type of cancer that is incurable, and that I should expect to live no longer than three to six months. My doctor advised me to go home and get my affairs in order, which is doctor's code for prepare to die. It means to try to tell your kids everything you thought you'd have the next 10 years to tell them in just a few months. It means to make sure everything is buttoned up so that it will be as easy as possible for your family. It means to say your goodbyes.

I lived with that diagnosis all day. Later that evening I had a biopsy, where they stuck an endoscope down my throat, through my stomach and into my intestines, put a needle into my pancreas and got a few cells from the tumor. I was sedated, but my wife, who was there, told me that when they viewed the cells under a microscope the doctors started crying because it turned out to be a very rare form of pancreatic cancer that is curable with surgery. I had the surgery and I'm fine now.

This was the closest I've been to facing death, and I hope it's the closest I get for a few more decades. Having lived through it, I can now say this to you with a bit more certainty than when death was a useful but purely intellectual concept:

No one wants to die. Even people who want to go to heaven don't want to die to get there. And yet death is the destination we all share. No one has ever escaped it. And that is as it should be, because Death is very likely the single best invention of Life. It is Life's change agent. It clears out the old to make way for the new. Right now the new is you, but someday not too long from now, you will gradually become the old and be cleared away. Sorry to be so dramatic, but it is quite true.

Your time is limited, so don't waste it living someone else's life. Don't be trapped by dogma — which is living with the results of other people's thinking. Don't let the noise of others' opinions drown out your own inner voice. And most important, have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.

When I was young, there was an amazing publication called The Whole Earth Catalog, which was one of the bibles of my generation. It was created by a fellow named Stewart Brand not far from here in Menlo Park, and he brought it to life with his poetic touch. This was in the late 1960's, before personal computers and desktop publishing, so it was all made with typewriters, scissors, and polaroid cameras. It was sort of like Google in paperback form, 35 years before Google came along: it was idealistic, and overflowing with neat tools and great notions.

Stewart and his team put out several issues of The Whole Earth Catalog, and then when it had run its course, they put out a final issue. It was the mid-1970s, and I was your age. On the back cover of their final issue was a photograph of an early morning country road, the kind you might find yourself hitchhiking on if you were so adventurous. Beneath it were the words: "Stay Hungry. Stay Foolish." It was their farewell message as they signed off. Stay Hungry. Stay Foolish. And I have always wished that for myself. And now, as you graduate to begin anew, I wish that for you.

Stay Hungry. Stay Foolish.

Thank you all very much.

First One In Gets the Win

Yikes, it's been a while since the last update! So I will try to keep this one short and useful. Most everybody knows there are essentially two ways for tables to be merged in SAS: using the merge statement in the data step and using a join in SQL. Programmers tend to prefer one way over the other, and generally they are interchangeable. However, there are some minor differences that you should keep in mind. One such difference is in how overlapping variables are handled.

Here is a very basic one-to-one merge and its SQL equivalent:

data left;

  do i = 1 to 10;

    output;

  end;

run;



data right;

  do i = 1 to 10;

    output;

  end;

run;



data merged;

  merge left(in=a) right(in=b);

  by i;

run;



proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

Now lets add another variable that is the same on both data sets:

data left;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'left';

    output;

  end;

run;



data right;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'right';

    output;

  end;

run;

Now when the two data sets are merged, what value will be in the rows for the overlap variable?

It depends on the order you specify the data sets on the merge statement.

The value comes from the last data set to contribute a record to the merge.

data merged;

  merge left(in=a) right(in=b);

  by i;

run;

The resulting value for overlap will be 'right' because it is the last one named on the merge statement and each row in left has a match in right.

Would you expect it to work the same way in proc SQL? Of course not! You are a SAS programmer. These types of inconsistencies keep you employed.

proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

The value for overlap is 'left' in the joined data set. Opposite of how the data step merge works. I like to remember the SQL rule as: First one in gets the win.

Data Step Hooks

Here is something to keep in mind when using the END= option on the set statement: There is no guarantee you will hit the end of file.

Simple example to illustrate:

data test;
  do i = 1 to 10;
    output;
  end;
run;

data _null_;
  set test(where=(i > 10)) end= eof;
  if eof 
   then put "It set EOF for end of file";
run;

In the SAS documentation this is stated cryptically:
Restriction: END= cannot be used with POINT=. When random access is used, the END= variable is never set to 1.

If only it were true that END= can not be used. It can be used. It just might not work as you assumed it would. Consider the above data step where i > 5. In that case it does set eof to 1 as expected. Try where i < 5. Strangely, it does set eof to 1 even though we have never reached the end of file. SAS just "knows" that is has reached the logical end of the file. Which makes you think that reading 0 obs from the file should set eof to 1. But as we saw, it doesn't.

What actually happens is the data step finishes executing as soon as the set statement fails to read another record. So even if it did set eof to 1, it would never reach the if statement to execute it.

Unfortuately, there is no good way (that I know of) to run a bit of code at the end of file, even if 0 obs are read. You could toss the where clause and use a subsetting if statement. But then you are doing a lot of useless data step IO.

What would be be sweet is if SAS provided hooks into the data step. Two useful ones would be post-compile/pre-execute and post-execute. Maybe use special named labels?

Something like:

data _null_;
  set test(where=(i > 10)) end= eof;

PRE_EXEC:
  * In a super awesome world, the code in this label 
     would ALWAYS execute no matter if the set 
     statement reads anything or not;
  * This would eliminate a lot of the IF _N_ = 1 silliness;
return;

POST_EXEC:
  * In a doubly super awesome world, the code in this label 
    would execute at the end of the data step's life;
  * No matter how many observations were read.;
return;

run;

!= does not == ne

In general, the more programming languages you work with, the better you are going to become as a programmer. I try to work with a new language about every year, so I can stretch my little brain in lots of different directions.

However, the downside is sometimes you lose track of the syntax that used to be second nature to you. Or maybe it's just old age? Wait, what was I talking about?

Oh yeah, I was working away on some SAS macro code and it just wasn't working. It was simple code. Easy code. Code you can read and write in your sleep. And I still couldn't get it to work. After circling around the problem for waaaay longer than I should have, it finally stuck out at me like a sore thumb. Oh silliness, there is no != in SAS (unlike say, EVERY other language).

Unfortunately the offending != syntax fails silently in SAS macro:

%macro whatIsGoingOn(name);
  %put ** reality check name is &name **;
  %if &name != Stephen
   %then %put You are not Stephen;
  %else %put Hello Stephen;
%mend;

%whatIsGoingOn(Chuck);
** reality check name is Chuck **
Hello Stephen

Leading Zeroes

Here's a situation that comes up pretty often. You receive a file that contains zip codes. It's an excel file and you need to create a SAS data set out of it, so you can do some nifty market analysis. No problem, you clickety clickety through the SAS import wizard and voila! a data set is created. However, the zip_code variable is numeric and doesn't have leading zeroes.

Even though zip codes are made up of numbers, we generally want to treat them as character data. Luckily for you it is easy to turn those numeric variables character and restore the leading zeroes.

In general you:
1) Rename your original numeric zip code variable.
2) Create a new character zip code variable.
3) Apply the z. format to the numeric value to make it character and
restore the missing leading zeroes.

Here is what it looks like in practice:

 data myData(drop= bad_zip);
   length zip_code $5;
   set someData( rename= ( zip_code = bad_zip ));
   zip_code = put( bad_zip, $z5. );
 run;

It's a Math, Math World

Here is a blog that I found. It belongs to Michael O'Brien. I have not had a chance to really read through any of his posts, but it is on my to-do list as soon as I have a little tiny bit of time. Based on my quick cursory view of it, he seems to be writing quite a bit about statistics. And not the crazy insane look how whippety smart I am statistics writing that makes my eyes hurt and my brain feel small. I'm not a stat wizard! I'm just a programmer!

I just glanced through some of his posts, and while there was math, it didn't seem impenetrable. In fact, a quick scan of this post actually made my brain feel a little bigger.

Kudos to Michael of It's a Math, Math World

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?