In this tutorial, we will cover how to apply 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'.
Sample Data
The following program would be used to create a sample data.
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.
 |
| Custom Sort Order in SAS |
 |
| 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;The SQL 'CASE WHEN' syntax is an alternative to IF THEN ELSE statement in SAS.
select * from temp
order by case when group = 'High' then 1
when group = 'Medium' then 2
when group = 'Low' then 3 end;
quit;
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.
can you give a simple explanation. of the use of put in method 1
ReplyDeleteCheck out this article - http://www.listendata.com/2016/03/sas-power-of-proc-format.html
DeleteDeepanshu,can you please elaborate how to sort the dataset without using any proc ?
ReplyDeleteDoes not work with 10 or more ranks.
ReplyDeleteHi, I am not able to sort below data:
ReplyDeletedata 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?