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

by | Sep 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_;
Run;
 
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;
Set CARS;
By make;
retain Seq 0;
if first.make then seq = 0;
seq + 1;
Run;
 
So can you add a sequential number in EG just using Wizards? I don’t think so, if you can let me know.
 
 
n.b If you were to create a list report in EG with unsuppressed Obs and then export it to Excel and then import it straight back again you can achieve your objective with wizards only (List report, Export, Import) but it would be very silly!
Do you need help with SAS? Find answers in our SAS blogs
Go to our SAS Administration Page to find out how you can remove the worries from administering your SAS environments.

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *