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!

A Word Counting Function For SAS

After some tinkering and toying around, I was finally able to negotiate the programming obstacle course known as SAS/TOOLKIT(R). SAS/TOOLKIT allows you to create user-written procedures, formats, informats and functions for the SAS system. I stuck to just writing a function.

The function is written in C and compiles to a dll file that you put into a SAS -PATH directory. Then you can use it just like any base SAS function. You do not need to have SAS/TOOLKIT in order to use the compiled dll file. So feel free to download the dll and use the function.

The dll file is hosted on my company site pelicanprogramming.com at
http://www.pelicanprogramming.com/sas/wcount.zip

Save the dll file after you download the zip. You can either save it to one of your SAS -PATH directories or update your SAS config file. If you don't know how to (or don't feel comfortable) updating your SAS config file, just save the dll file to
C:\Program Files\SAS\SAS 9.1\core\sasexeThis is where SAS finds most of it's own modules so adding this one in shouln't hurt anything. ;)

Oh yeah, it only works with SAS 9.13 on Windows PC SAS. Sorry SAS 8ers. Sorry Unixers.

The function is named wcount() and it counts the number of words in a string. The syntax is:

int = wcount( string <,char> );

Where int is the number of words,
string is the character string you are counting the words in,
and char is an optional second argument to specify the word delimiter. The default delimiter is a space. Actually wcount() defines a space as any "white" space: space, tab, carriage-return, newline, vertical tab and form-feed.

An example:

data _null_;
  wordCount = wCount('this  is my   test string ');
  put wordCount=;
run;

This would replace the following traditional SAS code:

data _null_;
  * count the number of words in a string;
  string = "this is my string of words";
  wordCount = 1;
  do while ( scan( string, wordCount) ^= '');
    wordCount+1;
  end;
  wordCount+(-1);
  put wordCount=;
run;

So, if you've got version 9 PC SAS and don't mind messing around a little bit, download the file and test it out. If you have any specific problems you can find my e-mail address in the readMe file included in the zip.

SAS Function Friday

In an attempt to increase my blog posting rate I will be highlighting a SAS function every (hopefully) Friday. At least ever Friday that I have access to a computer.

There's so many SAS functions, where do we start? Do we go for one of the more obscure ones in order to start things off with a little razzle dazzle? Something like CALL PEEK or CALL POKE. Cause it's always useful to know the address of your SAS variable. Actually, it can be very useful, just not very often. How about something a little more pedestrian?

Everybody knows all about the COMPRESS() function, right? It's not very razzle dazzle but it's darn useful. Compress() can be used to get rid of......
specific CHARACTERS in a character string!

Notice I did not say spaces. Although, a lot of times it's used just to get rid of spaces. That's the default for the optional second parameter.

data _null_;
   x = 'get rid of spaces';
   x = compress( x );

    y = 'get_rid of spaces_and_ underscores';
    y = compress( y, ' _' );
    put x = ;
   put y = ;
run;

One question you should always have when approaching a SAS character function is what is the length of the return value? In other words, if you create a new variable, what is it's length going to be?

data _null_;
    x = "Woohoo! It's Friday!";
    y = compress( x );
run;

What is the length of y? Eight (the "default" for a SAS variable)? Eighteen (the length of Woohoo!It'sFriday with the spaces comressed out)? Twenty (the length of the variable x)?

Of course you knew the answer is 20. Woohoo! smart reader.

Nifty Informat

What do you do if you are reading a date from a file and the owner of the file suddenly decides to change the format of the date?

Use the new anydtdte. informat.

From the SAS documentation:
Reads and extracts date values from DATE, DATETIME, DDMMYY, JULIAN, MMDDYY, MONYY, TIME, YYMMDD, or YYQ informat values


Nice.

A New SAS Blog To Check Out

It used to be that you could not find many resources for SAS on the web. Except for SAS-L and the odd university stuff, there just wasn't much out there. Thankfully, that's changing. For the SAS novice there is now more SAS related material on-line than ever before. Here's a new SAS blog that's aimed at spreading some SAS knowledge. Take a moment and check it out. And maybe even show some support with a comment or two. . . :)

SAS programming from scratch - by STANSI

Function WishList

