| This post was kindly contributed by NOTE: The blog of RTSL.eu - Development with SAS® - go there to comment and to read the full post. |
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;
run;
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!
| This post was kindly contributed by NOTE: The blog of RTSL.eu - Development with SAS® - go there to comment and to read the full post. |