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!

Stop Stop Stop Stop Executing!

Yikes! Here's a little something that seems quite obvious, but had never really occured to me. The other day I was talking to another programmer from businessresearchers.com about getting SAS to stop if there is an ERROR when running in batch mode. We don't want the SAS session to abort, just stop processing the code.

If you run something like the following in batch mode you might be a bit surprised at the results:

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

* purposeful error in this libname and data step;
libname s "sdfasdfjsadf/";

data junk2;
  set s.something;
run;

* SAS will now set obs=0 and go into "syntax check mode"
  whatever that's supposed to mean...;


* but look what happens here;
proc sql;
  insert into junk
  set i = 11;
quit;

So SAS sets obs=0 and enters syntax check mode,
but the SQL insert still executes. I am pretty sure SQL insert, update and delete will all be executed. It makes sense if you consider that obs=0 is an option that affects input not output; ie, it limits the observations being read into a step not being output. But still, it's a little counter-intuitive that "syntax check mode" would even allow a step to execute at all...

As far as I know, the only real way around this is to wrap your code in a macro and check the value of &SYSERR before any steps you DEFINETLY do not want executed if there is an error. Could this be another opportunity to use %GOTO?

SUGI 31 Paper Deadline

Get those proofreaders proofing! The deadline is today!

Documentation Tools

What tool do you use to document systems built with SAS? By "system" I am thinking of a solution built out of multiple programs. I have traditionally used excel and dropped boxes and lines everywhere a-la visio to diagram program flow. I'll be the first to admit; it's not perfect, but it works. Kinda.

Are there any good open-source tools that you like to use to do this type of system documentation?

Quantum missing

The other day I was pulling into one of my local surfing spots to watch one of the larger swells of the season roll through and I got a call on the cell phone from my wife. After exchanging the usual pleasentries she asked me if I knew how to get the maximum missing value out of a set of missing values. WTF?
For reasons that only really really smart people can comprehend, her organization sometimes uses different "values" of missing to enumerate different reasons for the missing value. Are you with me? Something along the lines of:

select( q1a )
  when('ONE') q1s = 1 ;
  when('TWO') q1s = 2 ;
  when('DK') q1s = .d ;
  when('RF') q1s = .r ;
   otherwise q1s = . ;
  end;

Ostensibly this is used for creating the variables that will eventually be used in statistics.
Cause as missings, they are automatically excluded by proc calculations; but you can still see "why" it's missing. I have to say, I don't know if it's a neat-o trick or a classic abuse of language potential (just cause you CAN do it doesn't mean it's a GOOD idea), but my wife is much more pragmatic than I and didn't care to hear my theoretical musings on storing multiple values in what most of us would consider a single value-- missing. She just wanted to know: did I have an answer?

Well, the max() function returns a missing value if any of it's arguments are missing so that was no good. But as luck would have it the max operator will treat a missing value as a real value. (Which seems quite opposite of the sum() function and + operator. . .) And not only that, but the SAS documentation actually includes an order of missing values. Going smallest to largest:
._
.
.A - .Z

So the simple answer to her complicated question was:

maxMissingValue = m1 <> m2 <> m3 ; 

Of course, I did not know the answer when she called and at the time I was much more interested in the overhead bombs that were breaking outside. I mean, these were some seriously mind numbing big waves. . . So I made some lame joke about how you can't know the missing value until you collapse the missing value potential wave into a singularity event through observation, blah blah blah, uh I have no idea honey.

Goto Memories

December already?! Seems like only yesterday I was dressing my little baby up for her first Halloween. And now we've already entered the *most wonderful time of the year*. A lot of people complain about the stores setting up their Christmas displays too early. Me? I like it. Bring on the displays! I like Christmas. I like winter. I like the holidays. I just wish I could get over this head cold. You know when you get a cold and it seems like your nose will never return to normal? Like you can't even remember what it was like to not have a cough? I hate having a cold.

When I was a kid my brother and I saved all our money one summer and bought ourselves a Commodore 64. That was the best Christmas ever. I would stay up all night programming in BASIC to get a smiley face sprite to bounce around the screen. I had to save my programs to cassette tape because we hadn't bought a disk drive yet. They were really expensive back then. I wish I had one of those cassette tapes now. If you played it back in the stereo it would make this weird analog warbly noise. I'd love to see what my BASIC code looked like. It'd probably be incomprehensible to me now. I remember I used to like cramming as many statements as possible onto one line and I think I used a lot of GOTO statements. Terrible, terrible bad habits for a 10 year old to be picking up!

