Practical Problem-Solving with PROC SQL

Creating a Customized Sort Order

You want to sort data in a logical, but not alphabetical, sequence.

There is one input table, called CHORES, that contains the following data:

                                 Garden Chores

                          Project      Hours  Season
                          ----------------------------
                          weeding         48  summer  
                          pruning         12  winter  
                          mowing          36  summer  
                          mulching        17  fall    
                          raking          24  fall    
                          raking          16  spring  
                          planting         8  spring  
                          planting         8  fall    
                          sweeping         3  winter  
                          edging          16  summer  
                          seeding          6  spring  
                          tilling         12  spring  
                          aerating         6  spring  
                          feeding          7  summer  
                          rolling          4  winter

You want to reorder this chore list so that all the chores are grouped by season, starting with spring and progressing through the year. Simply ordering by Season makes the list appear in alphabetical sequence: fall, spring, summer, winter.

Solution

Use the following PROC SQL code to create a new column, Sorter, that will have values of 1 through 4 for the seasons spring through winter. Use the new column to order the query, but do not select it to appear:

options nodate nonumber linesize=80 pagesize=60;

 proc sql;
    title 'Garden Chores by Season in Logical Order';
    select Project, Hours, Season
       from (select Project, Hours, Season,
               case
                  when Season = 'spring' then 1
                  when Season = 'summer' then 2
                  when Season = 'fall' then 3
                  when Season = 'winter' then 4
                  else .
               end as Sorter
               from chores)
       order by Sorter;

PROC SQL Output for a Customized Sort Sequence

                    Garden Chores by Season in Logical Order

                          Project      Hours  Season
                          ----------------------------
                          tilling         12  spring  
                          raking          16  spring  
                          planting         8  spring  
                          seeding          6  spring  
                          aerating         6  spring  
                          mowing          36  summer  
                          feeding          7  summer  
                          edging          16  summer  
                          weeding         48  summer  
                          raking          24  fall    
                          mulching        17  fall    
                          planting         8  fall    
                          rolling          4  winter  
                          pruning         12  winter  
                          sweeping         3  winter

How It Works

This solution uses an in-line view to create a temporary column that can be used as an ORDER BY column. The in-line view is a query that

selects the Project, Hours, and Season columns

uses a CASE expression to remap the seasons to the new column Sorter: spring to 1, summer to 2, fall to 3, and winter to 4.

 (select project, hours, season,
         case
            when season = 'spring' then 1
            when season = 'summer' then 2
            when season = 'fall' then 3
            when season = 'winter' then 4
            else .
         end as sorter
     from chores)

The first, or outer, SELECT statement in the query

selects the Project, Hours and Season columns
orders rows by the values that were assigned to the seasons in the Sorter column that was created with the in-line view.

Notice that the Sorter column is not included in the SELECT statement. That causes a note to be written to the log indicating that you have used a column in an ORDER BY statement that does not appear in the SELECT statement. In this case, that is exactly what you wanted to do.

Top of Page