Little Utility Macro

A lot of times when I am working with interactive SAS, I find myself staring at a SAS date that has not been formatted. The quickest way to see the actual date was to go to my "scratch" enhanced editor and write a quick data _null_ to put it to the log. That was before I realized that macros can be invoked from the command line.

%macro date(d);
  %let r = %sysfunc( putN(&d,mmddyy10.) );
  %put &r;
%mend date;

Now I just put this little guy into my autoexec, and voila! a full 40 seconds of my life saved!

Speaking of saving time, did you know that you can follow my job site on twitter? If you had, you would have seen the newest posting for a SAS BI Developer needed in Maryland $120K/yr.

Just go to www.sasCoders.com and click the twitter button underneath the banner.

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!

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...

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.

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.

i-Doc Interactive SAS Documentation

After much hard work, I am happy to announce the arrival of i-Doc interactive SAS documentation. The idea behind i-Doc is to generate SAS documentation from users all over the world. I have started with SAS functions and hope to continue with formats, informats, macro, system options, etc. Eventually I'd like to provide copies in book format for people to keep on their desks.

Please check it out, tell your friends if you find it useful, etc. Currently i-Doc is beta and only works with internet explorer.

i-Doc Interactive SAS Documentation

Using Logical Expressions In SQL

Most of us SAS programmers approach SQL as simply a data extraction and table joining tool. Since most of us have used the data step longer than SQL, we tend to leave the logic programming to the data step with its if/then statements. However, SQL does have a way of assigning values conditionally. With the CASE expression you can test and assign values logically.
The basic syntax is:

CASE value  
   WHEN condition THEN result  
   WHEN condition THEN result  
   ELSE result
END

In the code below I am just assigning a 1 or a 0 to a column/variable named bool_tf.
Using the CASE expression is pretty straightforward and is another great way to use SQL to get more coding done in fewer steps.

data myData;
  input answer $;
datalines;
true
false
true
true
false
false
true
;
proc sql;
  create table a as
  select answer, 
         case substr(answer,1,1)
              when 't' then 1
       when 'f' then 0
  end as bool_tf
  from myData;
quit;

Saving Steps With SQL

Often we need to create some simple statistics for a set of data and then associate those stats with each observation of the original set. As a simple example
consider a table with only three rows:
N
3
6
4

We want to get the mean of the variable N and stripe it down all the observations:
N Mean
3 4.333
6 4.333
4 4.333

The first way I learned to do this was with a proc summary and a merge. A better way to do it is with proc sql.

Here is a little test data:
data myData;
input x level $;
cards;
11 a
31 a
51 a
2 b
61 a
8 b
21 a
71 a
91 a
4 b
61 a
21 a
5 b
7 b
5 b
31 a
1 b
61 a
8 a
9 b
3 a
2 b
5 a
7 b
7 a
3 b
;
* in that data set we have two variables X and LEVEL. We can get the stats on X for each level by summarizing and merging...;
proc sort data = myData;
by level;
run;

proc summary data = myData;
by level;
var x;
output out= tempStats(drop=_type_ _freq_) mean=mean max=max min=min;
run;

data sumStats;
merge myData tempStats;
by level;
run;

* or better yet, we can collapse the whole thing into one nifty proc sql step!;
proc sql;
create table stats as select *,
min(x) as min,
max(x) as max,
mean(x) as mean
from myData group by level;
quit;

Hooray For Vmware!

I am excited for computers again! Every once in a while something comes along that really changes the way you interact with computers. You know the feeling, it stops you in your tracks and makes you say, wow.

I remember when I was a kid and I first played a game called "Beach Head" on my Commodore 64. There was a level where you controlled a machine gun and the little computer guys would run at you from behind walls and throw grenades at you.
Every once in a while if you would shoot one of the little men he would yell "Medic!" or "I'm hit!". It was such a strain for that little computer to create the digitized speech that the whole game would slow down for a second or two. But my brother and I were seriously impressed. Wow!

I recently installed Vmware's Player on my little Dell laptop. If you are not familiar with Vmware and their virtualition technology then stop reading this and go to their web site. It is easily the most impressive software I have used in quite a while.

You see, I am going on vacation for two weeks (woohoo!) and will have some time to work on some coding projects during flights. I have been working on a perl/web/mySql project for my website for a while now and am getting close to finishing it. To work on it, I usually log into my remote server using ssh and work away. Works great until you aren't connected to the internet. So I thought, why not create a local server to work on while I am away from the internet?

