## Sum with missing value

Solved
Occasional Contributor
Posts: 11

# Sum with missing value

[ Edited ]

Hi!

proc sql;
create table Tab14_16GrpNiv as select  KNIV, "" as typdip, groupe,
sum(tab14_16unionRep._2016total) as NbRep16,
sum(tab14_16unionRep._2015total) as NbRep15,
sum(tab14_16unionRep._2014total) as NbRep14,
sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total)  as NbRep14_16
from tab14_16unionSort  inner join tab14_16unionRep on tab14_16unionSort.code = tab14_16unionRep.code
Group by  KNIV, "", groupe;
quit;

Example :  NbRep16 = . , NbRep15 = .  and NbRep14 = 45   ---> The result is NbRep14_16  = .

But I woud like have NbRep14_16  = 45 and not "."

How to do ? Thanks

Accepted Solutions
Solution
‎06-27-2017 05:03 AM
Occasional Contributor
Posts: 11

## Re: Sum with missing value

Thank you, the two codes work

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

All Replies
PROC Star
Posts: 1,283

## Re: Sum with missing value

So you want missing values to be treated as zeros in your summation?

Occasional Contributor
Posts: 11

## Re: Sum with missing value

[ Edited ]

Yes, treated as zeros in the summation, but not replaced by 0 in the columns

Super User
Posts: 9,599

## Re: Sum with missing value

Use sum() function:

```proc sql;
create table Tab14_16GrpNiv as
select  kniv,           "" as typdip,
groupe,
sum(tab14_16unionRep._2016total) as NbRep16,
sum(tab14_16unionRep._2015total) as NbRep15,
sum(tab14_16unionRep._2014total) as NbRep14,
sum(calculated nbrep16,calculated nbrep15,calculated nbrep14)  as NbRep14_16
from    tab14_16unionSort
inner join tab14_16unionRep
on      tab14_16unionSort.code = tab14_16unionRep.code
group by kniv,           groupe;
quit;   ```
Occasional Contributor
Posts: 11

## Re: Sum with missing value

I don't understans ! I used sum() function... What is the diference between my program and your answer ?

Super User
Posts: 9,599

## Re: Sum with missing value

[ Edited ]

You used the + operator on nbrep14_16:

. + . + 45 = .

The sum() function however sums all present values:

sum(.,.,45) = 45

Per the documentation:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245953.htm

Occasional Contributor
Posts: 11

## Re: Sum with missing value

[ Edited ]

I tried this, but grouping does not happen, I get the same number of lines, KNIV*groupe are repeated

proc sql;
create table Tab14_16GrpNiv1 as select  KNIV, "" as typdip, groupe,
sum(tab14_16unionRep._2016total) as NbRep16,
sum(tab14_16unionRep._2015total) as NbRep15,
sum(tab14_16unionRep._2014total) as NbRep14,
sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total)  as NbRep14_16
from tab14_16unionSort  inner join tab14_16unionRep on tab14_16unionSort.code = tab14_16unionRep.code
Group by KNIV, "", groupe;
quit;

Super User
Posts: 9,599

## Re: Sum with missing value

Pleas re-read my code, particularly the keyword calculated.

Also, its a good idea to follow the guidance you will find next to the Post button on new questions.  Provide example test data in the form of a datastep so that we can a) see the structure, and b) have something to run against.  Also posting example required output is a good idea.

There is also a code window - {i} above the post area to put code in so that formatting is reatained, this makes code much easier to read,

Occasional Contributor
Posts: 11

## Re: Sum with missing value

Thank you

Occasional Contributor
Posts: 11

## Re: Sum with missing value

[ Edited ]

I'm not english spoken, and I don't understand when you say " you will find next to the Post button on new questions" ... I understood the rest

Super User
Posts: 9,599

## Re: Sum with missing value

No problems.  When you start a new topic, below the post area is a couple of buttons, Post and Cancel.  Should look like this:

Principally the third element.  Include code and data (as a datastep with datalines), so that we have something to run against.

Occasional Contributor
Posts: 11

## Re: Sum with missing value

[ Edited ]

Ok

In reality, I want to show only the total columns because there is a lot of columns

Here, the grouping doesn't work

sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total)  as NbRep14_16

Here, the grouping is OK, but there is no summation if no values

sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total)  as NbRep14_16

Posts: 4,736

## Re: Sum with missing value

[ Edited ]

@eric232

As @RW9 suggested to you:

1. Provide a data step which creates sample data

2. Explain us what you want and what's currently not working

3. Show us the desired result based on the sample data you've provided

If you ask questions this way then you put us into a situation where we can much better understand what you have and what you need AND you give us the data so we can provide you with tested code which actually creates your desired result.

Asking questions this way takes you a bit more time but it will reduce missunderstandings significantly and you'll get normally the "right" answer much quicker.

Occasional Contributor
Posts: 11

## Re: Sum with missing value

[ Edited ]

OK

data tab14_16unionRep;

input code KNIV groupe _2016total _2015total _2014total;

datalines;

01022701 5 227 9 8 .

01033401 5 227 . 7 12

32022710 3 227 21 31 11

32024002 3 240 12 . .

32024005 3 240 . 14 .

;

run;

data tab14_16unionSort;

input code  _2016total _2015total _2014total;

datalines;

01022701 11.12 13.25 .

01033401  . 10.23 15.21

32022710  21.24 31.68 11.47

32024002  13.36 . .

32024005  . 15.11 .

;

run;

The result I need :

5 227 36

3 227 63

3 240 26

What my 2 programs done :

proc sql;
create table Tab14_16GrpNiv2 as select  KNIV, groupe,
/* sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total)  as NbRep14_16  */
sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total)  as NbRep14_16
from tab14_16unionSort  inner join tab14_16unionRep on tab14_16unionSort.diplome_pre = tab14_16unionRep.diplome_pre
Group by KNIV, groupe;
quit;

5 227 17

5 227 19

3 227 63

3 240 12

3 240 14

or

5 227 36

3 227 63

3 240 .

PS : Providing a data set allowed me understand better (I'm a beginner in SAS, but no in MS Access SQL)

Super User
Posts: 9,599

## Re: Sum with missing value

Well, I have no idea what the second dataset is for.  This code gets you the desired output:

```data tab14_16unionRep;
input code kniv groupe _2016total _2015total _2014total;
datalines;
01022701 5 227 9 8 .
01033401 5 227 . 7 12
32022710 3 227 21 31 11
32024002 3 240 12 . .
32024005 3 240 . 14 .
;
run;

proc sql;
create table WANT as
select  KNIV,
GROUPE,
sum(ROW) as RESULT
from    (select  KNIV,
GROUPE,
sum(_2016TOTAL,_2015TOTAL,_2014TOTAL) as ROW
from    TAB14_16UNIONREP)
group by KNIV,
GROUPE;
quit;```
☑ This topic is solved.