Leading Zeroes

Here's a situation that comes up pretty often. You receive a file that contains zip codes. It's an excel file and you need to create a SAS data set out of it, so you can do some nifty market analysis. No problem, you clickety clickety through the SAS import wizard and voila! a data set is created. However, the zip_code variable is numeric and doesn't have leading zeroes.

Even though zip codes are made up of numbers, we generally want to treat them as character data. Luckily for you it is easy to turn those numeric variables character and restore the leading zeroes.

In general you:
1) Rename your original numeric zip code variable.
2) Create a new character zip code variable.
3) Apply the z. format to the numeric value to make it character and
restore the missing leading zeroes.

Here is what it looks like in practice:

 data myData(drop= bad_zip);
   length zip_code $5;
   set someData( rename= ( zip_code = bad_zip ));
   zip_code = put( bad_zip, $z5. );
 run;

I have a data set of sales data by day. Unfortunately the names of the columns represent the dates. In order to work with the data, I need to transform the data set so each day represents an observation.

The data set looks something like this:

store _010_05_01 _010_05_02 _010_05_03 ....
1     8          5          6
2     6          9          3
3     7          9          8
....

As you can see, I have a store variable and several variables that are named by the date and contain the number of sales for that day. I need it to look like this:

store date   sales
1     5/1/10 8
1     5/2/10 5
1     5/3/10 6
2     5/1/10 6
2     5/2/10 9
2     5/3/10 3
....

Unfortunately this is a pretty common problem when you receive data from vendors who aren't sure how you are going to work with it. Luckily the transformation is pretty easy:

1) data byDay;
2)   set myData;
3)   array t[*] $ _010: ;
4)   do i = 1 to dim(t);
5)     sales = t[i];
6)     date  = vName( t[i] );
7)     output;
8)   end;
9) run;

This little data step creates an array to hold our date variables. (3)We use a little syntax sugar to keep from having to type out all the variable names _010:. The colon tells the SAS compiler to list out all the variables that have the _010 prefix. (4)Then we loop through all the elements in our t[] array. We use the vName() function to get the name of the variable that t[] is referring to (6). And finally we output once for each iteration of our loop (7).

Obviously if your date columns aren't named uniformly (_010:) then things won't work as nicely. And you would likely want to add a KEEP statement to just keep the variables you are creating along with any others you might be interested in.

If you made it this far, you may have noticed I am not quite finished with my transformation. Line (6) assigns the value of date to something like '_010_05_01'. Not quite what we wanted. We need to turn that into a SAS date. I need to substr() the first two characters off to remove them (_0) and then input() the result using the appropriate informat. So line (6) should really be:

6) date = input( substr( vName( t[i] ), 3), yymmdd8. );

A little more complicated, but hopefully still understandable.

Hopefully you find this useful! Comment with any comments/observations.

Finding the Max Value In An Array

The max() function makes it easy to find the maximum value in a SAS array.

Given an array like:

array x[*] x1-x10;

maxValue = max(of x[*]);

Pretty slick, eh? Remember, it doesn't return the position of the max element, just the max value.

This can be pretty useful if you want to find out if at least one element of an array has a value. Like if you have an array of answers and you want to find out if there is at least one answer in the array.

hasAnAnswer = max(of answers[*]);

or in logic:

if max(of answers[*])
 then do;
        * yes, they have answered at least one question;
      end;

*NOTE: I have decided to keep this SAS programming blog SAS focused and move my Ruby on Rails writing to a Rails oriented blog. I am a guest contributor at www.HABTMProjects.com. If you were interested in following along with my RoR projects then please hop on over there and sign-up.*

Proc FCMP

A long time ago I figured out how to write functions for SAS using SAS/Toolkit. It was not a straightforward process and not very useful. In fact, someone recently asked me about it and I was unable to share much useful info. However, I did find proc FCMP which does look to be very useful-- if you're into writing your own functions, that is!

Here is some online info:
http://support.sas.com/rnd/base/datastep/user-written-functions.pdf
http://support.sas.com/documentation/onlinedoc/base/91/fcmp.pdf


If you have SAS 9.1 you can use it to write functions that can be used in some
procedures, but not within the data step. However, if you have SAS 9.2 you can use them in a data step. And it uses data step syntax so you don't have to know C!

i-Doc Interactive SAS Documentation

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

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

i-Doc Interactive SAS Documentation

The Cats() Function

One of my favorite new functions is the cats() function available in SAS v9. It is a compress() like function in that it removes leading and
trailing blanks, but it also concatenates the results. CATS() stands for concatenate and strip. Basically the cats() function takes this
type of assignment statement:

key = trim(left(firstName)) || trim(left(lastName)) || 
      trim(left(phone)) || trim(left(zip)) ;

and changes it to this:

key = cats(firstName, lastName, phone, zip);

Generally, the cats() function will return a value with a length of 32767 in the data step. So as always, it's a good idea to use
a length statement on the variable you are assigning to. In this example I might use something like:

length key $200;

The cats() function belongs to a family of cat() functions each doing it's own version of concatenate: cat(), cats(), catt(), catx().

Coming up in version 10, the dog() family of functions! :)

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;

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!

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.

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.

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.

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.