O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required