I am looking for ideas for functions that you would like in SAS but currently do not exist. I have a couple of ideas such as:

Count the number of words in a string (or items in a list).

How about a function to quote the words in a character string?

A function to determine if a number is prime or not?

Would you like a function that returns an MD5 hash from its input?

And I looked through a couple of the SASWARE ballots and found some contenders.
Such as, provide a function that returns the ordinal of a word in a string, such as
WORDINDEX("abc de def", "def")=3

Do you have any other ideas? If so, please let me know.

Macro comments

%macro aQuestion;

%* this code doesn't have any problems does it?;

%put Why are macro comments handled so badly?;
%put I mean, we are at version 9 here people.;
%put I wonder what version number will handle macro comments correctly?;
%put Version 10, 12, 14...?;
%put I wonder at what version number I will finally remember to stop using contractions in my comments!;

%mend aQuestion;

%aQuestion;

BLM

Recently I got an email from someone that thought I might be interested in an article about the SAS Institute in some online industry newsletter. I might have signed up and read the article if the executive summary didn't read like it was written by a 12 year-old making fun of her dad:

"SAS: Striving to Sustain Leadership
by P.J. Jakovljevic
SAS Institute has been successful, moving beyond a business intelligence. Lately, it has lately focused on sustaining its technology leadership, expanding in some vertical markets, and becoming more attentive to the low-end market."

Again, I couldn't bring myself to actually create a login to read it, but this other executive summary on the same site seems like it could be worth the read. If only to convince myself that most people in this industry are truly bat crazy.
"BLM: Buzzword Life Cycle Management
William Sheppard - August 6, 2005

Executive Summary

The IT industry is alive with buzzwords. The management of buzzwords represents a significant area of improvement for both the buzzword users (BU, for example vendors, analyst and consultants) and buzzword consumers (BC, mostly end users). Buzzword life cycle management (BLM) is a proven discipline being applied to this crying need within the software industry..."

My IT skillz must be going soft cause I've never taken part in the buzzword life cycle discipline. But I've got a hunch that waterfalls are involved.

Check it out.

Homegrown Solution

I finally got around to putting together a little utility to encrypt SAS script/source files. Of course, since SAS won't put out any API's or SDK it doesn't hook into the system directly. You just use an unnamed pipe on a filename statement to use it. The concept is pretty straightforward.

The utility is called Fugu. You can read more about it and download it here. I did a quick search of google before writing it and couldn't find a utility that is small, fast, easy to use and writes to STDOUT. Maybe someone else will find it useful?

Currently I am only offering up a windows binary version, but I also put the source code up there so you can compile it for your own platform. I did compile it using gcc on my linux box and it worked fine.

Speaking of APIs and SDKs, does anyone have the version 6 SAS/TOOLKIT book? I looked all over the place and couldn't find anything useful in online documentation. It is all additional notes to the version 6 book. So, if you've got the book and it's collecting dust, I'll pay for the shipping...

An Answer?

If anyone is interested, to keep hardcoded usernames and passwords from sitting in your SAS/Connect script, the SAS Institute recommends putting macro vars into the connect script and assigning those macro vars in a compiled data step. Straightforward and easy to use.

Check it out:
FAQ #1800

As an aside, if you've got > 1800 frequently asked questions, can you really describe them as being frequently asked? Someone out there is asking a lot of questions. Frequently.

find . -exec fgrep -i "passw" '{}' \; -print

Do you use SAS/Connect? I do. I think it works really well. I've used it for many years in many situations and never found it coming up short. I would even go so far as to say it's one of the few things in SAS that is straightforward, stable and a pleasure to use.

Do you hardcode usernames/passwords into your connect scripts? Sometimes circumstances dictate it. Do you use pass-through SQL? Do you use SAS/Access libname statements? Do you think it might be a bad thing to have usernames/passwords sitting around in code?

This is something I've thought about before, and it just came up at work recently so I'm thinking about it again. As far as I know there is no facility in SAS to encrypt/decrypt script files during the SAS session (in this case, I am considering a "script file" to be anything not compiled: base sas, connect script, config files, etc). Does anyone know if there is such a mechanism?

I think I could write something to accomplish this, though it would be a little kludgy since there are no api hooks into the SAS system internals. But then, what's a little kludge between SAS programmers?