SAS : Custom Sort Order


SAS Analytics : Practical SAS, Statistics & Analysis Course

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.
Coursera Data Science

SAS Tutorials : 100 Free SAS Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

2 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

Next → ← Prev