Usually that would entail downloading a linux distro, partitioning part of my hard drive, making sure the distro has all the drivers it needs for my laptop, setting up and configuring all the tools I need, etc etc. Essentially a lot of wasted, unproductive time.

Last night I downloaded Vmware Player for free. Then I downloaded an appliance called Grandma's LAMP for free. An appliance is a full-blown pre-configured virtual server that is hosted on your machine through the player. Within minutes it was up and running.

All I had to do was go to my web server, tarball all the files for my application and download them to my laptop. Then I just copied them to my virtual
Ubuntu server using the pre-configured samba share and Voila! A completely useable local copy of my entire development environment in two hours! I am seriously impressed. And all without doing any reconfiguring on my little windows xp laptop.

And to top it off, I can take the whole virtual server and the player and copy them to a 2 gig thumb drive. Any computer I stick my USB drive into can host my development server. Wow, indeed.

Clean Up Clean Up

Clean up.
Clean up.

Everybody everwhere.

Clean up.
Clean up.

Everybody do your share.


This is the song my wife taught our two-year old daughter in the hopes that it would make clean-up fun and encourage more of it. Sometimes it works really well and sometimes not so well. Every now and then it backfires completely and my toddler makes a big mess just so she can run around in circles singing the Clean Up song. Leaving Mommy or Daddy to do the actual cleaning.

As SAS programmers, we are given a lot of freedom to easily create as many data sets as the system will allow in the workspace. I have met many SAS programmers that do not even carry a thought about the conseqences of keeping all those work data sets hanging around. Some of the trickiest bugs to track down can be caused by stale work data sets (especially when running interactive SAS).

I have found it very useful to delete all work data sets if I am working a piece of code repeatedly. That way I make sure previous runs don't taint current runs. A simple proc datasets does the trick:

proc datasets library=work mt=data nodetails nolist KILL;
quit;

So now that you've got the song and the code, you have no excuses for leaving a mess in the work library :)

Clean up! Clean Up! Everybody Everywhere!

Saving Time

When I was a kid my brother, sister and I spent a lot of time in my Father's dental lab. This gave us a unique opportunity to learn how to get things done in a time-sensitive production environment. The more business he got and the more successful his practice became, the more demanding his labwork. He spent a lot of time working in the lab perfecting techniques and efficiency. We kids would hang out in his dental lab looking for things to do and he would hand out miscellaneous tasks to us (sadly he locked away the NO2 from us). As we got older and more profecient working the lathe, drill, sand blaster, oven, etc we would get more critical tasks. Spending time with Dad meant spending time learning how to get things done in a fast-paced hands-on environment.

One thing Dad would always repeat to us is how important it is to get things done "quickly and correctly."

Just getting things done quickly won't cut it. And believe it or not, just getting things done correctly doesn't cut it either. Not if you have other steps in the process or customers waiting on you to complete your task. In order to have time in this life for things other than work, it helps to learn how to get things done both quickly and correctly.

Generally, most people think of working quickly as producing sloppy work. But actually, you can get things done quickly with FEWER mistakes. The trick is to seperate tasks into two categories: things that should be done very quickly, and things that should be done very correctly. When you get good at cutting down the time it takes for you to do the miscellaneous tasks you can spend more time getting the critical tasks done correctly. This type of thinking translates very well to programming. It has probably helped my career more than any other single piece of advice I have received.

So as you spend your day programming, think to yourself, "what are the non-critical tasks that I am having to do and how can I minimize them?" Believe it or not, with just a few small changes you can find yourself getting a lot more done.

Here is an example of a change that I have recently incorporated. If you are like me, you probably have a few folders on your hard drive that you are constantly having to access. Throughout my day I am constantly typing something like "c:\my data\reports\ad hoc\" into Save As and Open dialog boxes, Windows Explorer, etc. In Windows you can create a PATH variable to substitute. So in my example I might create a Windows path variable name R (stands for reports) that has the value "c:\my data\reports\ad hoc\". Now I can just type %R% to navigate to that folder. Saves time and frees my mind to focus on the more critical tasks than navigating Windows Explorer.

I believe I got that tip from http://www.lifehack.org/. It's a great site full of useful tips for minimizing the clutter so you can focus on getting things done quickly and correctly.