So where am I going with this? This is a SAS blog after all, not a commodore blog. I was supposed to share some nugget of wisdom about programming in SAS but instead started rambling about BASIC and GOTO statements. Must be the cough syrup.

I still use GOTO statements today. Do you? They can come in quite handy for SAS/MACRO. Consider the following code. I know it's not a new technique, but it's useful and worth sharing.

* just two little data sets to work with;
data base;
  input key;
datalines;
1
2
3
4
;

data newRecords;
  input key;
datalines;
1
2
;

*************************************;

%macro earlyTermination();

%* Suppose you wanted to merge some data and see if
   there were any records that didnt match. Then you
   want to do some processing on those non-matching records.
   Otherwise if there were no non-matches you dont want
   to do any more processing.;

  proc sort data = base;
    by key;
  run;

  proc sort data = newRecords;
    by key;
  run;

  data nonMatches;
    merge base(in=base)
          newRecords(in=newRecords);
    by key;
    if newRecords and not base then output;
  run;

  %* check to see if there are any records in nonMatches;
  %let dsid = %sysfunc( open( nonMatches ) );
  %let nobs = %sysfunc( attrn( &DSID, nobs ) );
  %let rc   = %sysfunc( close( &DSID ) );

  %if &NOBS = 0 %then %goto done;

  %* otherwise do some processing with the
     nonMatches
     .
     .
     .;

  %put There were &NOBS non-matching records;

  %done:
%mend earlyTermination;

%earlyTermination;

In this case we goto an empty label. But there could have been some statements after %DONE. It's important to note however, that the %DONE label will be executed NO MATTER WHAT.

Happy programming.

fEqual() Compares Floats For Equality

Here's a SAS function I wrote in C using SAS/TOOLKT that addresses the floating point equality problem previously discussed here.

It is pretty straightforward and uses the algorithm based on this SAS TS Note.

%MACRO FUZZCOMP(X,Y,EPS=1E-12);
      (ABS(&X-&Y) LE &EPS*MAX(ABS(&X),ABS(&Y)))
%MEND;

You can find it and a couple other functions I have written here.

As an aside, is there any interest out there in knowing how to write user-written functions in C for the SAS System using SAS/TOOLKT? I know SAS/TOOLKT isn't the *sexiest* SAS product (that would be JMP), but it can be quite useful writing specific functions in a lower-level language like C. If there is any interest I could put something formal together for a paper or even just put a tutorial up on the web.

De-dupe In Excel

A lot of SAS programmers have to deliver data in Excel every once in a while. Sometimes, after you've gotten the data into Excel you find that you need to get rid of duplicates. Here's how to do it in Excel:

1) Be sure your columns are named.

2) Highlight the columns you want to use as your sort key (the ones you would use in your BY statement for PROC SORT).

3) From the drop-down menu go to Data->Filter->Advanced Filter.

4) Excel will automatically select the range you have highlighted. You should see a little checkbox that says "Unique Records Only." Check that.

5) Hit OK.

What Every Computer Scientist Should Know About Floating-Point Arithmetic

So I've been thinking about floating point numbers recently. Mostly I've been thinking about comparing floating numbers for _relative_ equality. I know this certainly isn't a new issue for most, especially if you have worked with a "lower" level language like C/C++, but for the average SAS programmer it may come as a surprise that 7.4 may not = 7.4! In fact the rules of real numbers dictate that 7.4 can never = 7.4 since they are both approximations ( or shorthand ) for an infinetly precise number with decimal places stretching from here to Mars and back again ad infinitum.

In the general world we don't really care that much about floating point inequality because our precision, or more specifically lack-of-precision, makes it a moot point.

But in the world of computers and real numbers precision is always an issue. As anyone who has been unfortunate enough to write code such as this has (painfully) learned:

// add .10 cents rebate to the customers account till they have reached 
// the rebate maximum
// called by perVisit() function

const float REBATE_MAXIMUM = 2.5;  // $2.50 rebate max

