Sum statement in PROC SQL on SAS

3
votes

When I am using a PROC SQL in SAS (SAS 9.3 via SAS EG on UNIX), I need to add up a few variables, but some of them are missing and it results into the overall number missing. For example: 

PROC SQL; 
CREATE TABLE my_table AS 
SELECT 
A,
B,
C,
(A+B+C) as D
FROM source_table

In this example, if either A,B or C are missing, then D is missing too. I need something similar to a sum statement in data step, where any missing values are disregarded. For example, if C is missing then D should be equal to A and B.

Data step is not an option. I have to stay in PROC SQL.

Regards, Vasilij

2
sqlsasproc

2 Answers

2
votes

Use coalesce():

PROC SQL; 
    CREATE TABLE my_table AS 
        SELECT A, B, C,
               coalesce(A, 0) + coalesce(B, 0) + coalesce(C, 0) as D
        FROM source_table ;
4
votes

You can use SUM in PROC SQL as well

PROC SQL; 
CREATE TABLE my_table AS 
SELECT 
A,
B,
C,
sum(A,B,C) as D
FROM source_table