10.4 - Using Order Variables10.4 - Using Order Variables
Thus far, we have used the DEFINE statement to change the appearance of our reports, doing such things as specifiying the format of a variable, changing the width of a column, specifying column labels, and changing the justification of a column. The truth is that the DEFINE statement is even more powerful when it is used to tell SAS how you want SAS to use each variable that appears in your report. There are six possible usages — DISPLAY, ANALYSIS, ORDER, GROUP, ACROSS, and COMPUTED — which we'll investigate in the course of the next five sections of this lesson. By default, the REPORT procedure uses:
- character variables as display variables
- numeric variables as analysis variables, which are used to calculate the SUM statistic
Because we haven't explicitly defined any variable usages, the reports we have created thus far have contained only display and analysis variables. For example, here's the most recent report we created:
Some Pennsylvania Golf Courses
|Name||Year||Type of Course||Par||Total Yardage|
|Penn State Blue||1921||Public||72||6,525|
|State College Elks||1973||SemiPri||71||6,369|
|Park Hills CC||1966||SemiPri||70||6,004|
|Sinking Valley CC||1967||SemiPri||72||6,755|
|Standing Stone CC||1973||SemiPri||70||6,593|
|Mount Airy Lodge||1972||Resort||72||7,123|
Because the second, fourth and fifth columns, Year, Par, and Yards, are numeric variables, SAS treats them as analysis variables by default. Analysis variables are used to calculate a statistic ... by default a SUM.
Now, because the first and third columns, Name and Type, are character variables, SAS treats them as display variables by default. What impact does that have on the appearance of the report? It means three things: 1) A report that contains one or more display variables necessarily contains a detail row for each observation in the data set, 2) Each detail row contains a value for each display variable, and 3) The order of the rows in the report matches the order of the observations in the data set.
We can alter the order in which the rows appear in our report by defining an order variable (or two or three ...). An order variable orders the detail the rows in a report according to the variable's formatted values. Let's take a look at an example.
Among other things, the DEFINE statements in the following REPORT procedure defines the Yards and Par variables as being ORDER variables:
PROC REPORT data = stat480.penngolf NOWINDOWS HEADLINE; title 'Some Pennsylvania Golf Courses'; column Par Name Year Type Yards; define Yards / order format = comma5.0 'Total/Yardage' width = 7 spacing = 4 center; define Type / 'Type of/Course' spacing = 6 width = 8 center; define Par / order; RUN;
There are just two differences between this REPORT procedure and the one from the previous example:
- The word ORDER has been added to the DEFINE statements for the Yards and Par variables
- The order of the columns appearing in the COLUMN statement has been changed so that the Par variable is the first to appear in the report.
Launch and run the SAS program, and review the output. You should note that the report rows are now first in ascending order of the Par variable, and then within in each Par value the rows are in ascending order of the Yards variable. You should also note that SAS displays only the first occurrence of each value of an order variable in a set of rows that have the same value for all order variables. For example, the first three rows of output pertain to golf courses whose Par is 70, the next three rows of output pertain to golf courses whose Par is 71, and so on. One more thing ... we never told SAS to display the rows in ascending order. That's just what SAS does by default. We can change the order by using the DEFINE statement's DESCENDING option.
The following program tells SAS, when creating the requested report, to display the rows in descending order of the Par variable and in ascending order of the Yards variable:
PROC REPORT data = stat480.penngolf NOWINDOWS HEADLINE; title 'Some Pennsylvania Golf Courses'; column Par Name Year Type Yards; define Yards / order format = comma5.0 'Total/Yardage' width = 7 spacing = 4 center; define Type / 'Type of/Course' spacing = 6 width = 8 center; define Par / order descending; RUN;
Launch and run the SAS program, and review the output to convince yourself that the rows are indeed in the order as described.