Thursday, 28 October 2010

NOTE: DATA Step's _N_ is PROC SQL's Monotonic

I've made a few posts over the last few months about SQL (particularly its use within Enterprise Guide). One of my unstated issues with SQL is the inability to do the equivalent of DATA step's _N_ to count unique rows. The ability to add a sequence number to a table, or as a contribution to a calculated column (perhaps to create a unique key), is something that I occasionally need.

I recently found PROC SQL's monotonic function in the Tips and Techniques section of the Amadeus web site. It's not documented or supported, so it should be used with caution.
proc sql;
  create table notecolon as
    select * , monotonic() as counter
    from mylib.mytable;
SAS Usage Note 15138 says that the monotonic function is not supported in PROC SQL and that using the monotonic function in PROC SQL can cause missing or non-sequential values to be returned.

In the right circumstances, the monotonic function can be useful. And Amadeus have plenty more useful tips and techniques to peruse and use!