Examples: SORT Procedure

Examples: SORT Procedure

Example 1: Sorting by the Values of Multiple Variables

Procedure features:

• PROC SORT statement option:

• OUT=

• BY statement

Other features:

• PROC PRINT

This example

• sorts the observations by the values of two variables

• creates an output data set for the sorted observations

• prints the results.

Program

Set the SAS system options. The NODATE option suppresses the display of the date and time in the output. PAGENO= specifies the starting page number. LINESIZE= specifies the output line length, and PAGESIZE= specifies the number of lines on an output page.

` options nodate pageno=1 linesize=80 pagesize=60; `

Create the input data set ACCOUNT. ACCOUNT contains the name of each business that owes money, the amount of money that it owes on its account, the account number, and the town where the business is located.

` data account;     input Company \$ 1-22 Debt 25-30 AccountNumber 33-36           Town \$ 39-51;     datalines;  Paul's Pizza             83.00  1019  Apex  World Wide Electronics  119.95  1122  Garner  Strickland Industries   657.22  1675  Morrisville  Ice Cream Delight       299.98  2310  Holly Springs  Watson Tabor Travel      37.95  3131  Apex  Boyd & Sons Accounting  312.49  4762  Garner  Bob's Beds              119.95  4998  Morrisville  Tina's Pet Shop          37.95  5108  Apex  Elway Piano and Organ    65.79  5217  Garner  Tim's Burger Stand      119.95  6335  Holly Springs  Peter's Auto Parts       65.79  7288  Apex  Deluxe Hardware         467.12  8941  Garner  Pauline's Antiques      302.05  9112  Morrisville  Apex Catering            37.95  9923  Apex  ; `

Create the output data set BYTOWN. OUT= creates a new data set for the sorted observations.

` proc sort data=account out=bytown; `

Sort by two variables. The BY statement specifies that the observations should be first ordered alphabetically by town and then by company.

` by town company;  run; `

Print the output data set BYTOWN. PROC PRINT prints the data set BYTOWN.

` proc print data=bytown; `

Specify the variables to print. The VAR statement specifies the variables to print and their column order in the output.

` var company town debt accountnumber; `

Specify the titles.

` title  'Customers with Past-Due Accounts';     title2 'Listed Alphabetically within Town';  run; `

Output

` Customers with Past-Due Accounts                      1                   Listed Alphabetically within Town                                                              Account  Obs    Company                   Town              Debt      Number    1    Apex Catering             Apex              37.95      9923    2    Paul's Pizza              Apex              83.00      1019    3    Peter's Auto Parts        Apex              65.79      7288    4    Tina's Pet Shop           Apex              37.95      5108    5    Watson Tabor Travel       Apex              37.95      3131    6    Boyd & Sons Accounting    Garner           312.49      4762    7    Deluxe Hardware           Garner           467.12      8941    8    Elway Piano and Organ     Garner            65.79      5217    9    World Wide Electronics    Garner           119.95      1122   10    Ice Cream Delight         Holly Springs    299.98      2310   11    Tim's Burger Stand        Holly Springs    119.95      6335   12    Bob's Beds                Morrisville      119.95      4998   13    Pauline's Antiques        Morrisville      302.05      9112   14    Strickland Industries     Morrisville      657.22      1675 `

Example 2: Sorting in Descending Order

Procedure features:

• This example BY statement option:

• DESCENDING

Other features

• PROC PRINT

Data set: ACCOUNT on page 1031

• sorts the observations by the values of three variables

• sorts one of the variables in descending order

• prints the results.

Program

Set the SAS system options. The NODATE option suppresses the display of the date and time in the output. PAGENO= specifies the starting page number. LINESIZE= specifies the output line length, and PAGESIZE= specifies the number of lines on an output page.

` options nodate pageno=1 linesize=80 pagesize=60; `

Create the output data set SORTED. OUT= creates a new data set for the sorted observations.

` proc sort data=account out=sorted; `

Sort by three variables with one in descending order. The BY statement specifies that observations should be first ordered alphabetically by town, then by descending value of amount owed, then by ascending value of the account number.

` by town descending debt accountnumber;  run; `

Print the output data set SORTED. PROC PRINT prints the data set SORTED.

` proc print data=sorted; `

Specify the variables to print. The VAR statement specifies the variables to print and their column order in the output.

` var company town debt accountnumber; `

Specify the titles.

` title  'Customers with Past-Due Accounts';     title2 'Listed by Town, Amount, Account Number';  run; `

Output

Note that sorting last by AccountNumber puts the businesses in Apex with a debt of \$37.95 in order of account number.

` Customers with Past-Due Accounts                      1                Listed by Town, Amount, Account Number                                                             Account  Obs   Company                   Town              Debt      Number    1   Paul's Pizza              Apex              83.00      1019    2   Peter's Auto Parts        Apex              65.79      7288    3   Watson Tabor Travel       Apex              37.95      3131    4   Tina's Pet Shop           Apex              37.95      5108    5   Apex Catering             Apex              37.95      9923    6   Deluxe Hardware           Garner           467.12      8941    7   Boyd & Sons Accounting    Garner           312.49      4762    8   World Wide Electronics    Garner           119.95      1122    9   Elway Piano and Organ     Garner            65.79      5217   10   Ice Cream Delight         Holly Springs    299.98      2310   11   Tim's Burger Stand        Holly Springs    119.95      6335   12   Strickland Industries     Morrisville      657.22      1675   13   Pauline's Antiques        Morrisville      302.05      9112   14   Bob's Beds                Morrisville      119.95      4998 `

