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?

Pre-Cambrian Code

How old is the SAS code you are working with? I am working with an application that was built from an application that was built from an application, etc. A lot of times you can tell the code was just taken from what was previously working and slapped into the newer version. And there are some crazy fossils to be found in that old code. Take this one for example:

proc sortt data =

This was showing up in just enough places to get me curious. It couldn't just be a common misspelling, could it? Nope.

Now the question is: Should I leave it there for someone else to find in a couple of years, or change it?

Bad Design

So here I am happily working along in SAS/AF updating an old V6 frame: Make some buttons, a couple listboxes, look up successful return codes for assigning a libref/fileref/opening a data set/ opening a directory/ etc, display a couple rows from a sas data set. Easy, easy, check, easy. Display rows, no problem. I'll just use the new-in-v8 (and somewhat obtuse) model/viewer relationship with a table viewer control. Drop the table viewer on my frame, go to the properties frame to adjust the attributes (that one always cracks me up), stick the SAS data set model on there, associate it with my viewer via the viewers.model attribute/property. No problem. Compile. Run. Click my View Items button. Go get more coffee as SAS does some crazy dog slooooooooooooooow operations to display the data set in the viewer. What the heck?

Quick search on support.sas.com and...

Slow performance initializing data with SAS Data Set Model
If you are displaying a data set with a large number of columns (ie. 100
or more) via a SAS Data Set model that is attached to a Table Viewer or
Form Viewer control in a SAS/AF FRAME entry, you may see slow
performance when initializing the viewer. This is a design issue and
may be alleviated if you instead display your data set through the
Version 6 legacy classes, Data Table or Data Form respectively.

Now I don't have any say in the number of columns in the displayed data set. And I agree, it smacks of bad design somewhere along the line when a SAS data set grows outrageously longitudanal (it's called normalizing people.)

But to call using V8 objects instead of legacy V6 objects bad design? Seems kinda weak if you ask me.

noFmtError For Me

You know those little SAS options that come in super useful about once every nine months, but you can never remember the name cause you almost never need to use it? So you spend half a day pulling your hair out trying to remember what the little bugger is called and scrolling through the millions of results that the SAS online doc returns from your futile keyword search (another topic for another day).

Well, here's one that came up today:

options fmtError|nofmtError;

This tells SAS not to worry if it can't find a format that a data set variable is associated with.

I'll end up using this when someone passes me a data set they have created, but I don't have access to the formats they are using.

SHELLacked

A couple months ago the company I do a lot of work for got a nifty new Unix server to replace the old one. Woohoo! It's fast. Screamin' fast. Now we can be even more productive than ever!

Except it doesn't have bash. Ugh. Unix without bash is like chocolate syrup without the chocolate. I don't really know what that is, but it certainly isn't good.

Something strange happens to my fingers at the standard prompt. They forgt how to tpye. And my memory goes all funny and I can't remember the names of directories and files. It's nearly impossible for me to get anything done. And multiple requests to sysAdmins have gotten me nowhere.
Dear Sysadmin: My tyoing sux. Can you plez install bash?

Dear Sysadmin: This Korn is killing me. Bash please?

Dear Sysadmin: The tab key seems to have died. Could you find a way to make it Bourne Again?

Call Scan() Routine

Here's a nice new SAS function/call routine thingy that I used recently:
call scan()

It works kinda like the scan() function, except it gives the index and length of the word. I was needing to look through text for a particular keyword and then I wanted to find other keywords within five words on either side. Say I'm looking for FIRE. And I want to know if HOUSE or CAR appears within five words. Before, I would have done this type of thing in PERL using it's sweet hash tables. But with call scan() I can get the position of a word, use substrn() to check for my keyword and then loop back and forth scan()ning for my other words.

Nifty.

Kicking The Macro Habit

I do a lot of work with SAS macro. I use it daily, but I must admit, I find the resulting code kludgy and ugly. Being able to write code that writes code is indeed powerful, but it comes with a cumbersome price: all those % signs, ampersands, the semi-colon that will sometimes get gobbled up by the compiler, local and global symbol tables, %sysfunc() to get to base functions, all those wacky quoting functions %NRBSTR( aaaarrrrrgggh! ), limited debugging, etc etc etc.

Plus, I have met many, many otherwise competent programmers that just can't seem to quite _get_ full scale macro programming. Like pointers to pointers in C, at some point the thread just unravels.

Now don't get my wrong. I think macro is a valuable skillset, and has a valuable place in your programming toolbox, but for large, complex applications I am always thinking there's got to be a better way.

SCL comes to mind. You can use it to write base sas code to the program stack just like macro, with the added benefit that you can manipulate and recall the stack. Imagine getting to recall all the base sas code that your big complicated macro produced instead of having to parse your log. There's other benefits too: built in data types arrays and lists; SCL debugging and compiling; much cleaner syntax, and clearly defined submit blocks; variable locality using declare, just to name a few.

So does anybody use SCL this way? I've been thinking about it for a while, but I haven't yet. Using SAS catalogs instead of text source files would be a drawback for me. Are there any other drawbacks?

Maybe those programmers that don't get large scale macro programming just have more attuned programming sensibilites than me. They see all those %'s and &'s and their refined programming brain wanders away in disgust. Maybe I will code my next large application in SCL from the beginning instead of turning to macro out of habit. But old habits are hard to break.

Validating Flat Files

There are still a lot of flat files out there. And there are a lot of SAS programmers who have to deal with them. And unfortunately, there are a lot of people who create flat files that don't understand their structure, or lack thereof.

I worked for a company that had a lot of vendors providing flat files of data. These vendors thought nothing of randomly expanding/shrinking columns and shifting data all over the place. Their DB was able to handle it, no problem. But when the flat file would get passed to us it would cause all kinds of headaches. What to do?

Check the dates. Try to get a date at the end of the record, read it in using a date informat and then check the result. If anything gets shifted around you either get a date that is very unrealistic or missing. Sure it's pretty simplistic, and not totally foolproof, but every programmer that saw it understood it. And more importantly, because it took 2 minutes to code, everybody used it and incorporated it into their own code. And suddenly life was better.

What's missing?

Here's some syntax I ran across today that I hadn't seen before:

data something;
set somethingElse( where= (someVar is not missing) );
run;

Usually I am skeptical of new syntax that duplicates the functionality of existing syntax. It causes language bloat and ends up adding unneeded complexity. But I think this is pretty useful. It looks a lot better than the alternative '' or ' ' or . for missing. And it solves ambiguities between numeric and character missings. I only have one complaint:

where= (someVar is not null)

Language bloat. I hate language bloat. Is the keyword null the same as the keyword missing?

Why do I hate language bloat?

If you are trying to figure out data discrepancies in a large system written by others and you run across both keywords, you will have to spend some time trying to figure out if SAS does indeed handle them the same by either writing test cases or looking through documentation.