SAS : Custom Sort Order

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Sept 25, 2017
- Batch starts from October 8, 2017

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.

SAS Tutorials : 100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

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