Clever Geek Handbook

Sum statement in PROC SQL over SAS

When I use PROC SQL in SAS (SAS 9.3 via SAS EG in UNIX) I need to add a few variables, but some are missing and this results in a missing total. 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 A, B, or C are missing, then D is also missing. I need something similar to the sum operator in a data step where any missing values ​​are ignored. For example, if C is missing, then D must equal A and B.

Data step is not a parameter. I have to stay at PROC SQL.

Regards, Vasilij

+3


source to share


2 answers


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 ;
+1


source


You can also use SUM

in PROC SQL



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


source






More articles:


All Articles