Creating a custom sort order in SAS

Published on:
written bySubhro
SAS PROGRAMS, Tips & Tricks

Sometimes, we prefer to sort variables manually with a custom sort order rather than alphabetically or Numerical sequence.

For example, we have a variable called ‘DelayCategory’. It contains three unique values such as ‘1-10 Minutes’, ’11+ Minutes’ and ‘No Delay’.

You want to reorder this chore list so that all the chores are grouped by DelayCategory, starting with No Delay followed by  1-10 Minutes and then 11+ Minutes. Simply using Proc Sort to sort by DelayCategory makes the list appear in Numerical Sequence followed by alphabetical sequence: 1-10 Minute, 11+ Minutes, No Delay.

For more information, see our guide on Default Sorting Order of Characters in SAS.

Method 1:  Creating a custom sort order using Proc Format

The first solution is to create user-defined formats that map the DelayCategory to a number that represents the order in which the records will appear.

Download the example dataset from here.

proc format;
value $delayfmt
'No Delay' = 1
'1-10 Minutes' = 2
'11+ Minutes' = 3;
run;

The $delayfmt format is created to define the custom sort order.

BY variables in most SAS procedures including PROC SORT are sorted according to their internal values and not according to the formatted values.

In order, to use proc sort, we need an extra step to create the sort variable and use the new variable in Proc Sort By statement.

data flightdelays2;
set flightdelays;
neworder=put(delaycategory, delayfmt.);
run;

proc sort data=flightdelays2;
by neworder;
quit;

SAS offers alternatives to sort a dataset according to formatted values. You can use a proc SQL procedure to sort a dataset by formatted values. PROC SQL is similar to the data step and proc sort but is more powerful.

proc sql;
select * from flightdelays
order by put(delaycategory, $delayfmt.);
quit;

Here, the put function is used to apply the manual sort order and ORDER BY is used to sort the variable. This yields a sorted data according to the formatted values.

Method 2: Creating a custom sort order using SQL Case when Statement

proc sql;
select * from flightdelays
order by case when delaycategory = 'No Delay' then 1
when delaycategory = '1-10 Minutes' then 2
when delaycategory = '11+ Minutes' then 3 end;
quit;

This solution uses an in-line case when statements are used as an ORDER BY column.  The CASE expression to map the delaycategory variable.

You can download this entire code from here.

So, this was our side of Creating a custom sort order in SAS. We really hope that you must have found it useful.

Moreover, if you have any other suggestions suggest them in the comment section.

We would really take those lists in our further blog post.

Thanks for reading!

Please subscribe to our mailing list for weekly updates. You can also find us on Instagram and Facebook.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link
Powered by Social Snap