Ordering Rows

Overview

The order of rows in the output of a PROC SQL query cannot be guaranteed, unless you specify a sort order. To sort rows by the values of specific columns, you can use the ORDER BY clause in the SELECT statement. Specify the keywords ORDER BY, followed by one or more column names separated by commas.
In the following PROC SQL query, the ORDER BY clause sorts rows by values of the column JobCode: 
proc sql;
   select empid,jobcode,salary,
          salary*.06 as bonus
     from sasuser.payrollmaster
     where salary<32000
      order by jobcode;
Note: In this example, the ORDER BY clause is the last clause in the SELECT statement, so the ORDER BY clause ends with a semicolon.
In the output of the sample query, shown below, the rows are sorted by the values of JobCode. By default, the ORDER BY clause sorts rows in ascending order.
Emplid, jobcode, salary, bonus
To sort rows in descending order, specify the keyword DESC following the column name. For example, the preceding ORDER BY clause could be modified as follows: 
order by jobcode desc;
In the ORDER BY clause, you can alternatively reference a column by the column's position in the SELECT clause list rather than by name. Use an integer to indicate the column's position. The ORDER BY clause in the preceding PROC SQL query has been modified, below, to specify the column JobCode by the column's position in the SELECT clause list (2) rather than by name: 
proc sql;
   select empid,jobcode,salary,
          salary*.06 as bonus
      from sasuser.payrollmaster
      where salary<32000
      order by 2;

Ordering by Multiple Columns

To sort rows by the values of two or more columns, list multiple column names (or numbers) in the ORDER BY clause, and use commas to separate the column names (or numbers). In the following PROC SQL query, the ORDER BY clause sorts by the values of two columns, JobCode and EmpID: 
proc sql;
   select empid,jobcode,salary,
          salary*.06 as bonus
      from sasuser.payrollmaster
      where salary<32000
      order by jobcode,empid;
The rows are sorted first by JobCode and then by EmpID, as shown in the following output.
Emplid, jobcode, salary, bonus
Note: You can mix the two types of column references, names and numbers, in the ORDER BY clause. For example, the preceding ORDER BY clause could be rewritten as follows: 
order by 2,empid;
You can also reference column aliases in the ORDER BY clause. Here is an example:
order by 2, empid, bonus;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.172.130