Generating Column Totals

The SUM Statement

To produce column totals for numeric variables, you can list the variables to be summed in a SUM statement in your PROC PRINT step.
Syntax, SUM statement:
SUM variable(s);
variable(s) is one or more numeric variable names, separated by blanks.
The SUM statement in the following PROC PRINT step requests column totals for the variable BalanceDue: 
proc print data=cert.insure; 
  var name policy balancedue; 
  where pctinsured < 100;  
  sum balancedue; 
run;
Column totals appear at the end of the report in the same format as the values of the variables.
Figure 6.11 Column Totals
Column Totals
Note: If you specify the same variable in the VAR statement and the SUM statement, you can omit the variable name in the VAR statement. If a SUM variable is not specified in the VAR statement, the variable to be summed is added to the output in the order in which it appears in the SUM statement.

Creating Subtotals for Variable Groups

You might also want to group and subtotal numeric variables. You group variables using the BY statement. SAS calls these groups BY groups. You can use the SUM statement to create a subtotal value for variables in the group.
Syntax, BY statement in the PRINT procedure:
BY <DESCENDING> BY-variable-1
<...<DESCENDING> <BY-variable-n>>
<NOTSORTED>;
  • BY-variable specifies a variable that the procedure uses to form BY groups. You can specify more than one variable, separated by blanks.
  • The DESCENDING option specifies that the data set is sorted in descending order by the variable that immediately follows.
  • The NOTSORTED option specifies that the observations in the data set that have the same BY values are grouped together, but are not necessarily sorted in alphabetical or numeric order. For example, the observations might be sorted in chronological order using a date format such as DDMMYY. If observations that have the same values for the BY variables are not contiguous, the procedure treats each contiguous set as a separate BY group.
Note: The NOTSORTED option applies to all of the variables in the BY statement. You can specify the NOTSORTED option anywhere within the BY statement. The requirement for ordering or indexing observations according to the values of BY variables is suspended when you use the NOTSORTED option.
When you sort the data set, you must use the same BY variable in PROC SORT as you do in PROC PRINT.

Example: SUM Statement

The following example uses the SUM statement and the BY statement to generate subtotals for each BY group and a sum of all of the subtotals of the Fee variable. 
proc sort data=cert.admit out=work.activity;     /*#1*/
  by actlevel;
run;
proc print data=work.activity;
  var age height weight fee;
  where age>30;
  sum fee;                                       /*#2*/
  by actlevel;                                   /*#3*/
run;
1 The PROC SORT step sorts the permanent SAS data set Cert.Admit by the values of the variable ActLevel. The OUT= option creates the temporary SAS data set Activity.
2 The SUM statement produces column totals for the numeric variable Fee.
3 The BY statement specifies ActLevel as the variable that PROC PRINT uses to form BY groups.
In the output, the BY variable name and value appear before each BY group. The BY variable name and the subtotal appear at the end of each BY group.
Figure 6.12 BY-Group Output: High
BY Group Output: High
Figure 6.13 BY-Group Output: Low
BY Group Output: Low
Figure 6.14 BY-Group Output: Mod
BY Group Output: Mod

Creating a Customized Layout with BY Groups and ID Variables

In the previous example, you might have noticed the redundant information for the BY variable. For example, in the PROC PRINT output below, the BY variable ActLevel is identified both before the BY group and for the subtotal.
Figure 6.15 Creating a Customized Layout with BY Groups and ID Variables
Creating a Customized Layout with BY Groups and ID Variables
To show the BY variable heading only once, use an ID statement and a BY statement together with the SUM statement. Here are the results when an ID statement specifies the same variable as the BY statement:
  • The Obs column is suppressed.
  • The ID or BY variable is printed in the left-most column.
  • Each ID or BY value is printed only at the start of each BY group and on the line that contains that group's subtotal.

Example: ID, BY, and SUM Statements

The ID, BY, and SUM statements work together to create the output shown below. 
proc sort data=cert.admit out=work.activity;    /*#1*/
  by actlevel;
run;
proc print data=work.activity;
  var age height weight fee;
  where age>30;
  sum fee;                                      /*#2*/
  by actlevel;                                  /*#3*/
  id actlevel;                                  /*#4*/
run;
1 The PROC SORT step sorts the permanent SAS data set Cert.Admit by the values of the variable ActLevel. The OUT= option creates the temporary SAS data set Activity.
2 The SUM statement produces column totals for the numeric variable Fee.
3 The BY statement specifies ActLevel as the variable that PROC PRINT uses to form BY groups.
4 The ID statement specifies ActLevel as the variable that replaces the Obs column and listed only once for each BY group and once for each sum. The BY lines are suppressed, and the values of the ID statement variable ActLevel identify each BY group.
Output 6.2 Creating Custom Output Example Output
Creating Custom Output Example Output

Creating Subtotals on Separate Pages

As another enhancement to your PROC PRINT report, you can request that each BY group be printed on a separate page by using the PAGEBY statement.
Syntax, PAGEBY statement:
PAGEBY BY-variable:
BY-variable identifies a variable that appears in the BY statement in the PROC PRINT step. PROC PRINT begins printing a new page if the value of the BY variable changes, or if the value of any BY variable that precedes it in the BY statement changes.
Note: The variable specified in the PAGEBY statement must also be specified in the BY statement in the PROC PRINT step.

Example: PAGEBY Statement

The PAGEBY statement prints each BY group on a separate page. The following example uses the PAGEBY statement to print the BY groups for the variable ActLevel on separate pages. The BY groups are separated by horizontal lines in the HTML output. 
proc sort data=cert.admit out=work.activity;
  by actlevel;
run;
proc print data=work.activity;
  var age height weight fee;
  where age>30;
  sum fee;
  by actlevel;
  id actlevel;
  pageby actlevel;
run;
Output 6.3 PAGEBY Example Output
PAGEBY Example Output
Last updated: August 23, 2018
..................Content has been hidden....................

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