How to sum all of numeric columns in a table in SAS?

Question

I have a table that will have a variable number of columns based on my initial input. Is there a function to sum all the numeric columns of this table without specifying the name of each column?

Right now I have each column name hard coded in a proc sql command.

CREATE TABLE &new_table_name AS 
(SELECT SUM(CASE WHEN col1 = &state THEN 1 ELSE 0 END) AS month_01,
        SUM(CASE WHEN col2 = &state THEN 1 ELSE 0 END) AS month_02,
        SUM(CASE WHEN col3 = &state THEN 1 ELSE 0 END) AS month_03,
        SUM(CASE WHEN col4 = &state THEN 1 ELSE 0 END) AS month_04,
        SUM(CASE WHEN col5 = &state THEN 1 ELSE 0 END) AS month_05
);

Sample input would be like this:

name    m1  m2  m3  m4
aa      1   7   7   1
ab      2   4   2   
ac      1   1       
ad      1   3   1   1
ae      2   1   3

Then the sample output would be

name    m1  m2  m3  m4
        7   16  13  2

Post sample data, in/out. I wonder if this shouldn't be a proc transpose. Your code seems unrelated to the stated question. You can't use variable lists in PROC SQL. — Reeza
The matrix I'm trying to do the sum on is a post transpose data set. — Alex F

Joe Joe · Accepted Answer · 2016-11-10T21:09:56

You are looking for PROC MEANS. Or really any summarization proc.

data have;
infile datalines missover;
input name $ m1  m2  m3  m4;
datalines;
aa      1   7   7   1
ab      2   4   2   
ac      1   1       
ad      1   3   1   1
ae      2   1   3   
;;;;
run;

proc means data=have;
  output out=want sum=;
run;

And the class statement would let you group by state or whatever. WHERE also works fine in PROC MEANS to filter.

Leaving the var statement off calls for all numeric variables, or you can put in a var statement to limit, such as

var m1-m4;

as Reeza notes in comments.

How to sum all of numeric columns in a table in SAS?

1 Answers