CODEWITHSUNDEEP
sqlsasproc
Vasilij Nevlev
Vasilij Nevlev

Reputation: 1449

Sum statement in PROC SQL on SAS

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

Upvotes: 3

Views: 7554

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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 ;

Upvotes: 2

Longfish
Longfish

Reputation: 7602

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

Upvotes: 4

Related Questions