SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

when month(InvoiceDate)=3 then

InvoiceAmount end as Mar

Creating a Customized Sort Order

Problem

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

Background Information

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

data chores;

input Project $ Hours Season $;

datalines;

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

;

proc sql;

title 'Garden Chores';

select * from chores;

quit;

Creating a Customized Sort Order 197

Output 6.17 Sample Input Data for a Customized Sort

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:

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;

198 Chapter 6 • Practical Problem-Solving with PROC SQL

Output 6.18 PROC SQL Output for a Customized Sort Sequence

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 performs the following:

• 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 performs the following:

• selects the Project, Hours, and Season columns

Creating a Customized Sort Order 199