Sentry Page Protection
Proc SQL [9-14]
ORDER BY Clause
ORDER BY height;
The ORDER BY clause is used to sort the output.
Copy and run the SCHOOL table from the yellow box below if you haven't done so.
Example
Proc sql;
select *
from school
order by height;
quit;
select *
from school
order by height;
quit;
The ORDER BY clause sorts the output by the HEIGHT column in ascending order.
Descending Order
You can also sort the table in descending order by simply adding the keyword DESC after the column in the ORDER BY clause.
Example
Proc sql;
select *
from school
order by height desc;
quit;
select *
from school
order by height desc;
quit;
Combining Multiple Clauses
You can perform multiple tasks within a Proc SQL step by using multiple clauses.
Example
You can perform multiple tasks within a Proc SQL step by using multiple clauses.
Example
Proc sql;
select *,
case
when height>=60
then ">=60 inches"
else "<60 inches"
end as hflag
from school
where age in (13, 16)
order by height desc;
quit;
select *,
case
when height>=60
then ">=60 inches"
else "<60 inches"
end as hflag
from school
where age in (13, 16)
order by height desc;
quit;
In this Proc SQL step, there are four clauses:
This Proc SQL step performed the following tasks:
- Created the HFLAG column (SELECT clause).
- Specified the source of data (FROM clause).
- Selected students whose age is either 13 or 16 (WHERE clause)
- Sorted the output by HEIGHT in descending order (ORDER BY clause)
Exercise
Locate the CARS table from the SASHelp library.
Write a Proc SQL step to display the cars that cost more than $60,000. Sort the cars by MSRP in descending order.
Locate the CARS table from the SASHelp library.
Write a Proc SQL step to display the cars that cost more than $60,000. Sort the cars by MSRP in descending order.
Need some help?
HINT:
The keyword DESC is placed AFTER the column in the ORDER BY clause.
SOLUTION:
Proc sql;
select *
from sashelp.cars
where MSRP>60000
order by MSRP desc;
quit;
Fill out my online form.