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!

Multiple By Variables

Here is one little piece of SAS programming that I always have to work out: When using multiple "by variables" in a SAS data step, when does the grouping flip? An example:

data stuff; 
  set otherStuff; 
  by var1 var2 var3; 
  if first.var1 then ...; 
  if first.var3 then ...;
run;

For some reason, I always have to sit and think through how multiple by variables effect each other. So here, once and for all, is the rule for me to remember:

If the group (value) changes in the variable to the left, it changes the group of all the variables on the right regardlessof their values.

It makes sense if you think it through, but sometimes it's just easier to write the rule down and refer to it (here!).

Summer Reading

Currently I am reading a book that is so good, I thought I would give it a quick recommendation. Against The Gods: The Remarkable Story of Risk is one of those books that I know-- before even finishing it, I will read again, and again. And I will gain deeper insights into history, humanity, stock markets, statistics and even the decisions that I make in my everyday life.

So if you get the chance, pick up a copy. And if you have any other good reads that you think I or others might be interested in, please share them here.

Los Angeles Basin SAS User Group

If you live in the Los Angeles area and have not had the chance to attend a LABSUG you are missing out. Kimberly Lebouton has worked very hard to bring a user group to Los Angeles and her efforts have been very productive. The speakers have been very good and Kimberly has worked diligently to listen and respond to attendee's feedback.

I was hoping to attend this year, but my wife is going out of town leaving me with babysitting duty. Of course, I could bring my toddler-- she would make a very engaging presenter!

I wonder if Kimberly could carve out 2 hours for nap time this year. . . :)

LABSUG
Friday June 22nd
Sheraton Los Angeles Downtown Hotel
http://www.labsug.org

Where Did the Observation Come From?

Here is a little snippet of code I created to address the problem of assigning a value to a variable based on what data set an observation came from in a data step. Here is an example:

Suppose I have a whole bunch of data sets each representing a different country. I want to set a lot of them in one data step and create one resulting data set with a variable called language. In order to create the language variable correctly, we need to know which data set the observation is coming from. Typically we would use the IN= option on the data set to create a flag and then check that flag using if/then logic.

data selectedCountries;
  set
    chile(in=chile)
    china(in=china)
    costa_rica(in=costa)
    egypt(in=egypt)
    fiji(in=fiji)
    turkey(in=turkey)
    usa(in=usa)
    saudi_arabia(in=saudi)
  ;

  if chile then language = 'SPANISH';
  else if china then language = 'CHINESE';
  else if costa then language = 'SPANISH';
  etc etc etc...
run;

One of the major problems with this approach is it does not scale well. The more countries you set, the more problematic your if/then logic becomes.

Here is a slightly more elegant solution that uses arrays and variable information functions. You still use the IN= option on the data set, however you want to name the in= variable the same as the value we want to assign. Then you create an array of all those in=variables. Finally, you loop through the array of in= variables and check for their boolean value. If it is true then you assign your new variable the value derived from the vname() function.

data selectedCountries;
  set
    chile(in= SPANISH)
    china(in= CHINESE)
    costa_rica(in= SPANISH)
    egypt(in= ARABIC)
    fiji(in= ENGLISH)
    turkey(in= TURKISH)
    usa(in= ENGLISH)
    saudi_arabia(in= ARABIC)
  ;
  array names[*] SPANISH CHINESE ARABIC ENGLISH TURKISH;
  do i = 1 to dim(names);
    if names[i] eq 1
     then language = vname( names[i] );
  end;
run;

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.

LRECL

Here is a SAS trick that is especially useful for Windows users. By default, Windows creates files with a logical record length of 256. This means if you are creating a flat file with records (lines) longer than 256, the lines are going to wrap. You can tell Windows exactly how long to make the record length on the filename statement in SAS. The option is lrecl= (logical record length) and it looks like this:

filename myFile "c:\some directory\some file.txt" LRECL= 400;

Then you can write lines to that file that are up to 400 characters long without fear of the line wrapping.

SAS Programming Google Search

I have added a custom SAS programming search button to the top of this blog. It is done through Google Co-op and should offer better SAS programming search results than just searching the web.

You can use it directly from this blog, or you can add it to your Google homepage. To add it to your Google homepage click on the button "Add To Google".

I have not added/filtered many sites in it yet, but already I can see the results are more specific for SAS programming than just searching the web.

If you see sites that don't belong in the search result or if you know of a site that should have appeared in a search result but for some reason didn't, please comment here. The more I am able to refine the results, the better the SAS programming search will be. Soon, I would also like to start taging the sites in the search results. If you have suggestions for tags, that would be useful too.
Happy Searching!

Not Equal

A long long time ago (or what seems like a long time ago!), before I could could call myself a professional SAS programmer I made lots of little mistakes in my programs. Now that I have been programming for a long time and have lots of good habits, I generally tend to avoid the little mistakes. Now when I make a mistake it is generally one of the bigger varieties. :)

