Producing Detail Reports with the PRINT Procedure |
Showing All the Variables |
By default, the PRINT procedure generates a simple report that shows the values of all the variables and the observations in the data set. For example, the following PROC PRINT step creates a report for the first sales quarter:
options linesize=80 pageno=1 nodate; proc print data=qtr01; title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
The following output shows the values of all the variables for all the observations in QTR01:
Showing All Variables and All Observations
TruBlend Coffee Makers Quarterly Sales Report2 1 Amount Obs1 Month Quarter SalesRep Type Units Price Sold 1 01 1 Hollingsworth Deluxe 260 49.50 12870.00 2 01 1 Garcia Standard 41 30.97 1269.77 3 01 1 Hollingsworth Standard 330 30.97 10220.10 4 01 1 Jensen Standard 110 30.97 3406.70 5 01 1 Garcia Deluxe 715 49.50 35392.50 6 01 1 Jensen Standard 675 30.97 20904.75 7 02 1 Garcia Standard 2045 30.97 63333.65 8 02 1 Garcia Deluxe 10 49.50 495.00 9 02 1 Garcia Standard 40 30.97 1238.80 10 02 1 Hollingsworth Standard 1030 30.97 31899.10 11 02 1 Jensen Standard 153 30.97 4738.41 12 02 1 Garcia Standard 98 30.97 3035.06 13 03 1 Hollingsworth Standard 125 30.97 3871.25 14 03 1 Jensen Standard 154 30.97 4769.38 15 03 1 Garcia Standard 118 30.97 3654.46 16 03 1 Hollingsworth Standard 25 30.97 774.25 17 03 1 Jensen Standard 525 30.97 16259.25 18 03 1 Garcia Standard 310 30.97 9600.70
The following list corresponds to the numbered items in the preceding output:
The Obs column identifies each observation by a number. By default, SAS automatically displays the observation number at the beginning of each row. | |
The content of the report is very similar to the contents of the original data set QTR01; however, the report is easy to produce and to enhance.
Labeling the Observation Column |
A quick way to modify the report is to label the observation number (Obs column). The following SAS program includes the OBS= option in the PROC PRINT statement to change the column label for the Obs column:
options linesize=80 pageno=1 nodate; proc print data=qtr01 obs='Observation Number'; title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
The following output shows the report:
Labeling the Observation Column
TruBlend Coffee Makers Quarterly Sales Report 1 Observation Amount Number Month Quarter SalesRep Type Units Price Sold 1 01 1 Hollingsworth Deluxe 260 49.50 12870.00 2 01 1 Garcia Standard 41 30.97 1269.77 3 01 1 Hollingsworth Standard 330 30.97 10220.10 4 01 1 Jensen Standard 110 30.97 3406.70 5 01 1 Garcia Deluxe 715 49.50 35392.50 6 01 1 Jensen Standard 675 30.97 20904.75 7 02 1 Garcia Standard 2045 30.97 63333.65 8 02 1 Garcia Deluxe 10 49.50 495.00 9 02 1 Garcia Standard 40 30.97 1238.80 10 02 1 Hollingsworth Standard 1030 30.97 31899.10 11 02 1 Jensen Standard 153 30.97 4738.41 12 02 1 Garcia Standard 98 30.97 3035.06 13 03 1 Hollingsworth Standard 125 30.97 3871.25 14 03 1 Jensen Standard 154 30.97 4769.38 15 03 1 Garcia Standard 118 30.97 3654.46 16 03 1 Hollingsworth Standard 25 30.97 774.25 17 03 1 Jensen Standard 525 30.97 16259.25 18 03 1 Garcia Standard 310 30.97 9600.70
Suppressing the Observation Column |
A quick way to simplify the report is to suppress the observation number (Obs column). Usually it is unnecessary to identify each observation by number. (In some cases, you might want to show the observation numbers.) The following SAS program includes the NOOBS option in the PROC PRINT statement to suppress the Obs column:
options linesize=80 pageno=1 nodate; proc print data=qtr01 noobs; title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
The following output shows the report:
Suppressing the Observation Column
TruBlend Coffee Makers Quarterly Sales Report 1 Amount Month Quarter SalesRep Type Units Price Sold 01 1 Hollingsworth Deluxe 260 49.50 12870.00 01 1 Garcia Standard 41 30.97 1269.77 01 1 Hollingsworth Standard 330 30.97 10220.10 01 1 Jensen Standard 110 30.97 3406.70 01 1 Garcia Deluxe 715 49.50 35392.50 01 1 Jensen Standard 675 30.97 20904.75 02 1 Garcia Standard 2045 30.97 63333.65 02 1 Garcia Deluxe 10 49.50 495.00 02 1 Garcia Standard 40 30.97 1238.80 02 1 Hollingsworth Standard 1030 30.97 31899.10 02 1 Jensen Standard 153 30.97 4738.41 02 1 Garcia Standard 98 30.97 3035.06 03 1 Hollingsworth Standard 125 30.97 3871.25 03 1 Jensen Standard 154 30.97 4769.38 03 1 Garcia Standard 118 30.97 3654.46 03 1 Hollingsworth Standard 25 30.97 774.25 03 1 Jensen Standard 525 30.97 16259.25 03 1 Garcia Standard 310 30.97 9600.70
Emphasizing a Key Variable |
To emphasize a key variable in a data set, you can use the ID statement in the PROC PRINT step. When you identify a variable in the ID statement, PROC PRINT displays the values of this variable in the first column of each row of the report. Highlighting a key variable in this way can help answer questions about your data. For example, the report can answer this question: "For each sales representative, what are the sales figures for the first quarter of the year?" The following two examples demonstrate how to answer this question quickly using data that is unsorted and sorted.
To produce a report that emphasizes the sales representative, the PROC PRINT step includes an ID statement that specifies the variable SalesRep. The revised program follows:
options linesize=80 pageno=1 nodate; proc print data=qtr01; id SalesRep; title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
Because the ID statement automatically suppresses the observation numbers, the NOOBS option is not needed in the PROC PRINT statement.
The following output shows the new report:
Using the ID Statement with an Unsorted Key Variable
TruBlend Coffee Makers Quarterly Sales Report 1 Amount SalesRep Month Quarter Type Units Price Sold Hollingsworth 01 1 Deluxe 260 49.50 12870.00 Garcia 01 1 Standard 41 30.97 1269.77 Hollingsworth 01 1 Standard 330 30.97 10220.10 Jensen 01 1 Standard 110 30.97 3406.70 Garcia 01 1 Deluxe 715 49.50 35392.50 Jensen 01 1 Standard 675 30.97 20904.75 Garcia 02 1 Standard 2045 30.97 63333.65 Garcia 02 1 Deluxe 10 49.50 495.00 Garcia 02 1 Standard 40 30.97 1238.80 Hollingsworth 02 1 Standard 1030 30.97 31899.10 Jensen 02 1 Standard 153 30.97 4738.41 Garcia 02 1 Standard 98 30.97 3035.06 Hollingsworth 03 1 Standard 125 30.97 3871.25 Jensen 03 1 Standard 154 30.97 4769.38 Garcia 03 1 Standard 118 30.97 3654.46 Hollingsworth 03 1 Standard 25 30.97 774.25 Jensen 03 1 Standard 525 30.97 16259.25 Garcia 03 1 Standard 310 30.97 9600.70Notice that the names of the sales representatives are not in any particular order. The report will be easier to read when the observations are grouped together in alphabetical order by sales representative.
If your data is not already ordered by the key variable, then use PROC SORT to sort the observations by this variable. If you do not specify an output data set, then PROC SORT permanently changes the order of the observations in the input data set.
The following program shows how to alphabetically order the observations by sales representative:
options linesize=80 pageno=1 nodate; proc sort data=qtr01;1 by SalesRep;2 run; proc print data=qtr01; id SalesRep;3 title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
The following list corresponds to the numbered items in the preceding program:
The following output shows the report:
Using the ID Statement with a Sorted Key Variable
TruBlend Coffee Makers Quarterly Sales Report 1 Amount SalesRep Month Quarter Type Units Price Sold Garcia 01 1 Standard 41 30.97 1269.77 Garcia 01 1 Deluxe 715 49.50 35392.50 Garcia 02 1 Standard 2045 30.97 63333.65 Garcia 02 1 Deluxe 10 49.50 495.00 Garcia 02 1 Standard 40 30.97 1238.80 Garcia 02 1 Standard 98 30.97 3035.06 Garcia 03 1 Standard 118 30.97 3654.46 Garcia 03 1 Standard 310 30.97 9600.70 Hollingsworth 01 1 Deluxe 260 49.50 12870.00 Hollingsworth 01 1 Standard 330 30.97 10220.10 Hollingsworth 02 1 Standard 1030 30.97 31899.10 Hollingsworth 03 1 Standard 125 30.97 3871.25 Hollingsworth 03 1 Standard 25 30.97 774.25 Jensen 01 1 Standard 110 30.97 3406.70 Jensen 01 1 Standard 675 30.97 20904.75 Jensen 02 1 Standard 153 30.97 4738.41 Jensen 03 1 Standard 154 30.97 4769.38 Jensen 03 1 Standard 525 30.97 16259.25Now, the report clearly shows what each sales representative sold during the first three months of the year.
Reporting the Values of Selected Variables |
By default, the PRINT procedure reports the values of all the variables in the data set. However, to control which variables are shown and in what order, add a VAR statement to the PROC PRINT step.
For example, the information for the variables Quarter, Type, and Price is unnecessary. Therefore, the report needs to show only the values of the variables that are specified in the following order:
SalesRep Month Units AmountSold
The following program adds the VAR statement to create a report that lists the values of the four variables in a specific order:
options linesize=80 pageno=1 nodate; proc print data=qtr01 noobs; var SalesRep Month Units AmountSold; title 'TruBlend Coffee Makers Quarterly Sales Report'; run;
This program does not include the ID statement. It is unnecessary to identify the observations because the variable SalesRep is the first variable that is specified in the VAR statement. The NOOBS option in the PROC PRINT statement suppresses the observation numbers so that the sales representative appears in the first column of the report.
The following output shows the report:
TruBlend Coffee Makers Quarterly Sales Report 1 Amount SalesRep Month Units Sold Hollingsworth 01 260 12870.00 Garcia 01 41 1269.77 Hollingsworth 01 330 10220.10 Jensen 01 110 3406.70 Garcia 01 715 35392.50 Jensen 01 675 20904.75 Garcia 02 2045 63333.65 Garcia 02 10 495.00 Garcia 02 40 1238.80 Hollingsworth 02 1030 31899.10 Jensen 02 153 4738.41 Garcia 02 98 3035.06 Hollingsworth 03 125 3871.25 Jensen 03 154 4769.38 Garcia 03 118 3654.46 Hollingsworth 03 25 774.25 Jensen 03 525 16259.25 Garcia 03 310 9600.70The report is concise because it contains only those variables that are specified in the VAR statement. The next example revises the report to show only those observations that satisfy a particular condition.
Selecting Observations |
To select observations that meet a particular condition from a data set, use a WHERE statement. The WHERE statement subsets the input data by specifying certain conditions that each observation must meet before it is available for processing.
The condition that you define in a WHERE statement is an arithmetic or logical expression that generally consists of a sequence of operands and operators.(footnote 1) To compare character values, you must enclose them in single or double quotation marks and the values must match exactly, including capitalization. You can also specify multiple comparisons that are joined by logical operators in the WHERE statement.
Using the WHERE statement might improve the efficiency of your SAS programs because SAS is not required to read all the observations in the input data set.
You can select observations based on a single comparison by using the WHERE statement. The following program uses a single comparison in a WHERE statement to produce a report that shows the sales activity for a sales representative named Garcia:
options linesize=80 pageno=1 nodate; proc print data=qtr01 noobs; var SalesRep Month Units AmountSold; where SalesRep='Garcia'; title 'TruBlend Coffee Makers Quarterly Sales for Garcia'; run;
In the WHERE statement, the value Garcia is enclosed in quotation marks because SalesRep is a character variable. In addition, the letter G in the value Garcia is uppercase so that it matches exactly the value in the data set QTR01.
The following output shows the report:
TruBlend Coffee Makers Quarterly Sales for Garcia 1 Sales Amount Rep Month Units Sold Garcia 01 41 1269.77 Garcia 01 715 35392.50 Garcia 02 2045 63333.65 Garcia 02 10 495.00 Garcia 02 40 1238.80 Garcia 02 98 3035.06 Garcia 03 118 3654.46 Garcia 03 310 9600.70
You can also select observations based on two or more comparisons by using the WHERE statement. However, when you use multiple WHERE statements in a PROC step, then only the last statement is used. You can create a compound comparison by using AND operator. For example, the following WHERE statement selects observations where Garcia sold only the deluxe coffee maker:
where SalesRep = 'Garcia' and Type='Deluxe' |
The following program uses two comparisons in a WHERE statement to produce a report that shows sales activities for a sales representative (Garcia) during the first month of the year:
options linesize=80 pageno=1 nodate; proc print data=year_sales noobs; var SalesRep Month Units AmountSold; where SalesRep='Garcia' and Month='01'; title 'TruBlend Coffee Makers Monthly Sales for Garcia'; run;
The WHERE statement uses the logical AND operator. Therefore, both comparisons must be true for PROC PRINT to include an observation in the report.
The following output shows the report:
TruBlend Coffee Makers Monthly Sales for Garcia 1 Sales Amount Rep Month Units Sold Garcia 01 41 1269.77 Garcia 01 715 35392.50
You might also want to select observations that meet at least one of several conditions. The following program uses two comparisons in the WHERE statement to create a report that shows every sale during the first quarter of the year that was greater than 500 units or more than $20,000:
options linesize=80 pageno=1 nodate; proc print data=qtr01 noobs; var SalesRep Month Units AmountSold; where Units>500 or AmountSold>20000; title 'Quarterly Report for Sales above 500 Units or $20,000'; run;
Notice this WHERE statement uses the logical OR operator. Therefore, only one of the comparisons must be true for PROC PRINT to include an observation in the report.
The following output shows the report:
Making Comparisons for One Condition or Another
Quarterly Report for Sales above 500 Units or $20,000 1 Amount SalesRep Month Units Sold Garcia 01 715 35392.50 Jensen 01 675 20904.75 Garcia 02 2045 63333.65 Hollingsworth 02 1030 31899.10 Jensen 03 525 16259.25
FOOTNOTE 1: The construction of the WHERE statement is similar to the construction of IF and IF-THEN statements.
Copyright © 2012 by SAS Institute Inc., Cary, NC, USA. All rights reserved.