SAS : Custom Sort Order

Deepanshu Bhalla 5 Comments
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
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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

5 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
  4. Hi, I am not able to sort below data:

    data a;
    input customer:$10. Month:$20. Type:$10.;
    cards;
    A910 28-Feb-05 AUTO
    A312 16-Feb-05 SHOPPING
    A119 23-Feb-05 AIR TICKET
    A312 3-Feb-05 BUS TICKET
    ;
    run;

    I tried running the below code:
    proc sort data=a sortseq=linguistic;
    by customer
    run;

    but it didn't work. Can you please help?

    ReplyDelete
Next → ← Prev