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.
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;
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
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.
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;
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
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.
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;
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
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.
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;
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