We are still helping our customers get more out of the data they have and make data driven decisions. Our new site is packed with information and insights of how data can be the catalyst to your business growth.
The DATA step environment has an internal counter that takes the form of an automatic variable named _N_. This is because the DATA Step executes statements reading one record at a time and looping through the statements. This variable can be particularly useful when you need to perform special processing for the first/last record found, or to simply add a counter to your table.
Within the SQL environment, the concept of internal iterations does not exist as data gets read and analysed in pages rather than one at a time. But there is an undocumented function that can reproduce the behaviour of the _N_ DATA step variable. This is the monotonic() function, which can be used to generate sequential data and also to evaluate it.
create table results as
select monotonic() as sequence,
when (monotonic()=1) then 'First'
else 'Not First'
end as text
In this example the monotonic() function gets used to create a sequential number and to evaluate the first observation on the data set. Each time the monotonic() function gets called, a new sequential number will be generated.
sequence Name Sex Age Height Weight text
1 Alfred M 14 69.0 112.5 First
2 Alice F 13 56.5 84.0 Not First
3 Barbara F 13 65.3 98.0 Not First
4 Carol F 14 62.8 102.5 Not First
5 Henry M 14 63.5 102.5 Not First
6 James M 12 57.3 83.0 Not First
7 Jane F 12 59.8 84.5 Not First
8 Janet F 15 62.5 112.5 Not First
9 Jeffrey M 13 62.5 84.0 Not First
10 John M 12 59.0 99.5 Not First
11 Joyce F 11 51.3 50.5 Not First
12 Judy F 14 64.3 90.0 Not First
13 Louise F 12 56.3 77.0 Not First
14 Mary F 15 66.5 112.0 Not First
15 Philip M 16 72.0 150.0 Not First
16 Robert M 12 64.8 128.0 Not First
17 Ronald M 15 67.0 133.0 Not First
18 Thomas M 11 57.5 85.0 Not First
19 William M 15 66.5 112.0 Not First
The monotonic() function can be extremely useful when having to create or work with sequential numbers within SQL code. As it is an undocumented function it is recommended to use within simple queries.