If you work with tables, you can calculate the sum of a row or a column. This article will discuss how to calculate the 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’sLet’s see an example.
We have used the EMPLOYEE Table in our example.
proc SQl;
select sum(salary) as Tot_Salary from sql.employees;
quit;
Summarizing Data in Multiple Columns
Suppose you want to generate a total of multiple columns in a table.
You want to create an output that shows the total sales for – nhits,nruns, nhome variables each and the combined total. Use the following PROC SQL code to generate the sum of multiple columns and grand total.
proc sql;
select sum(nhits) as nHitsTotal,
sum(nruns) as nRunsTotal,
sum(nhome) as nHomeTotal,
sum(calculated nHitsTotal, calculated nRunsTotal,
calculated nHomeTotal) as GrandTotal
from sashelp.baseball;
2. Column Total in SAS with a Data Step
You can use the SAS data step to generate the cumulative sum in SAS. For example, the SAS Data step processes the statements row by row. Therefore, it is helpful to calculate the total sum 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 statement, you can add the current row’s value 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.
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. Finally, 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;
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 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=;
output out=Tot_Sal SUM=/autoname;
By Default, Proc Summary will not include a sum in the analysis result, so you need to have the sum explicitly after the output dataset.
Suppose there are multiple analysis variables specified in the VAR statement. In that case, 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. It can be helpful to calculate the sum of one variable and the mean of another variable.
4. Column Total in SAS using PROC MEANS
PROC MEANS 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 that you can omit the var statement in Proc Means. However, it will still calculate the sum for each of the numeric variables in the input dataset.
proc means data=sql.employees sum;
run;
5. Column Sum in SAS using PROC UNIVARIATE
PROC UNIVARIATE is one of the most potent SAS procedures for running descriptive statistics and checking critical 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.
proc univariate data=sql.employees;
var salary;
output out=tot_salary
sum=Sum_salary;
run;
6. Column Sum using PROC PRINT Procedure
PROC PRINT Procedure can also calculate the column totals, but SAS will not create an output dataset.
print data=sql.employees noobs;
sum Salary;
run
7. Column Sum using PROC TABULATE
The TABULATE procedure utilizes the SUM function to generate tabular reports. Below is the basic syntax to calculate the Sum of Salaries.
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 detailed and summary reports and output. In Proc Reports, there are several statements and options you can choose.
The COLUMNS statement tells SAS what variable(s) to display in the report. The DEFINE statement means how you will use each variable in the generated report output.
Proc Report data = sql.employees;
column Salary;
define Salary /analysis sum;
run;
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;
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.