One of the little mistakes I remember making was using the wrong "not equal" operator. It was terribly embarrassing for me at the time, and for some reason it stuck in my memory more than the other myriad mistakes I made.

When I first got hired as a SAS programmer, I did not have a whole lot of SAS experience. I had coded quite a bit growing up, but had only used SAS in a limited function at Texas A&M Univ on Windows. In my interview I explained my SAS skills honestly, and lo-and-behold they hired me! I was hired as an "intern" and had a few months to prove myself. I was told they needed people with PC experience because most of the programmers came from a mainframe MVS TSO background (which meant nothing to me at the time) and there were going to be more PC SAS contracts coming. Well, the PC SAS contracts never appeared and I suddenly found myself knee-deep in MVS TSO and mainframe SAS. JCL, ISPF, pf8 forward, pf7 back, pf3 end-- all new to me. It was all terribly daunting and every day I came to work, I thought someone was going to ask me to leave. So I did my best to keep my head above water and learn everything as quickly as I could. I thought I was doing a pretty good job masquerading as a true-blue mainframe developer until I wrote one of my first full programs and had another programmer look at it (the first week or two was spent making small changes to other people's programs and going through logs, etc). The reviewer wanted to know what this line meant:

if x <> y then delete;

I answered "if x not equal y then delete the row." The mainframer shrugged and gave me the benefit of the doubt that I knew what I was talking about. That was until someone else (one of the programmers employed by our client-- GULP!) looked at it and pointed out in a friendly email to everyone that <> is "not equal" in BASIC, but means something entirely different in SAS. I could feel everyone looking at me differently and hear their whispers.

"Basic? Basic? Is this kid a joke?" I had been exposed as a commodore 64 hack!

Well, luckily I wasn't fired and ended up learning a tremendous amount from those mainframe SAS programmers at my first real consulting job. I truly owe them my career.

So, what does <> mean in SAS? It is the MAX operator and returns the maximum of the two values on either side of it. Conversely >< is the MIN operator and returns the smaller of the two values.

Oh wait! We're talking about SAS here, right? Then I should say <> is _usually_ the max operator, but in one situation, it can stand in as the "not equals" operator I was intending it to be.

Proc SQL of course!

The =: Operator

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

Here is a quick little example:

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

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

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

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

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

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

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

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

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

SAS Unix Process ID

Today a friend called and wanted to know if there was an easy way to use the Unix Process ID as part of the name of the log file when invoking SAS in batch mode. She wanted to make (semi-)unique log files. She is concerned about uniqueness enough to not want to immediatly overwrite another log file, but not so much that she's worried about possible collisions when the system recycles a process ID.

Well, it just so happens the script variable $$ contains the process ID of that script. So you can use that when constructing your log file name. Such as:

nohup /home/sas mySAScode.sas -log "/tmp/mySAScode_$$.log" &

In the above, nohup tells unix to keep the process alive even after we've closed down our terminal and logged off.
/home/sas is the sas executable (or the script that executes SAS).

An example of the log file created by this command would be /tmp/mySAScode_1298656.log

If you wanted to get the process ID that SAS was started with you can use the automatic macro variable &SYSJOBID within SAS.

If you wanted to learn some more about running SAS on Unix you could also bounce over to SASonUnix.blogspot.com. It has some very good tips.

Cool V9 SAS Compress() Function Tricks

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

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

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

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

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

Pretty nice, eh?

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

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

Creating Numeric Buckets

The other day I was writing some code that was needed for a report. Part of the report was to take a number (integer) and fit it into a set of "buckets" at intervals of 100, rounded up. Confused? Here's some examples of what I needed:
3 --> 100
101 --> 200
1536 --> 1600
64 --> 100


Here's the line of code I used to accomplish it:
newNumber = ( ceil( myNumber/100 ) ) * 100;

Certainly not the most cerebral code ever written, but (hopefully) worth sharing.

This type of problem (creating numeric buckets) is fairly common and I was wondering if anyone else had a different way of solving it?

Hex It

Sometimes you need to specify an ASCII text character you can't see or print. You can specify any ASCII character using it's hex value and a hex literal in SAS. A hex literal in SAS is any of the 16 hex characters(0-9 and A-F) in quotes followed by an x. Such as '3A'x. You can see all the hex values for ASCII characters here: www.lookuptables.com

A classic example of this is creating a tab delimited file using a data _null_ step.

data _null_;
  set myData;
  put @01 var1 '09'x
      @10 var2 '09'x
  ;
run;

Another useful time to specify hex characters is to get rid of them. Suppose you
have some "dirty data" that somehow has some weird non-printable characters in it. You look at the text using the hex32 format and discover that some form feed characters somehow snuck into there (0x0C). The easiest way to get rid of them is to compress() the variable. Such as

myVar = compress(myVar,'0C'x); 

This will remove all occurrences of the character specified from the text variable.

Input Into Numeric

Many times you have a variable in SAS that is character and you want to convert it to numeric. This tends to come up a lot when importing from Excel. Excel shows a number, but SAS reads the column in as a character variable.

It is a bit difficult to *replace* the original character variable with a numeric one, but it is trivial to create a new numeric variable. Just use the input() function.

The syntax is:
numericVar = input(charVar, informat.);

NumericVar is the numeric variable you are hoping to create.
CharVar is the character variable that holds the 'number'.
Informat is a numeric informat that tells SAS how to translate the numeric 'characters' into a useful number. Dates are often used to illustrate this concept:

data _null_;
  charDate = '01mar06';
  numDate = input(charDate, date7.);
run;

In the above case, date7. tells SAS how to interpret the character string '01mar06'
into a number (in this case, the number of days since Jan 01, 1960).

Of course your character variable can be something as simple as '1234'. In that case this would work:

data _null_;
  charVar = '1234';
  numvAR = input(charVar, 4.);
run;

People often confuse the input() function with the put() function. I always
remembered by emphasizing the n sound with this little refrain:

Input Into Numeric.

Macro Debugging

Here's another SAS options related post. Have you ever had the frustration of debugging a big macro you didn't write? (Who hasn't!)

If you answered "yes" to the above then you probably know all about options MACROGEN and MPRINT. And you've probably spent a considerable amount of time staring at the log and all the messy MPRINT statements. Often the "bug" you are trying to find isn't necessarily in the macro code itself, but in the code it generates. Here's an easy way to get to that generated code so you can work directly with the logic it contains.

filename mprint "/tmp/code_to_debug.sas";
options mfile;

This will take the code generated by any subsequent macros and write it to the external file referenced by the filename statement.

Options?

Here's something a little different. I have a colleague who asked if there was a way to save the current SAS options and then restore them somewhere in the middle of a job stream. Instead of keeping track of what linesize, pagesize, etc had been set to, he wanted to just reset everything back to some "base". I did not know a way to do that off the top of my head, but after a little poking around in the onLineDoc I thought I had found the answer:

proc optsave saves your current options to a data set or to a registry key.

proc optload loads and sets the options from a data set or registry key.

So at the beginning of your sas session you could have code like this:

proc optSave data = work.myOptions;
run;

And then anywhere in the code that you wanted to set the options back to the way they were when sas started you could run code like this:

proc optLoad data = work.myOptions;
run;

You would think that would do the trick, right?

Unfortunately it doesn't seem to work as advertised. My colleague wrote back with the following:

Try this code. I got inconsistant result:

options ls=100;
proc optsave out=work.ycOpt1;run;
%put ls is %sysfunc(getoption(LS));

options ls=120;
proc optload data=work.ycOpt1;run;
%put ls is %sysfunc(getoption(LS));

When it gets restored linesize (ls) is. . . 96!?

This is using SAS 9.13 on Windows XP. However, the exact same code works as expected under Unix and the linesize option gets correctly restored to 100.

What gives?

How To Be Nice

Now that my daughter is mobile, she is constantly interacting with other kids at the park. Being the little social butterfly that she is, she has no problem walking up to other kids and trying to take their toys. Of course, at 15 months old she doesn't really know any better, but I still find myself trailing behind her saying "Be nice. Play nice." Some day soon she might actually listen.

But if you're using batch SAS on Unix, you can be nice today!

The "nice" command is used to raise/lower the priority of your background sas jobs. Generally we all run jobs at the same nice priority, but by lowering your priority you can let your big background jobs run without interfering with other people's jobs. This can be useful during the heavy use times and you are not too concerned about whether your job runs in 30 mins or 60 mins. It essentially lets you get out of the way of other users without putting your jobs on hold. Nice!

Here's how it works. The higher the nice number the lower your priority. The syntax of nice is:
nice -N /your/command/
where N is the number to move your priority. Positive lowers your priority by that amount, negative raises your priority by that amount.

So instead of me running my sas command like this:
$ sas myBigSAS.sas -autoexec "/my/autoexec.sas" &

I can be nice and run it like this:
$ nice -15 sas myBigSAS.sas -autoexec "/my/autoexec.sas" &

That will run my command with a higher nice value (low priority) freeing up resources for other users.

MOD For Append

Well, it's been a while since the last post. And I don't really have a good excuse other than life sometimes gets busy. But anyways, if anyone is still reading or happens to stumble by, here is another little SAS tip for you:

An easy (and fast!) way to APPEND to a text file is to use the MOD option on the file statement.

Such as:

data _null_;
file outFile MOD;
put 'new stuff being added to the end of the file';
run;

Macro %str() Tip

Today's post is just a quick little SAS MACRO tip. Suppose you are working in a macro and you need to compare a macro variable to an empty string. You can simply say:

%if &myVar = %then ...;

But that is not the most intuitive, especially when you have more complex logic such as:

%if &myVar = and &nextVar = something %then ...;

Looks a little confusing. Like I forgot to type something after the equals sign!

I like to handle this by using %STR( ). That way you can see that I am definetly testing for a blank. Such as:

%if &myVar = %str( ) %then ...;

Happy coding!