Sum with missing value

[eric232]

Thank you very much. Il will use these two requests.

Does this mean that SAS does not allow summation with a single request ? Because with Access SQL it's possible to use the function Nz to avoid the missing values

[RW9]

Sorry, its not clear what you are saying.  The SQL I provided has the subquery as you are both summing across variables, and down through observations.  This is a data modelling choice made by you and your data.  

The function Nz you refer to in Access is a simplified case when statement - this is a logical assignment function, nothing to do with aggregates which is the basis of your question.

ANSI SQL (which all programs implement) support summation upon columns - this is called aggregation.  If all your data were in columns then it would be a simple sum() however your data isn't:

01022701 5 227 9 8 .

01033401 5 227 . 7 12

As you can see from the above, you are both summing() 9, 8, ., and ., 7, 12 across the observations, then summing those two totals together in the group.  A better modelling technique would have been to have the data as:

CODE     KNIV  GROUPE  YEAR  RESULT

01022701 5     227     2015  8

01022701 5     227     2016  9

...

The reason is twofold, firstly you can do summing simply then as you only have one column to sum() by your groups.  Secondly from a data point of view you do not need to have obervations for 2014 first code and 2016 second code.

[eric232]

You are right, The function Nz have nothing to do with aggregates

[Tom]

To add multiple values together and ignore the missing values you can use the SAS function SUM(,).  Note that this is a different function than that SQL aggregate function SUM().  In SQL you can take aggregates of a single variable, SUM(), MEAN(), etc. But the SAS function SUM(,), MEAN(,) etc are designed to work on a single observation and take TWO or MORE arguments.  That is how you and the SAS compiler can tell them apart.

In your example you appear to what to sum the individual sums. So you could either use the SAS SUM(,) function to add the values generated by the SQL aggregate function SUM() or do the reverse.

In this example program the last three variables should have the same value which will be the sum of the first two variables.

proc sql ;
create table want as
  select sum(x) as sum_x
       , sum(Y) as sum_y
       , sum( sum(x) , sum(y) ) as sum_xy
       , sum( sum(x,y) ) as sum_xy2
       , sum( calculated sum_x, calculated sum_y) as sum_xy3
  from have
;
quit;

[eric232 · Accepted solution]

Thank you, the two codes work

 sum( sum(x) , sum(y) ) as sum_xy
       , sum( sum(x,y) ) as sum_xy2