Example 3: Maintaining the Relative Order of Observations in Each BY Group

Procedure features:

• PROC SORT statement option:

• EQUALSNOEQUALS

Other features: PROC PRINT

This example

• sorts the observations by the value of the first variable

• maintains the relative order with the EQUALS option

• does not maintain the relative order with the NOEQUALS option.

Program

Set the SAS system options. The NODATE option suppresses the display of the date and time in the output. PAGENO= specifies the starting page number. LINESIZE= specifies the output line length, and PAGESIZE= specifies the number of lines on an output page.

` options nodate pageno=1 linesize=80 pagesize=60; `

Create the input data set INSURANCE. INSURANCE contains the number of years worked by all insured employees and their insurance ids.

` data insurance;     input YearsWorked 1 InsuranceID 3-5;     datalines;  5 421  5 336  1 209  1 564  3 711  3 343  4 212  4 616  ; `

Create the output data set BYYEARS1 with the EQUALS option. OUT= creates a new data set for the sorted observations. The EQUALS option maintains the order of the observations relative to each other.

` proc sort data=insurance out=byyears1 equals; `

Sort by the first variable. The BY statement specifies that the observations should be ordered numerically by the number of years worked.

` by yearsworked;  run; `

Print the output data set BYYEARS1. PROC PRINT prints the data set BYYEARS1.

` proc print data=byyears1; `

Specify the variables to print. The VAR statement specifies the variables to print and their column order in the output.

` var yearsworked insuranceid; `

Specify the title.

` title 'Sort with EQUALS';  run; `

Create the output data set BYYEARS2. OUT= creates a new data set for the sorted observations. The NOEQUALS option will not maintain the order of the observations relative to each other.

` proc sort data=insurance out=byyears2 noequals; `

Sort by the first variable. The BY statement specifies that the observations should be ordered numerically by the number of years worked.

` by yearsworked;  run; `

Print the output data set BYYEARS2. PROC PRINT prints the data set BYYEARS2.

` proc print data=byyears2; `

Specify the variables to print. The VAR statement specifies the variables to print and their column order in the output.

` var yearsworked insuranceid; `

Specify the title.

` title 'Sort with NOEQUALS';  run; `

Output

Note that sorting with the EQUALS option versus sorting with the NOEQUALS option causes a different sort order for the observations where YearsWorked=3.

` Sort with EQUALS                    1          Years    Insurance  Obs    Worked        ID   1        1         209   2        1         564   3        3         711   4        3         343   5        4         212   6        4         616   7        5         421   8        5         336 `
` Sort with NOEQUALS                   2          Years    Insurance  Obs    Worked        ID   1        1         209   2        1         564   3        3         343   4        3         711   5        4         212   6        4         616   7        5         421   8        5         336 `

Example 4: Retaining the First Observation of Each BY Group

Procedure features:

• PROC SORT statement option:

• NODUPKEY

• BY statement

Other features:

• PROC PRINT

Data set: ACCOUNT on page 1031

Interaction: The EQUALS option, which is the default, must be in effect to ensure that the first observation for each BY group is the one that is retained by the NODUPKEY option. If the NOEQUALS option has been specified, then one observation for each BY group will still be retained by the NODUPKEY option, but not necessarily the first observation.

In this example, PROC SORT creates an output data set that contains only the first observation of each BY group. The NODUPKEY option prevents an observation from being written to the output data set when its BY value is identical to the BY value of the last observation written to the output data set. The resulting report contains one observation for each town where the businesses are located.

Program

Set the SAS system options. The NODATE option suppresses the display of the date and time in the output. PAGENO= specifies the starting page number. LINESIZE= specifies the output line length, and PAGESIZE= specifies the number of lines on an output page.

` options nodate pageno=1 linesize=80 pagesize=60; `

Create the output data set TOWNS but include only the first observation of each BY group. NODUPKEY writes only the first observation of each BY group to the new data set TOWNS.

Operating Environment Information: If you use the VMS operating environment sort, then the observation that is written to the output data set is not always the first observation of the BY group.

` proc sort data=account out=towns nodupkey; `

Sort by one variable. The BY statement specifies that observations should be ordered by town.

` by town;  run; `

Print the output data set TOWNS. PROC PRINT prints the data set TOWNS.

` proc print data=towns; `

Specify the variables to print. The VAR statement specifies the variables to print and their column order in the output.

` var town company debt accountnumber; `

Specify the title.

` title 'Towns of Customers with Past-Due Accounts';  run; `

Output

The output data set contains only four observations, one for each town in the input data set.

` Towns of Customers with Past-Due Accounts                  1                                                              Account  Obs    Town             Company                    Debt      Number   1     Apex             Paul's Pizza               83.00      1019   2     Garner           World Wide Electronics    119.95      1122   3     Holly Springs    Ice Cream Delight         299.98      2310   4     Morrisville      Strickland Industries     657.22      1675 `

Base SAS 9.1 Procedures Guide, Volumes 1, 2, 3 and 4
ISBN: 1590472047
EAN: 2147483647
Year: 2004
Pages: 260