8 Ways to Calculate Column Total in SAS7 min read

If you work with tables, you can calculate the sum of a row or a column. In this article, we will discuss how to calculate the Column total in SAS.

Column total in SAS

1. Column Total in SAS using PROC SQL

To calculate column total in SAS, we will be using the SUM() function in Proc SQL. Let’s see an example.

We have used the EMPLOYEE Table in our example.

8 Ways to Calculate Column Total in SAS 1
proc SQl; 
select sum(salary) as Tot_Salary from sql.employees; 
quit;
8 Ways to Calculate Column Total in SAS 2

2. Column Total in SAS with a Data Step

You can calculate the sum of a column in a SAS data step. SAS Data step process the statements row by row, and this can be useful to calculate the total sum of a column using the sum function or the + operator.

SAS process statements row by row in the data step therefore it is necessary to know the sum of the previous rows to calculate the column total.

Using the RETAIN keyword, you can add the value of the current row to the sum of all previous rows. This way, SAS will create a dataset with a new column that contains the cumulative sum and at the end of the last row, you would have the column sum.

In the below code, Sum_Salary is a variable with the cumulative sum.

data sum_ex; 
set sql.employees; 
retain Sum_Salary; 
Sum_Salary = sum(Sum_Salary, salary); 
run;

Remember to use the retain statement to retain the value of the previous iteration as SAS will reset the counter of a newly created variable – Sum_Salary to 0 on each iteration.

8 Ways to Calculate Column Total in SAS 3

However, Retain statement is not required if you use the + Operator.

data sum_ex2;
 set sql.employees;
sum_salary+salary;
run;

3. Column Total in SAS using PROC SUMMARY

PROC SUMMARY procedure can also be used to analyze data and calculate descriptive statistics.

The VAR statement in PROC Summary is used to specify the variables which need to be analyzed. The OUTPUT OUT = statement names the output SAS data set.

proc summary data=sql.employees sum print;
var salary;
output out=Tot_Sal SUM(Salary)=Tot_Salary;
run;
8 Ways to Calculate Column Total in SAS 4

 

As you can see in the OUTPUT Proc Summary procedure creates two additional columns (_TYPE_ and _FREQ_). You can remove these two columns with the DROP=-option dataset. Check this article to learn more about dataset options and to remove columns in the SAS dataset.

You can change the name of the analysis variable by either adding a specific name or letting SAS take care of naming by using the /autoname option.

output out=Tot_Sal SUM=;
8 Ways to Calculate Column Total in SAS 5
output out=Tot_Sal SUM=/autoname;
8 Ways to Calculate Column Total in SAS 6

By Default, Proc Summary will not include a sum in the analysis result, so you need to include the sum explicitly after the output dataset.

If there are multiple analysis variables specified in the VAR statement, Proc Summary will calculate the sum for all those variables. Specify the variable name inside the sum to get the sum of a specific variable. This Can be useful if you want to calculate the sum of one variable and the mean of another variable.

For more information on PROC SUMMARY Procedure, see our guide on Proc Summary In SAS: Explained.

4. Column Total in SAS using PROC MEANS

PROC MEANS is another SAS procedure, which you can use to calculate the column sum. This procedure is very similar to PROC SUMMARY, except that Proc MEANS by default produces printed output in the LISTING window, whereas Proc SUMMARY does not.

Another difference is you can omit the var statement in Proc Means, and it will still calculate the sum but for each of the numeric variables in the input dataset.

proc means data=sql.employees sum;
run;
8 Ways to Calculate Column Total in SAS 7

For more details on PROC means, see our article on Using PROC MEANS For Detailed Analysis Of Data.

5. Column Sum in SAS using PROC UNIVARIATE

PROC UNIVARIATE is one of the most powerful SAS procedure for running descriptive statistics as well as checking important assumptions of various statistical techniques. Although this procedure is more for statistical purposes, you can still use it to calculate the sum of a column.

Column total in SAS

6. PROC PRINT

PROC PRINT Procedure can also be used to calculate the column totals but there will be no output dataset created.

print data=sql.employees noobs;
sum Salary;
run
8 Ways to Calculate Column Total in SAS 8

 

7. PROC TABULATE

The TABULATE procedure utilizes the SUM function to generate tabular reports. Below is the basic syntax to calculate Sum of Salaries.

proc tabulate data=sql.employees;
 Title 'Sum of Salaries';
 var salary ;
 table salary;
run
Column total in SAS
proc tabulate data=sql.employees; 
Title 'Sum of Salaries'; 
var salary ; 
table salary;
run

8. Column Total in SAS using the PROC REPORT Procedure

PROC REPORT is a procedure for creating detail and summary reports and output. In Proc Reports, there are a number of statements and options to choose from.

The COLUMNS statement tells SAS what variable(s) to display in the report. The DEFINE statement tells PROC REPORT how each variable will be used in the generated report output.

Proc Report data = sql.employees;
column Salary;
define Salary /analysis sum;
run;
Column total in SAS

You can even customize the output to include other additional variables in the report.

Proc Report data=sql.employees;
 column fname salary;
 define fname /display;
 define salary /analysis sum;
 rbreak after /summarize;  
 compute after ;
 fname="total";
 endcomp;
run;
Column total in SAS

We hoped this article helped you to calculate the Column total in SAS using the most common PROC SQL, Data Step and SAS Procedures.

PROC SQL provides the easiest way to get the column sum, while the other procedures give you more flexibility. The Data Step is more complex but shows you the cumulative sum too.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
Scroll to Top