First One In Gets the Win

Yikes, it's been a while since the last update! So I will try to keep this one short and useful. Most everybody knows there are essentially two ways for tables to be merged in SAS: using the merge statement in the data step and using a join in SQL. Programmers tend to prefer one way over the other, and generally they are interchangeable. However, there are some minor differences that you should keep in mind. One such difference is in how overlapping variables are handled.

Here is a very basic one-to-one merge and its SQL equivalent:

data left;

  do i = 1 to 10;

    output;

  end;

run;



data right;

  do i = 1 to 10;

    output;

  end;

run;



data merged;

  merge left(in=a) right(in=b);

  by i;

run;



proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

Now lets add another variable that is the same on both data sets:

data left;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'left';

    output;

  end;

run;



data right;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'right';

    output;

  end;

run;

Now when the two data sets are merged, what value will be in the rows for the overlap variable?

It depends on the order you specify the data sets on the merge statement.

The value comes from the last data set to contribute a record to the merge.

data merged;

  merge left(in=a) right(in=b);

  by i;

run;

The resulting value for overlap will be 'right' because it is the last one named on the merge statement and each row in left has a match in right.

Would you expect it to work the same way in proc SQL? Of course not! You are a SAS programmer. These types of inconsistencies keep you employed.

proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

The value for overlap is 'left' in the joined data set. Opposite of how the data step merge works. I like to remember the SQL rule as: First one in gets the win.

Data Set Sorted By Information

#UPDATE# Please see the comments for a better way to get the sorted by information from a data set! #UPDATE#

The other day a colleague mentioned that it was not possible to get the sort information for a data set from the dictionary tables. Intrigued, I
took up the challenge. While it's true that you cannot get the sorted by information directly, it is possible to get the necessary information
and put it together.

First of all, a little test data set:

data myData;
  input key1 nonKey keyB key3;
cards; 
1 2 6 3
1 5 5 7
2 7 4 4
3 3 5 9
4 5 3 2
1 5 4 6
3 3 9 8
5 5 7 9
6 6 4 3
3 3 3 6
;

I chose the names key1, nonKey, keyB, and key3 to make sure I wasn't getting the variables in alphabetical order
and mistaking it for the sort order.

Now sort the data set:

proc sort data = myData;
  by key3 descending keyB key1;
run;

It is important to make sure this works with descending sorts. Also notice the
variables are in a different order than in the data step so we don't confuse any artifact of creation order with sort order.

And now finally the code that will report the sort order. I used SQL and a data _null_ step to get the information then I just write it to the log. Originally I was just trying to see if it could be done, but just writing it to the log is not the most useful. Now that I've seen it works, I can rewrite it as all macro code using the vtables and %sysfunc() calls. That would allow me to make it a "function" style macro which returns a value to be used within code. Maybe tomorrow or next week...

%macro getSortedByVars(lib=,mem=);
%* This macro will write the sort order of a data set into the log;
%* If there is no sort on the data set it returns a blank;
%* it takes two parameters: the library and the name of the data set;
%* June 2008, Stephen Philp datasteps.blogspot.com/pelicanprogramming.com;
%let lib = %upcase(&lib);
%let mem = %upcase(&mem);

proc sql;
  create table keys
  as select name, sortedBy, case 
                    when (sortedBy<0) then 'DESCENDING'
     when (sortedBy>1) then ' '
      end as prefix,
      case 
                    when (sortedBy<0) then abs(sortedBy)
     else sortedBy
      end as sortOrder
from dictionary.columns
  where libname ="&LIB"    and
        memname = "&MEM" and
  sortedBy ne 0
  order by sortOrder;
quit;

%* now pack up those values from the keys table;
data _null_;
  length value $32767; 
  if 0 then set keys nobs=n;
  do i = 1 to n;
    set keys point=i;
 value = catX(' ', value, prefix, name);
  end;
  call symput('sortedBy',trim(value));
  stop;
run;

%put &sortedBy;
%mend getSortedByVars;

%getSortedByVars(lib=work,mem=mydata);

Using Logical Expressions In SQL

Most of us SAS programmers approach SQL as simply a data extraction and table joining tool. Since most of us have used the data step longer than SQL, we tend to leave the logic programming to the data step with its if/then statements. However, SQL does have a way of assigning values conditionally. With the CASE expression you can test and assign values logically.
The basic syntax is:

CASE value  
   WHEN condition THEN result  
   WHEN condition THEN result  
   ELSE result
END

In the code below I am just assigning a 1 or a 0 to a column/variable named bool_tf.
Using the CASE expression is pretty straightforward and is another great way to use SQL to get more coding done in fewer steps.

data myData;
  input answer $;
datalines;
true
false
true
true
false
false
true
;
proc sql;
  create table a as
  select answer, 
         case substr(answer,1,1)
              when 't' then 1
       when 'f' then 0
  end as bool_tf
  from myData;
quit;

Saving Steps With SQL

Often we need to create some simple statistics for a set of data and then associate those stats with each observation of the original set. As a simple example
consider a table with only three rows:
N
3
6
4

We want to get the mean of the variable N and stripe it down all the observations:
N Mean
3 4.333
6 4.333
4 4.333

The first way I learned to do this was with a proc summary and a merge. A better way to do it is with proc sql.

Here is a little test data:
data myData;
input x level $;
cards;
11 a
31 a
51 a
2 b
61 a
8 b
21 a
71 a
91 a
4 b
61 a
21 a
5 b
7 b
5 b
31 a
1 b
61 a
8 a
9 b
3 a
2 b
5 a
7 b
7 a
3 b
;
* in that data set we have two variables X and LEVEL. We can get the stats on X for each level by summarizing and merging...;
proc sort data = myData;
by level;
run;

proc summary data = myData;
by level;
var x;
output out= tempStats(drop=_type_ _freq_) mean=mean max=max min=min;
run;

data sumStats;
merge myData tempStats;
by level;
run;

* or better yet, we can collapse the whole thing into one nifty proc sql step!;
proc sql;
create table stats as select *,
min(x) as min,
max(x) as max,
mean(x) as mean
from myData group by level;
quit;

Not Equal

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

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

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

if x <> y then delete;

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

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

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

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

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

Proc SQL of course!

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?