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:
“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:
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.