8 Ways to Calculate Column Total in SAS

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.

8 Ways to Calculate Column Total in SAS
Sample Input Table for calculating column total
proc SQl; 
select sum(salary) as Tot_Salary from sql.employees; 
quit;
8 Ways to Calculate Column Total in SAS
Column Total in SAS using PROC SQL

Summarizing Data in Multiple Columns

Suppose you want to generate a total of multiple columns in a table.

8 Ways to Calculate Column Total in SAS
Sample Input Table for Summarizing Data From Multiple Columns

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;
8 Ways to Calculate Column Total in SAS
PROC SQL sum multiple columns

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.

8 Ways to Calculate Column Total in SAS

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;
8 Ways to Calculate Column Total in SAS

 

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=;
8 Ways to Calculate Column Total in SAS
output out=Tot_Sal SUM=/autoname;
8 Ways to Calculate Column Total in SAS

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;
8 Ways to Calculate Column Total in SAS

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;
Column total in SAS

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
8 Ways to Calculate Column Total in SAS

 

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
Column total in SAS

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;
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
PROC REPORT Procedure

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.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Leave a Reply

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

Share via
Copy link
Powered by Social Snap