SAS : Custom Sort Order

In this tutorial, we will cover how to apply custom sort order in SAS.
Custom Sort Order in SAS

Most of the times, we want to sort variables manually with a custom sort order instead of alphabetically. For example, we have a variable called 'group'. It contains three unique values such as 'High', 'Low' and 'Medium'. We want values to be sort in such a way that 'High' appears first and then followed by 'Medium' and then 'Low'.
Custom Sort

Sample Data
The following program would be used to create a sample data.
data temp;
input group$;
cards;
High
Low
Medium
;
run;

What's wrong with PROC SORT? 

PROC SORT sorts the character variable alphabetically. PROC SORT would return 'High' in the first observation, followed by 'Low' and then 'Medium'. We want 'Medium' to be appeared in second observation and 'Low' in third observation.


Method 1 : Proc Format to define Sort Order
proc format;
value $rank
'High' = 1
'Medium' = 2
'Low' = 3;
run;
proc sql;
select * from temp
order by put(group, $rank.);
quit;
The $rank format is created to define custom sort order. The keyword $ is used to tell SAS the values are character. Later, put function is used to apply the manual sort order and ORDER BY is used to sort the variable.

Method 2 : Proc SQL CASE WHEN Method
proc sql;
select * from temp
order by case when group = 'High' then 1
when group = 'Medium' then 2
when group = 'Low' then 3 end;
quit;
The SQL 'CASE WHEN'  syntax is an alternative to IF THEN ELSE statement in SAS.
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

4 Responses to "SAS : Custom Sort Order"
  1. can you give a simple explanation. of the use of put in method 1

    ReplyDelete
    Replies
    1. Check out this article - http://www.listendata.com/2016/03/sas-power-of-proc-format.html

      Delete
  2. Deepanshu,can you please elaborate how to sort the dataset without using any proc ?

    ReplyDelete
  3. Does not work with 10 or more ranks.

    ReplyDelete

Next → ← Prev
Love this Post? Spread the Word!
Share