How to create a sequence number in SAS – Beware the Monotonic

Sam Sewell
September 18, 2012

One of the great things about SAS is that there is normally a few viable ways of doing anything that you may want to do, this blog will cover the ways in which you can add a sequential number to a SAS dataset. My recommended method is to use a Data Step.
Very simply, SAS Data Steps work in such a way that they have an internal counter that counts the rows in your tables when they execute, this is represented by an automatic variable called _N_.
So to add a sequence number to a table use

Data out;
Set in;
Seqno = _N_;

So what other methods are there? Well one which comes up in a lot of Google searches is Monotonic(). This is a function you can use in SQL which is great because that means you can use it in a query wizard in Enterprise guide. Monotonic meaning  ‘preserved order’ will add a sequence number in any SQL code, however be aware that the monotonic function is not supported by SAS. And because of that I would not recommend you use it.  While it seems to work in all the tests I have done, SAS state that It can give missing or irregular values.
Of course there are many and more complicated ways of adding a sequence number, some of which you would need to use if you want to selectively add a sequence number, say for a by group. For this you could use you could use a retain statement as below. (I will blog about the retain statement soon)

Data out;
By make;
retain Seq 0;
if first.make then seq = 0;
seq + 1;

So can you add a sequential number in EG just using Wizards? I don’t think so, if you can let me know.

Copyright © 2019 OptimalBI LTD.