SAS: Customize your sorting order in Proc SQL

Welcome to Klick2learnMore……


In this post we will learn how to customize sorting order of a variable in SAS.


We know that we can sort a variable in either ascending or descending order. But few times we get the requirement of sorting a variable/column in our own customized order which is neither ASCENDING nor DESCENDING…


Suppose you are creating a table using PROC SQL and in that table you have 4 columns Name, City, Sex, Amount

Now suppose your table looks like below:

Name              City          Sex          Amount

Anant               Delhi         M             1000

Girish               Pune         M             1500

Manu               Delhi         M             1800

Punya              Jaipur       M             2600

Prith                Chennai    M             1300

Natasha           Delhi        M              7500


Now you want to sort Name column in your customized order like it should be in sequence like this (Natasha, Manu, Anant, Prith, Girish, Punya)

This is neither an ASCENDING order nor DESCENDING..

So how will you write Proc SQL so that it creates a table giving names in mentioned customized order.

Lets learn how to do it:

To get the output in customized order, we will have to create a FORMAT first.

To create a format use below mentioned code:

proc format;

value $rank

“Natasha” = 1

“Manu” = 2

“Anant” = 3

“Prith” = 4

“Girish” = 5

“Punya” = 6;


(Note: instead of rank you can use any name, actually rank is the name of the format, it can be anything else also)

Now when we have created a format, we can use this format in our any PROC SQL code, to apply customized sorting like shown below:

Proc Sql;

Select * from mydata

order by put(Name, $rank.);


(Note: mydata is the source table from which we are fetching these columns)

In the output table created by above code, name will be in the sequence which we wanted like (Natasha, Manu, Anant, Prith, Girish, Punya)

Hope this will help you.

Feel free to comment if you face any issue.

If you like this post then please click on like button and share it with others also by clicking on share button mentioned below.


Please follow and like us:
Follow by Email

Leave a Reply

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