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.

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;```

```                    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
```

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.

 Previous Page | Next Page | Top of Page