void addRebate( Customer &c)
{
  if ( c.accumulatedRebate == REBATE_MAXIMUM ) return;
  else 
  {
     // add the ten cents to their account and update their accumulated rebate
     // so they do not go over
     c.account += .1;
     c.accumulatedRebate += .1;
  }
}

Now who's going to explain to the CEO why all the 3rd quarter revenue got eliminated in massive rebates? GULP.

Hopefully you recognize the error in the above code? Since the values being compared are floats they are not really 2.50 but really something closer to 2.50000000007 or 2.5000000000001 or well _anything_ once you get past the signifigant digits of 2.50.

But the above code is C++ and as a SAS programmer you don't have to worry about those kinds of hairy details right? Try this code from Data Savant Consulting(which has a nice page discussing this very issue):

data _null_;
  x = 7.3;
  x = x+ 0.1;
  y = 7.4;
  if x = y then put "Duh! of course they are equal.";
  else put "Doooh! " x " and " y " are not equal!!";
run;

Then go read this!
What Every Computer Scientist Should Know About Floating-Point Arithmetic
Or if you don't have the time to grind through that, just remember comparing floats for equality is not usually a good idea.

The Sum Function

Today's function is very straightforward. It adds numbers.

num = sum( argument, argument, ... );

So straightforward in fact, that some of you new to SAS may be wondering why you might even need such a simple function? There's certainly nothing wrong with the old "+ sign" right? Well, the sum() function can do something that the plus sign cannot do. And that is treat missing values as if they are 0. That can be a very important distinction if there is the possibility of adding variables that may contain missing values.

Consider the following data step:

data _null_;
  a = 1;
  b = 2;
  c = .; * our missing value;

  r1 = a + b + c;
  r2 = sum( a, b, c );
  put r1=;
  put r2=;
run;

The value of r1 will be missing since the plus operator returns missing if one of it's arguments is missing. The value for r2 will be 3 since it treats the missing value as if it were a 0

Another SAS Function Friday

Friday again already?! Doesn't it seem like time speeds up around Autumn? Something about the shortening days, the changing weather, the new TV line-up, the expectation of the holiday season soon approaching... I dunno, maybe it's just me?

For some crazy reason all this ruminating on days shortening kinda reminds me of one of my favorite SAS functions: intnx(). How's that for a weak tie-in? :)

Intnx() is used to increment a SAS date/time/datetime value by a given interval and returns a SAS date/time/datetime value. The following syntax should be enough to get you started, refer to SAS documentation for more details:

dt = intnx( 'INTERVAL', dateTime, increment <,alignment> );
Where dt is the date/time/datetime value returned,
INTERVAL is a time interval (WEEK, MONTH, HOUR, etc),
dateTime is a SAS date/time/datetime value,
increment is a positive or negative integer which specifies the number of intervals to shift the value,
and alignment controls the position of the shifted value within the interval (BEGINNING|MIDDLE|END|SAMEDAY). Default is beginning.

Got it? How about an example:

data _null_;
  * take todays date and shift it forward two months;
  thisDay = today();
  forward2Months = intnx('MONTH', thisDay, 2, 'SAMEDAY');
  put forward2Months= mmddyy10.;
run;

We specified SAMEDAY as the fourth argument instead of letting it default to BEGINNING which would have given us a date of 12/01/2005 instead of 12/07/2005.

Ready to test out your new function? I've always been confused about what day is the first day of the week? A quick google search gets me a very infomative page which states:

The Bible clearly makes the Sabbath the last day of the week, but does not share how that corresponds to our 7 day week. Yet through extra-biblical sources it is possible to determine that the Sabbath at the time of Christ corresponds to our current 'Saturday.' Therefore it is common Jewish and Christian practice to regard Sunday as the first day of the week (as is also evident from the Portuguese names for the week days). However, the fact that, for example, Russian uses the name "second" for Tuesday, indicates that some nations regard Monday as the first day.

In international standard ISO-8601 the International Organization for Standardization (ISO) has decreed that Monday shall be the first day of the week.

So for you SAS trivia buffs out there, figure out which day SAS considers to be the first day of the week. Does it follow the Judeo-Christian standard of Sunday? Or bend to the ISO-8601 standard of Monday?

Happy Friday!